Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server
Article

SQL Server 2000/2005: Automated Database Backups and Restorations

Rate me:
Please Sign up or sign in to vote.
4.76/5 (21 votes)
22 Dec 2008CDDL10 min read 67.7K   53   6
Provides an insight into creating automated process for Backups and Restorations

SQL Server: Automated Database Backups and Restoration<o:p>

<o:p> 

Introduction<o:p>

The database server is the heart of information storage for the enterprise, so special care must be taken in managing this critical resource. In case of a problem, it must be addressed quickly, efficiently, and correctly, or an organization stands to suffer critical losses.<o:p>

<o:p> 

In all the environments I have worked in that I might have taken lightly, backups were obviously not it. In my opinion, database backups hold second place in importance only to the actual data itself. With this mindset, I am adamant about making sure that backups are done properly and on a regular schedule.<o:p>

<o:p> 

Although not interesting, but as a DBA, performing backups is one of the most important tasks. To wipe off this load of daily manual backups and restoration from multiple servers to the Data Recovery server, I created an automated process for the same and the sole purpose of writing this tutorial is to help all you people who daily bang their heads in this arena.<o:p>

<o:p> 

Pre-Requisitions<o:p>

One of the most interesting things is that you can run the DOS commands from within your SQL Server. When I say running DOS commands, it obviously includes running batch files too. <o:p>

<o:p> 

  • Folders
    For the process of automatic backups and restorations, create two folders on your “C” drive and name them “DataBackup” and “DBRepository”. The “DataBackup” folder will be used as a folder where initial backup comes, whereas, the “DBRepository” folder will be used for storing the secondary backup. The “DBRepository” folder can also be created on another drive also, if exists.

    In my case I created the “DBRepository” folder in my “D” drive.

    <o:p>
  • Winzip Application
    Licensed version of “Winzip 10.0” or higher is required if the requirement is such that you need to restore the database on some other server (such as a Fall Back Server). Though you can simply FTP the backup file on the DataRecovery server, but if the size of the backup file is large, you should zip the backup file before FTP’ing it to the other server. It will always be quicker.

    <o:p>
  • wzcline22.exe
    Download the above mentioned file and install it. This file will enable running the zip command from the command prompt and so from the batch file. Remember that you must have the licensed version of winzip 10.0 or higher installed on your server or otherwise it will not work.

    You can download this file from the following location:
    http://www.winzip.com/dprob.htm

    <o:p>
  • Push.bat
    Now, you need to create a batch file with the following lines of code in it:
    CD\<br />
    cd DataBackup<br />
    "c:\program files\winzip\wzzip.exe" DBFull.zip
    sg_Complete_Backup.bak<br />
    "c:\program files\winzip\wzzip.exe" DBDiff.zip
    sg_Differential_Backup.bak<br />
    c:\windows\system32\FTP.exe -s:AccessFTP.txt<br />
    move *.bak D:\DBRepository<br />
    del *.zip<br />
    

    Name the batch file as “Push.bat” and save it in the “DataBackup” folder. This is the folder where the automated backup initially comes.

    The above written lines of codes are simply the normal DOS commands, which you must be familiar with.

    In my case, I have named the complete backup as “sg_complete_backup.bak” and the differential backup as “sg_differential_backup.bak”. Also, the complete backup will be zipped as “DBFull.zip” and the differential backup will be zipped as “DBDiff.zip”.

    I have created a single batch file that works for both the complete as well as the differential backup sets.

    Note: You need to be cautious while naming the files as this is an automated process and changing the names at one would require changes at all the places where the

    <o:p>
  • AccessFTP.txt
    If you look at the code above in the batch file you just created, you will see that the batch file opens the “AccessFTP.txt” file for FTP’ing the zip file to the DataRecovery server. So, now create a text file and save it as “AccessFTP.txt” in the “DataBackup” folder along with “Push.bat”. Write the following lines inside this file:

    open<br />
         <<ip address of the Data Recovery Server>><br />
         <<Windows logon UserName of the Data
         Recovery Server >><br />
         <<Windows logon Password of the Data Recovery Server >><br />
         cd <<folder where you want to FTP the zip
         file on the Data Recovery Server >><br />
         Put "C:/DataBackup/DBFull.zip"<br />
         Put "C:/DataBackup/DBDiff.zip"<br />
         quit

    Let me explain the above lines one by one.

    <o:p>
    • open – This will ask you the ip address of the machine to connect.

      <o:p>
    • <<ip address of the Data Recovery Server>> - This is the IP of the Data Recovery Server.

      <o:p>
    • <<Windows logon UserName of the Data Recovery Server >> - Windows Username of the data Recovery Server.

      <o:p>
    • <<Windows logon Password of the Data Recovery Server >> - Windowws Password for the Data Recovery Server.

      <o:p>
    • cd <<folder where you want to FTP the zip file on the Data Recovery Server >> - This may be the FTP root folder or a folder within the configured FTP folder on the Data Recovery Server.

      <o:p>
    • Put "C:/DataBackup/DBFull.zip" – This will put the complete backup set from the current server to the folder mentioned above on the Data Recovery Server.

      <o:p>
    • Put "C:/DataBackup/DBDiff.zip" – This is the same as above, but is valid for the differential backup set.

      <o:p>
    • Quit – This will quit the text file.<o:p>

