TECHNICAL SUPPORT: 4/8/12

In Sql server 2000

you start the server with trace flag 3608.In SQL Server Enterprise Manager, right-click the server name, and then click Properties.

On the General tab, click Startup Parameters.

Add the following new parameter:

-c -m -T3608

After this restart the sql server and detatch the msdb database and move the mdf and ldf files from the default folder C:\Program Files\Microsoft SQL Server\MSSQL\DAta

Then recreate msdb file by executing in new query window

instmsdb.sql (C:\Program Files\Microsoft SQL Server\MSSQL\Install)

Then remove-c -m -T3608 from the startup parameters in SQL Server Enterprise Manager

Then stop and restart the sql server 2000

and then recreate the maintenance plans

In SQL Server 2005

Detach the msdb database and install/create new msdb database. This will solve the problem. But the problem is that SQL Server 2005 does not allow to detach a system database. This will be solved by the following

another way Start--------------Sql Server 2005-------------Configuration Tools----------SQL Server Configuration Manager

Then click on SQL server 2005 services.-----On the right side----Right click on SQL Server----Select Properties

Click on the Advance Tab and change the Parameters of Start up Parameters ie please add -m;-c;-T3608 in front of the existing parameter.

Then restart SQL server

detach the msdb database by query

”use master

go

sp_detach_db ‘msdb’

go

and click Execute

”

Then move the existing mdf and ldf files of msdb from” C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data “

Then recreate msdb files open the nstmsdb.sql in a new query window and execute.The file is located in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install

Then your msdb is restored. Before restarting the sql server remove the parameters (-m;-c;-T3608 )Then restart the sql server. Be and recreate the new maintenance plan.

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.

TECHNICAL SUPPORT

link

Ad

Saturday, April 14, 2012