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">
<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">
<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">
<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">
<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">
<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">
<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">
<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">
<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">
<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>