<o:p> 

Implementation
Now, after all the above mentioned pre-requisites have been met, it is time for us to really dig deep into the implementation part. All must be aware that for the schedulers to run, the SQL Server Agent must be in the Start state, i.e., it must be running.<o:p>

<o:p> 

Since, we need to make an automated process; we will schedule the complete process as a job, so as to run it as per the time set by us. For this, the SQL Server Agent must be running.<o:p>

<o:p> 

Follow the steps below to create a job that takes the complete backup, zips the backup file and FTPies it to the Data Recovery Server. Also, the steps include the commands that run the Job created on the Data Recovery Server.

<o:p>

  • Step 1<o:p>
    • Expand the SQL Server Agent to see the Jobs.<o:p>
    • In the wizard that appears, give the name to the scheduler by writing the name inside the text box in front of the Name.<o:p>

<o:p> 

<v:shapetype stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600" id="_x0000_t75"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> <v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"> <v:shape style="width: 442.5pt; height: 252pt;" type="#_x0000_t75" id="_x0000_i1025"> <v:imagedata o:title="step2_0" src="/KB/database/assiduous/image010.jpg"> Image 1<o:p>

  • Step 2<o:p>
    • Click on the steps Tab and then click on the New button to create a new step.

      <o:p>

<v:shape style="width: 442.5pt; height: 337.5pt;" type="#_x0000_t75" id="_x0000_i1026"> <v:imagedata o:title="step2_1" src="/KB/database/assiduous/image011.jpg"> Image 2<o:p>

<o:p> 

    • Enter the step name in the text box. In the Database name drop down list box, leave the database name as master.

      The sole purpose of leaving the database name as master is that we are going to use the extended procedures provided in the master database for our entire task.

      <o:p>
    • Write the code in the text box provided in front of the label “Command”. Note that the path is mentioned as “c:\DataBackup\sg_complete_backup.back” for taking the backup. It is the same path in which we had created the batch file as well as the text file.<o:p>

<v:shape style="width: 442.5pt; height: 351.75pt;" type="#_x0000_t75" id="_x0000_i1027"> <v:imagedata o:title="step2_2" src="/KB/database/assiduous/image012.jpg"> Image 3

<o:p>

    • Replace the name of the database from SG with the name of your database. Also, you need to change the name of the backup file to the name you have changed in the batch file created above.

      <o:p>
    • Click on the Parse button to ensure that the command written has no errors. Click on the Apply button and then click on OK to exit this dialog box.

      <o:p>
    • It is now time to create the second step of the job. This step will utilize the extended procedure “xp_cmdshell” of the master database to run the DOS commands written within the batch file created above.

      This step is mainly responsible for creating the zip file of the backup and also to do FTP to the desired Data Recovery Server.

      Note that again I have kept the default name of the Database server as master.

      <o:p>

