INTRODUCTION
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.
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.
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.
PRE‐REQUISITIONS
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.
- 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.
- 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.
- 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
- PUSH.BAT Now, you need to create a batch file with the following lines of code in it:
CD\ cd DataBackup
"c:\program files\winzip\wzzip.exe" DBFull.zip sg_Complete_Backup.bak
"c:\program files\winzip\wzzip.exe" DBDiff.zip sg_Differential_Backup.bak
c:\windows\system32\FTP.exe -s:AccessFTP.txt
move *.bak D:\DBRepository
del *.zip 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 - ACCESSFTP.TXTIf 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
<<ip address of the Data Recovery Server>>
<<Windows logon UserName of the Data Recovery Server >>
<<Windows logon Password of the Data Recovery Server >>
cd <<folder where you want to FTP the zip file on the Data Recovery Server >>
Put "C:/DataBackup/DBFull.zip"
Put "C:/DataBackup/DBDiff.zip"
quitLet me explain the above lines one by one.
- open – This will ask you the ip address of the machine to connect.
- <<ip address of the Data Recovery Server>> - This is the IP of the Data Recovery Server.
- <<Windows logon UserName of the Data Recovery Server >> - Windows Username of the data Recovery Server.
- <<Windows logon Password of the Data Recovery Server >> - Windowws Password for the Data Recovery Server.
- 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.
- 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.
- Put "C:/DataBackup/DBDiff.zip" – This is the same as above, but is valid for the differential backup set.
- Quit – This will quit the text file.
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.
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.
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.
- STEP 1 Expand the SQL Server Agent to see the Jobs. In the wizard that appears, give the name to the scheduler by writing the name inside the text box in front of the Name.
- STEP 2Click on the steps Tab and then click on the New button to create a new step.
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.
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.
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.
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.
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.
Parse to check out for errors, apply and click OK to exit this step. 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.
declare @retcode int
declare @job_name varchar(300)
declare @server_name varchar(200)
declare @query varchar(8000)
declare @cmd varchar(8000)
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.
set @server_name = '<<IP address of the Data Recovery Server>>' ------------------Server name goes here.
set @query = 'exec msdb.dbo.sp_start_job @job_name = ''' + @job_name + ''''
set @cmd = 'osql /U <<UserName of the Database>> /P <<Password for the database>> -S ' + @server_name + ' -Q "' + @query + '"'
exec @retcode = master.dbo.xp_cmdshell @cmd - STEP 3 Open the Data Recovery Server and then open the SQL Enterprise Manager.
Now create a new job by following the initial steps mentioned under the step 2. 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.
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. 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.
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
declare @str nvarchar(4000)
declare @spid int
while exists (select spid from sysprocesses where dbid= db_id('<<DatabaseName>>'
begin
set @spid = (select min(spid) from sysprocesses where dbid= db_id('<<DatabaseName>>'))
set @str= 'kill ' + convert(nvarchar,@spid)
execute sp_executesql @str
end
RESTORE DATABASE <<DatabaseName>> from disk=’<<Path where the backup file resides on the Data Recovery Server>>'
with StandBy='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\UNDO_<<DatabaseName>>.DAT',
Move '<<Logical Data File Name>>' To '<<path where the Physical data file name resides>>',
Move '<<Logical Log File Name>>' To '<<path where the Physical log file name resides>>',
Replace
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. 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. - STEP 4Now 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. Create a batch file with the following lines of code:
D:
cd "ftp/db backup/SE"
"c:\program files\winzip\WZUNZIP.EXE" DBFull.zip
""on"">""on"">
del *.zip
Name the batch file as the one that you have mentioned in the step1 of the job created in “Step 3” above.
This file will unzip the database and delete the zip file.
Create one more batch file with the following lines of code
D:
cd "ftp/db backup/SE"del *.bak
Name this batch file to the one as mentioned in the last step of the job created in “Step 3” above. This will delete the backup file after successful restoration. 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. 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.
