SQL Mirroring Using Batch Files

By : R.SENTHIL KUMAR, SYSTEM ADMINISTRATOR, CHENNAI CITY NORTH DIVISION

MOBILE 9884373390

---

I HOPE OUR SYSTEM ADMINISTRATORS WILL HAVE THE PATIENCE TO READ THE FOLLOWING AND ALSO SUGGEST ANY OTHER BETTER METHODS WHICH THEY ARE ADOPTING AT THEIR RESPECTIVE WORK PLACE

In today's scenario of our department after three to four years of implementation of Total Computerisation we encountering frequent server breakdowns. The AMC service providers also do not attend the Server breakdowns in time which results in total stoppage of office functioning till the Server is set right. Even after the Server is set right we still rely on the automatic "Database maintenance plan" which we configure in SQL Server. But how many of us have consiously made an effort to frequently restore the backup files created vide the Maintenance plans.
In this context recently I have been trying to configure a SQL server mirroring with the existing provision available in SQL server which is called "Replication". Unfortunately though it is an built-in utility available in SQL Server I tried it but its not effective enough because if you configure replication SQL databases from one server to another Server it slows down the entire process on the both the Server which is not desirable.
Hence recently I had glimpse reading about the "OSQL Utility". In OSQL Utility you can display the results of any SQL Query in the command prompt. Just simply try typing the following command in DOS prompt.
osql ?

The above command will show the osql switches that can be used. With this as the baseline I tried to develop a batch program which runs on a client machine automatically when the user logs in. Immediatly when the user logs this batch program will fire in the startup. The prerequisites for running is batch program and the procedure of how to configure a backup server is given below:

1. Configure another client machine and load Windows Server 2003 / 2008 OS. While doing "dcpromo" select the option "Additional Domain Controller for an existing Domain" and give the administrator password and Domain name of the Main Server. At the end of the dcpromo wizard you restart the please check in "Active Directly User and Computers" that the same Domain Users of the Main Server is shown in this also.

2. If you are having all the database files (i.e mdf,ldf and dat files) in a drive other than "C" drive please ensure that the same drive is available in the client machine. Because while restoring the database from a backup file it will be restored in the same drive only. Let us say for example you have all your database files in D drive in the Main Server. In that case create a D drive partion in the client machine also with enough disk space.

3. Create a folder named "backup" in E drive of you Main Server(or any other drive as you wish) share the folder with permissions as "Full control".

4. Install SQL Server 2000 / 2005 in the newly configured client machine (i.e your additional domain controller).

5. Create a folder name "restore" in the newly configured client machine in the D drive.

6. Open SQL query analyser in the newly configured client machine and type the following coding given below and save it with a file name "bkpquery1.sql" in D drive.

BACKUP DATABASE BPRO TO DISK='E:\BACKUP\BPRO.BAK'
BACKUP DATABASE BPLOG TO DISK='E:\BACKUP\BPLOG.BAK'
BACKUP DATABASE COUNTER TO DISK='E:\BACKUP\COUNTER.BAK'
BACKUP DATABASE ECOUNTER TO DISK='E:\BACKUP\ECOUNTER.BAK'
BACKUP DATABASE EMO TO DISK='E:\BACKUP\EMO.BAK'
BACKUP DATABASE EIOD TO DISK='E:\BACKUP\EIOD.BAK'
BACKUP DATABASE IPO TO DISK='E:\BACKUP\IPO.BAK'
BACKUP DATABASE PATCH TO DISK='E:\BACKUP\PATCH.BAK'
BACKUP DATABASE POSPCC TO DISK='E:\BACKUP\POSPCC.BAK'
BACKUP DATABASE POSPCCBACKUP TO DISK='E:\BACKUP\POSPCCBACKUP.BAK'
BACKUP DATABASE POST TO DISK='E:\BACKUP\POST.BAK'
BACKUP DATABASE POSTMAN TO DISK='E:\BACKUP\POSTMAN.BAK'
BACKUP DATABASE PROJECT TO DISK='E:\BACKUP\PROJECT.BAK'
BACKUP DATABASE ROUTING TO DISK='E:\BACKUP\ROUTING.BAK'
BACKUP DATABASE SIGN TO DISK='E:\BACKUP\SIGN.BAK'
BACKUP DATABASE SOSB TO DISK='E:\BACKUP\SOSB.BAK'
BACKUP DATABASE SUBTREASURY TO DISK='E:\BACKUP\SUBTREASURY.BAK'
BACKUP DATABASE TREASURY TO DISK='E:\BACKUP\TREASURY.BAK'

(The above code when executed from the client machine after connecting to the SQL Server instance of Main Server will backup all you databases in the path E:\Backup of the Main Server.)
(Note: in the above coding I have just included the databases of a Sub-Offices only if you are working in an head office incorporate the databases like Accounts, Schedules, CashBook and all other Sanchay Post Sub Office databases)


7. In the same way open SQL Query Analyser in the newly configured client machine and type the following code and save it with the name "restorequery1.sql" in the path "d:\restore".