<v:shape style="width: 442.5pt; height: 364.5pt;" type="#_x0000_t75" id="_x0000_i1028"> <v:imagedata o:title="step2_3" src="/KB/database/assiduous/image013.jpg"> Image 4

<o:p>

    • Parse to check out for errors, apply and click OK to exit this step.

      <o:p>
    • Now create a new step, which starts the job of unzipping and restoring the database on the remote Data Recovery Server.

      Since there are a few more lines of code in this step, the screen shot does not show the complete code. The complete code for the same is, therefore, written below the image.

      <o:p>

<v:shape style="width: 442.5pt; height: 378pt;" type="#_x0000_t75" id="_x0000_i1029"> <v:imagedata o:title="step2_4" src="/KB/database/assiduous/image014.jpg"> Image 5<o:p>

<o:p> 

declare @retcode
int <br />declare @job_name
varchar(300) <br />

declare @server_name
varchar(200) <br />

declare @query varchar(8000)
<br />
declare @cmd varchar(8000) <br />

<br /><br />

<br />set @job_name
= '<<This is the job name of the Data Recovery Server, which we want to
run for unzipping and Restoring the database>>' ------------------Job
name goes here. <br />

set @server_name
= '<<IP address of the Data Recovery Server>>'
------------------Server name goes here. <br />

<br /><br />

<br />set @query = 'exec msdb.dbo.sp_start_job @job_name =
''' + @job_name + '''' <br /><br />

<br />set @cmd =
'osql /U <<UserName
of the Database>> /P <<Password for the database>> -S ' + @server_name + ' -Q "' + @query + '"' <br />

<br /><br />

exec @retcode
= master.dbo.xp_cmdshell @cmd

<o:p>

<o:p> 

<o:p> 

  • Step 3<o:p>
    • Open the Data Recovery Server and then open the SQL Enterprise Manager.

      <o:p>
    • Now create a new job by following the initial steps mentioned under the step 2.

      <o:p>
    • Give the new job the same name as the one given by you in the last step of “Step 2” above, i.e, the name of this job must be the same as the one mentioned in the last step of the job created above for Unzipping and Restoring the database.

      If the names do not match, the complete service will not work.

      <o:p>

<v:shape style="width: 442.5pt; height: 251.25pt;" type="#_x0000_t75" id="_x0000_i1030"> <v:imagedata o:title="step3_0" src="/KB/database/assiduous/image001.jpg"> Image 6

<o:p>

    • Now Click on the Steps tab and then click on the New button to create the steps for this new job on the Data Recovery Server.

      <o:p>
    • In the first step write the command for running the batch file that unzips the database backup and delete the zip file.

      I will give the brief on this batch file later after we had created our job of unzipping and restoration of the backed up database.

      <o:p>

<v:shape style="width: 442.5pt; height: 351pt;" type="#_x0000_t75" id="_x0000_i1031"> <v:imagedata o:title="step3_1" src="/KB/database/assiduous/image002.jpg"> Image 7

<o:p>

    • Click on the OK button to apply and exit the dialog box. Now click on the New button again to create the second step.

      This is the main step, that restores the database

      <o:p>

<v:shape style="width: 442.5pt; height: 365.25pt;" type="#_x0000_t75" id="_x0000_i1032"> <v:imagedata o:title="step3_2" src="/KB/database/assiduous/image003.jpg"> Image 8

<o:p>

declare @str nvarchar(4000)<br />

declare @spid int<br />

