I got an email the other day from a friend who needed some SQL help. He had a SQL Server with a database whose transaction log had grown and was filling the entire disk drive. This is a common problem that system administrators face in shops that do not have a DBA on staff. The cause, of course, is that the database was in full recovery mode and no transaction log backups were being made.
My friend was asking if it was safe to use BACKUP LOG with TRUNCATE_ONLY. This was advice he had found via Google. While that used to work, it is never a good idea. If you do that, you’ve just lost the ability to do a point in time recovery, so you’ve seriously compromised yourself should your system fail. Furthermore, the TRUNCATE_ONLY option was removed with SQL 2008, so this might not even have done anything on his system.
So I walked him through the process of backing up his log file and then shrinking it back down to a manageable size. (It turns out there hadn’t been any backups made in 9 months.) I then walked him through creating a backup plan so that this wouldn’t happen again. Given that this is a common problem, I thought it was worth showing step-by-step instructions on how to do this.
Note: This tutorial is intended for accidental DBAs – people whose primary job role is something else, but ended up in charge of one or more SQL Servers. It will create a very basic backup plan that will prevent transaction logs from growing to eat up all your disk space and give you a basic level of data protection. It is not meant as a substitute for someone with database experience who can actively manage your environment.
This plan will take a full backup each Sunday night at midnight, a differential backup at midnight Monday through Saturday and transact log backups every hour. It should be fairly obvious how to adjust this schedule to suit your particular needs.
This plan was developed with SQL Server 2005, but the steps should be the same on SQL Server 2008, 2008 R2, and 2012, although the screens may be slightly different. You will likely need sa rights on the server and this will be built using the wizard in SQL Server Management Studio (SSMS).
The first step is to launch the maintenance plan wizard. In SSMS, you do this by connecting to the SQL Server, expanding the Management node, and right-clicking on the Maintenance Plans folder.
This will bring up a new window where you can name your plan and choose how you want to schedule the various parts of the plan. I’ve named my plan “Backup Plan,” but you are free to name it whatever you want. (Except “Brittnie.” That’s just wrong.) Select the radio button to have separate schedules for each task, then click Next.
The next window that appears gives you a list of tasks you want the plan to perform. We’re going to choose all the backup tasks.
The next screen lets you set the order the tasks are performed in. We’re going to manually schedule each step, so there is no need to change anything here. just click Next to accept.
Now you’ll be presented with a screen to configure the full database backup task. From the Database drop down list, select All Databases and click OK.
Now, you need to tell the wizard where you want the database backups stored. Towards the bottom of the window, there is a Folder: field. Enter the path here. UNC paths are supported. Also, select the two checkboxes I have circled.
Now let’s tell the wizard when we want the full backups made. Click on the Change… button near the bottom of the above window. You’ll be presented with the screen below. We’re going to perform full backups each Sunday at midnight, so set the options as shown, then click OK.
Click Next to proceed to setting up the differential backup task. For this task, we are only going to backup the user databases, so make the following selection from the Databases drop down list:
Once again, we need to tell SQL Server where we want these files stored:
And now we have to tell SQL Server when to take the differential backups. click the Change… button to set the schedule. we’ll take differential backups Monday through Saturday (full backups are being taken on Sunday, so we don’t need to take a differential that day). Again, these will run at midnight.
Click OK to accept the schedule, then Next to move on to the transaction log backup task. Once again, choose All User Databases from the drop down list.
Again, define the save path and select the two check boxes.
Now we’ll set up the times we want the transaction log backups to occur. This will be a bit different from the other two we set up because we want these to run every hour, not once a day. Also note we’re setting the starting time to 1 AM. This is because our full and differential backup jobs run at midnight, so there is no need to also take a transaction log backup at that time.
Click OK to accept the schedule and then Next to move on to the next screen in the wizard. Here, we will specify that the backup jobs write their output to a text file. This is useful for troublehsooting purposes in case the job fails for some reason.
Click Next to get to the wizard summary screen:
Click Finish and the wizard will create your jobs. If you now refresh the Maintenance Plans node in the left pane of SSMS, you should see your new plan.
And finally, if you open the SQL Agent node and double-click on the Job Activity Monitor node, you’ll get a list of jobs on the server. You’ll see the one the wizard just created:
My jobs are disabled, which is why the icons are grey, but by default, the wizard enables the jobs when they are created, so they will run at the defined time.
You’ve now got backups being made and you are managing your transaction logs. Congratulations!
But this isn’t all there is to do. The astute reader will realize that we have not set up any method of purging old backups or the job output text files. Left as is, these will just accumulate and fill up whatever disk you are storing them on. Next time, I’ll show how to edit these maintenance plans to include steps to purge old files.
Another item to note: we selected all user databases to be have differential and transaction log backups be taken. This was to ensure that any newly created database automatically get picked up by the plan. However, this can cause problems because a differential or transaction log backup cannot be taken until a full backup of the database has been taken. So if you have a developer that creates a new database on Tuesday, the transaction log backup job and the differential backup log jobs will start failing until a full backup has been made of that database. The solution, of course, is to make a full backup of the new database, and then the jobs will work.