RESTORE DATABASE BPRO FROM DISK='D:\RESTORE\BPRO.BAK'
RESTORE DATABASE BPLOG FROM DISK='D:\RESTORE\BPLOG.BAK'
RESTORE DATABASE COUNTER FROM DISK='D:\RESTORE\COUNTER.BAK'
RESTORE DATABASE ECOUNTER FROM DISK='D:\RESTORE\ECOUNTER.BAK'
RESTORE DATABASE EMO FROM DISK='D:\RESTORE\EMO.BAK'
RESTORE DATABASE EIOD FROM DISK='D:\RESTORE\EIOD.BAK'
RESTORE DATABASE IPO FROM DISK='D:\RESTORE\IPO.BAK'
RESTORE DATABASE PATCH FROM DISK='D:\RESTORE\PATCH.BAK'
RESTORE DATABASE POSPCC FROM DISK='D:\RESTORE\POSPCC.BAK'
RESTORE DATABASE POSPCCRESTORE FROM DISK='D:\RESTORE\POSPCCRESTORE.BAK'
RESTORE DATABASE POST FROM DISK='D:\RESTORE\POST.BAK'
RESTORE DATABASE POSTMAN FROM DISK='D:\RESTORE\POSTMAN.BAK'
RESTORE DATABASE PROJECT FROM DISK='D:\RESTORE\PROJECT.BAK'
RESTORE DATABASE ROUTING FROM DISK='D:\RESTORE\ROUTING.BAK'
RESTORE DATABASE SIGN FROM DISK='D:\RESTORE\SIGN.BAK'
RESTORE DATABASE SOSB FROM DISK='D:\RESTORE\SOSB.BAK'
RESTORE DATABASE SUBTREASURY FROM DISK='D:\RESTORE\SUBTREASURY.BAK'
RESTORE DATABASE TREASURY FROM DISK='D:\RESTORE\TREASURY.BAK'



8. Now comes the actual part where we are going to write a batch program incorporating the OSQL Utility which will call the backup.sql and restore.sql in its program. The coding of the batch program is as follows given in colours. Type the following code in notepad and save it with some name with extention as *.bat for eg.senthil.bat and not the default as *.txt. Further after creating this batch create a shortcut and paste the shortcut in C:\Documents and Setting\All Users\Startmenu\Program\Startup
:BEGIN
@ECHO OFF
ECHO.
CD\
D:
CD RESTORE
DEL *.BAK
ECHO.
BACKING UP FILES
osql -U SA -P password1 -S SQL_SERVER1 -i D:\RESTORE\BKPQUERY1.sql
ECHO COPYING BACKUP FILES
COPY \\MainServerName\backup D:\RESTORE
ECHO.
ECHO COPY FILES COMPLETED
DEL file://mainservername/backup *.bak
ECHO.
ECHO RESTORATION PROCESS GOING ON PLEASE WAIT
osql -U SA -P password2 -S SQL_SERVER2 -i D:\RESTORE\restorequery1.sql
GOTO BEGIN


INDEX
-----

SQL_SERVER1= The SQL instance name of the Main Server
SQL_SERVER2= The SQL instance name of the newly configure Server (i.e the clinet machine which you loaded with Win 2k3 OS)
password1= The SA password of the Main Server
password2= The SA password of the newly configured Server

DESCRIPTIVE EXPLANATION OF THE ABOVE BATCH PROGRAM STEP BY STEP.

I. I have created a loop with the key words :BEGIN AND GOTO BEGIN which is first and last line of the batch program. This will create a loop so that when the program ends the GOTO will direct the control to the place where it find the word BEGIN. Hence a loop is created.
II. In the next few steps reproduced below I am opening the folder d:\restore (you can know this if you are familiar with DOS commands)
CD\
D:
CD RESTORE

III. In the next step reproduced below I am deleting the existing backup files which are present in the path "d:\restore" as they are file created during previous cycle.
DEL *.BAK

IV. Then I am logging into the SQL Server of the main server and backing up file in the main server itself in the path "E:\BACKUP" of main Server by calling the SQL Query "backup.sql" which we have saved in the client machine in the line given below.

osql -U SA -P password1 -S SQL_SERVER1 -i D:\RESTORE\BKPQUERY1.sql

(Leave a black space between each work after "osql" in the above line except the place where I have written the path)

V. Then in the next step reproduced below I am copying the backup files (*.bak files) from the shared folder of the Main Server to the local folder "d:\restore" of the client machine which we have newly configured.
COPY \\MainServerName\backup D:\RESTORE
After copying the backup files to the client using I am deleting the backup files in the Shared folder of the Main Server with the command
DEL \\MainServerName\backup *.bak

in order to clear the disk space in the Main Server.

VI. Then I am logging into the SQL Server of client machine and calling and executing the SQL Queries stored in the file restorequery1.sql in order to restore the backed up data into the SQL server of the client machine in the following code:

osql -U SA -P password2 -S SQL_SERVER2 -i D:\RESTORE\restorequery1.sql

VII. Then the program completes and starts from the beginning. 




Now the main advantage of using the above program is that you can have a back to back replication of the databases from one Server to another. Further if you are relying on "SQL Database Maintenance Plans" you are not sure whether the backup created can be restored properly. Where as in the above case as the backup and restoration happens back-to-back there wont be any problem.
Further the workload will only be on the Client Machine or the Additional Server which we newly configure and this process will not affect the performance of the Main Server thus ensuring normal functioning.
In the event of failure of the Main Server just go that office edit all the "config.xml" files of our Meghdoot programs in the each of the client machine with the new SQL Server Name and the office is up and running. For Sanchay Post as usual run the DB Analyser and updating the DCL user and the office is up and running.
Yes there will be data loss in the event of failure of the Main Server but the loss will be very minimum to quantity of just data fed in before 20 minutes of 30 minutes would have been lost. The reason being the above batch program to complete one cycle will take some 20 minutes time. If it is an HO and if the databases are more it may take time accordingly.

I HOPE THE ABOVE METHOD IS SUGGESTED WOULD BE USEFUL TO EVERYONE IN REDUCING THE DOWNTIME OF OUR OFFICE FUNCTIONING IN THE EVENT OF A SERVER BREAKDOWN. ANY OTHER BETTER SUGGESTION PLEASE POST IN THIS THREAD IF THIS METHOD IS NOT USEFUL ALSO PLEASE GIVE YOU FEEDBACK IT WILL HELP ME IN REFINING OF FINE TUNING THIS METHODOLOGY.