<br />while exists (select spid from sysprocesses where dbid= db_id('<<DatabaseName>>'<br />

begin<br />

          set @spid = (select min(spid) from sysprocesses where dbid= db_id('<<DatabaseName>>'))<br />

          set @str= 'kill ' + convert(nvarchar,@spid)<br />
          execute sp_executesql @str<br />

<br />end<br />

<br /><br />

RESTORE DATABASE <<DatabaseName>>
from disk=’<<Path where the backup file resides on the Data Recovery
Server>>' <br />

with StandBy='C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\UNDO_<<DatabaseName>>.DAT',<br />
Move '<<Logical Data File Name>>' To '<<path where the Physical data file name
resides>>', <br />

Move '<<Logical Log File Name>>' To '<<path where the Physical log file name
resides>>', <br />

Replace

<o:p> 

<o:p> 

    • In my case, I have kept the database as Read Only. Therefore, I have use the “StandBy” command in my restoration. You van also use the “NoRecovery” option.

      <o:p>
    • Now create the third step that is responsible to delete the backup file from the place from where the database was restored. We can delete the backup file as we have already restored the database and also to save the disk space.

      Deleting the backup file will also be done by running the batch file.

      <o:p>

<v:shape style="width: 442.5pt; height: 378pt;" type="#_x0000_t75" id="_x0000_i1033"> <v:imagedata o:title="step3_3" src="/KB/database/assiduous/image004.jpg"> Image 9

<o:p>

  • Step 4
    Now is the time to create the batch files that runs from the steps mentioned above and have the task to unzip the zip file, deleting the zip file and thereafter, deleting the backup file after successful restoration.

    <o:p>
    • Create a batch file with the following lines of code:



            D:<br />
            cd "ftp/db backup/SE"<br />
            "c:\program files\winzip\WZUNZIP.EXE"
            DBFull.zip<br />
      <st1:state w:st=""on""><st1:place w:st=""on"">del</st1:place></st1:state> *.zip

      Name the batch file as the one that you have mentioned in the step1 of the job created in “Step 3” above.

      <o:p>
    • This file will unzip the database and delete the zip file.

      <o:p>
    • Create one more batch file with the following lines of code

      D:<br />
      cd "ftp/db backup/SE"<br />
      del *.bak
      


      Name this batch file to the one as mentioned in the last step of the job created in “Step 3” above.

      <o:p>
    • This will delete the backup file after successful restoration.

      <o:p>
    • Both these batch files must be kept inside the folder where the backup file is FTPied after taking the backup, i.e., the FTP folder of the Data Recovery Server.<o:p>

<o:p> 

<o:p> 

You can now schedule the job of taking the complete/differential backups.

This completes the process of automating the database Backups and Restorations. I hope this would be of great help to you all. In case of a problem or a query or clarifications, kindly feel free to drop me a mail at neerajks77@gmail.com.<o:p>

<o:p> 

<o:p> 

License

This article, along with any associated source code and files, is licensed under The Common Development and Distribution License (CDDL)


Written By
Program Manager Tata Consultancy Services
India India
Microsoft Certified Technology Specialist: Microsoft Office SharePoint Server 2007:Configuration

Microsoft Certified Technology Specialist: SharePoint Server 2010:Development

Microsoft Certified Technology Specialist: SharePoint Server 2010:Configuring

Certified Scrum Master

Comments and Discussions

 
Questioncan i have code with exe Pin
Deepak Kumar5-Aug-20 20:11
Deepak Kumar5-Aug-20 20:11 
GeneralMy vote of 4 Pin
balakrishnav21-Mar-11 17:55
balakrishnav21-Mar-11 17:55 
GeneralMy 5 [modified] Pin
Manas_Patnaik24-Dec-08 17:33
Manas_Patnaik24-Dec-08 17:33 
GeneralRe: My 5 Pin
Neeraj Kr26-Dec-08 3:11
Neeraj Kr26-Dec-08 3:11 
GeneralMy Vote of 1 Pin
CARPETBURNER23-Dec-08 2:10
CARPETBURNER23-Dec-08 2:10 
Why Zip the archive?

We have a 8gb Production Db how long would it take to Zip that?

Seems a very odd way of doing it esp when SQL Already has a good backup solution.
GeneralRe: My Vote of 1 Pin
Neeraj Kr23-Dec-08 4:08
Neeraj Kr23-Dec-08 4:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.