Daily scheduled backup to disk (SQL 7.0)
This document will show you how to configure SQL server 7.0 to back up selected databases (Db) to the hard disk. This is useful if you don’t have a lot of money to play with for a backup solution and need your databases to be up and running pretty much 24/7. Backing up a database to the hard disk is a lot quicker than backing up to tape, hence your Db is available a lot quicker again to users. Once stored on disk you can schedule another task to back up the Db to tape, giving you added security for more information on scheduling a backup using the NT backup software click here. So let’s get going.
Configuring the Backup
Right first let’s open up the Enterprise Manager (Management Console) and select your server and expand the tree. Now select Management and expand that branch as well.
Right click on the Backup option and from the drop down menu select New Backup Device. From the dialogue box, on the General tab, enter the name of your backup device in the Name: box. You’ll notice that as you are entering the device name that it is replicated in the field labelled File name: and has the extension .bak appended to it. By default SQL server stores the back up device in <drive letter>:mssql7ackup. Once you have entered all the details click OK
Right click on the Backup option again and select Backup database. On the Dialogue box that appears (labelled SQL Server Backup, you will have to fill in a number of details split into five section.In section 1 you start by specifying the Db you wish to backup from the drop down menu labelled Database:. Next you specify the name of the backup (from the field Name:). This name is dynamically allocated, but you can override this and enter your own name if you so wish. You can now optionally enter some details about the backup in the Description field. In section 2 you can specify the type of backup you wish to perform. By default Database - complete is selected. But you can also carry out a differential backup. The other options are greyed out and I will talk about these at a later date. The third section is the Destination section. Here you select your backup device, which you created earlier on. You do this by clicking Add…. Another dialogue box appears giving you the option to select to either backup your Db to a file or the device you created earlier on by checking the appropriate radio button. If you check the Backup device option you can select the appropriate one from the dropdown box. Submit your selection by clicking OK. The fourth section allows you to specify a few more options for your backup type, namely whether you wish to Append to media(add to your existing backup set, without overwriting) or Overwrite existing media(overwrite your existing backup set). In the fifth and final option, you set your backup to scheduled by ticking the Schedule: checkbox you and then click the browse button to specify day, time, and frequency.
Scheduling the backup
First you need to give your schedule a name. You do this by entering the schedule name in the Name: field. For your back up to occur on a daily basis make sure that in the Schedule type: section, the option for recurring is selected [this by the way is the default selection]. By clicking on the Change button you open a new dialogue that allows you to set the interval, days and time at which you wish to have your backup occur. First off you can set the frequency to either daily, weekly or monthly. For our purpose we set it to daily and in the daily section we set the number of days to 1. If we wanted the backup to occur every second day, we would set that value to 2. Moving along, you can now set the frequency of the daily backup (Daily frequency). We will want to set the back up to occur once every day at a time when usage is at it’s lowest (bearing in mind that the web is up 24/7:o)). So make sure the radio button Occurs once at: is checked and specify the time, say 2:00. In the final section you can specify the duration, i.e. you can set a start and end date. For the purpose of this exercise, set the start date to today and set no end date. Click OK to complete the recurrence schedule and OK again to finish configuring the schedule. Click OK one more time and you have configured the backup.
There are two things you should now check to ensure that your scheduled backup will take place and successful. First off make sure that the SQL Server Agent is started (also in the Management branch. Secondly please make sure that your Task Scheduler (if you have IE5 installed on your machine) or that the Scheduler (for IE4 and below) service has been started. That’s it should all work like clockwork now. For more information on SQL Server Administration, check out SQL Server 7 DBA Survival Guide. As ever comments and suggestions welcome.