Restoring Master and System database : Disaster recovery

Question

Post reply

Restoring Master and System database : Disaster recovery

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

sswords SSCrazy Eights Points: 8207 More actions · Answer 1

Just a thought for what it's worth. It seems like you're going through a lot of trouble just with restoring Master and the system databases. In a DR situation, it might be easier just to reinstall SQL Server and let it build its own new system DB's then just copy over and attach or restore your user db's.

This of course won't work by itself if you've got gobs of users, DTS packages, etc. In that case, you'd have to have a mechanism setup to script all this stuff out periodically so that it could be backed up and restored. Still, it seems easier to me than trying to shoe-horn another server's copy of the system databases onto an entirely new location.

My hovercraft is full of eels.

devereauxj Hall of Fame Points: 3263 More actions · Answer 2

For those who were waiting, here are my results. I basically cut and pasted the 2nd attempt at the documentation. It is good enough that I can duplicate each time. Not yet ready to be view by management.

SSWORDS: The reason to spend the months getting this to work is that I have 18 production servers with 42 instances running (not including Oracle servers), all but 5 have a 3 day or less restore time at time of disaster. Only 6 are mirrored. Each has its own setup of users and security as well as jobs. By restoring the system databases, I no longer have maintain a scripts for each of the instance on users, security, and jobs.

Below is my Dr solution. I don't understand why a couple things work or what the "undocumented" parameter does. Will figure those out later. A couple things have to do with the tape robotic system and Tivoli software.

Terms:

------

Target : newly installed or local instance of sql server where the databases will be restored to

Source : the instance of sql server on tape that the database and information will come from

--> : command issued from dos command line

SSQA--> : command issued from SQL Server Query Analyzer

*************************************************************************************************

DR Steps and procedures:

------------------------

New install of SQL Server 2000 with the correct version and collation.

SQL Server Version:

SQL Server SP:

SQL Server Collation:

Collation Description:

SQL Server Key:

------------------------

AA) Modify the d:\...\system_variables.cmd

The paths and instance names will need to be changed if the new install was not identical to the

previous server.

01) In Enterprise Manger (EM) add admin operators (nfg_domain\<DBA GROUP>, nfg_domain\<operator GROUP&gt logins as sysadmin

02) In EM remove Administrators login

03) From EM remove pubs and northwind databases

04) Apply appropriate service pack(s)

05) Set the following services to manual, change "log on as" so they run as nfg_domain\<operator GROUP> and shut them down

MSSQL$<instance_name>

SQLAgent$<instance_name>

06) Restore Master Database

A) The parameter information can be found in d:\tdp_backups\scripts\system_variables.cmd

Use d:\tdp_backups\scripts\sql_tdp_restore.cmd to restore the master database

In sql_tdp_restore.cmd modify variables and use Duplicate_Active, Full Backup Restore Only

B) From dos prompt: go to the binn directory of the sql server instance that is the target

Issue the following command to bring SQL Sever up in single user mode

Modify the the path and SQL Server instance name

-->sqlservr -slatin_cp850_cias -m -c -e"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\ERRORLOG" -d"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\data\master.mdf" -l"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\data\mastlog.ldf"

C) Run sql_tdp_restore.cmd to restore the master database

The instance will stop after the database is restored

07) Clean up old instance/database information

A) startup sql with the command below: modify the instance name and path

-->sqlservr -slatin_cp850_cias -m -Q -T3608 -e"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\ERRORLOG" -d"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\data\master.mdf" -l"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\data\mastlog.ldf"

B) Open query analyzer and verify restore by running the following command

SSQA-->select @@servername, @@servicename

servername should be the source server, servicename should be the instance installed

C)To find the databases the master database knows about issue this command

SSQA-->select * from sysdatabases

D) Issue the following commands to reset the tempdb files and location, modify the path

SSQA-->update sysdatabases SET filename='D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\Data\tempdb.mdf' where name = 'tempdb'

SSQA-->go

SSQA-->update sysdatabases SET filename='D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\Data\tempdb.mdf' where name = 'tempdb'

SSQA-->go

SSQA-->alter database tempdb modify file (name = tempdev, filename = 'D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\Data\tempdb.mdf')

SSQA-->alter database tempdb modify file (name = templog, filename = 'D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\Data\templog.ldf')

SSQA-->go

You should get a message that tells you to delete the old temp database files after you shutdown SQL Server

E) Detach/Attach the model database and modify the path

SSQA-->sp_detach_db 'model'

SSQA-->go

SSQA-->sp_attach_db 'model', 'D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\data\model.mdf','D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\data\modellog.ldf'

SSQA-->go

F) Stop instance instance using ctrl-C at dos window

G) Delete old temp files

H) Startup sql server instance, modify the path

-->sqlservr -slatin_cp850_cias -m -Q -T3608 -e"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\ERRORLOG" -d"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\data\master.mdf" -l"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\data\mastlog.ldf"

I) From query analyzer

SSQA-->select * from sysdatabases

If it worked, the temp database should have the new path, all the other databases should have the same path

J) Stop sql server instance, start it, modify path

-->start sqlservr -slatin_cp850_cias -m -f -c -e"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\ERRORLOG" -d"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\data\master.mdf" -l"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\data\mastlog.ldf"

K) Detach user databases

SSQA-->sp_detach_db '<db_name>'

SSQA-->go

L) stop sql server instance, start it, modify path

-->sqlservr -slatin_cp850_cias -m -Q -T3608 -e"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\ERRORLOG" -d"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\data\master.mdf" -l"D:\PROGRAM FILES\Microsoft SQL Server\<instance name>\data\mastlog.ldf"

M) Detach the MSDB database

SSQA-->sp_detach_db 'msdb'

SSQA-->go

N) shutdown at command window : ctrl-c

O) Remove old msdb files

08) Open EM and startup the sql server instance

09) Verify that the SQLAgent is NOT running and in manual, the service is SQLAgent$<instance_name>

10) Modify and run sql_tdp_restore.cmd to restore the MSDB database

In sql_tdp_restore.cmd modify variables and use Duplicate_Active, Full Backup with all logs

11) From EM shut down startup sql instance normally

Once up, to do Management --> SQL Server Agent --> right click and start

12) From SQL Analyzer

use sp_dropserver/sp_addserver to change internal servername

you can get the server name by

SSQA--> select @@servername

SSQA--> sp_dropserver '<@@servername>'

SSQA--> GO

SSQA--> sp_addserver '<new sql server instance name>','LOCAL'

SSQA--> go

13) From EM shut down startup normally, start SQL Server Agent

14) To verify the variable is changed

SSQA-->select @@servername

The output should show the new SQL Server instance name

15) Restore user databases by modifing sql_tdp_restore.cmd

j mangual Ten Centuries Points: 1197 More actions · Answer 3

j mangual

Ten Centuries

Points: 1197

May 18, 2006 at 12:26 pm

#638838

Currently trying to test restore scenario from backup on our shop, similar with what you tried in here. However, on Step 6A you refer to the parameter information on the d:\...system_variables.cmd. Is this a file you created? What about the variables need to be modify on the sql_tdp_restore.cmd file?

devereauxj Hall of Fame Points: 3263 More actions · Answer 4

Yes, it is a file I created. Basically, going from server to server the scripts stay the same except for this file. It contains many variables. I use it for the Dr/restore/duplications/backups. Below is an example. It make promoting and matenance easier. Our new DBA thinks it is a bit confusing, but once you have the process down, it makes a lot of sense.

set tdp_config_file01=srvmain022_gmii.cfg

set tdp_sql_inst01=srvmain022_gmii

set tdp_opt_file=srvmain022.opt

set tdp_config_opt_dir=d:\tdp_backups\cfg_opt

set tdp_output_dir=d:\tdp_backups

set tdp_reports_dir=d:\tdp_backups\reports

set tdp_scripts_dir=d:\tdp_backups\scripts

Below is an exmpale from my sql_tdp_resore.cmd file

set lv_config_file=%tdp_config_opt_dir%\%tdp_config_file01%

set lv_opt_file=%tdp_config_opt_dir%\%tdp_opt_file%

now starting %~n0 >> %tdp_output_dir%\%~n0.out

tdpsqlc restore <db_name> full /configfile=%lv_config_file% /tsmoptfile=%lv_opt_file% /mountwait=yes /traceflags=service,api /tracefile=%tdp_output_dir%\cmdtrace_%tdp_sql_inst01%.out >> %tdp_output_dir%\%~n0.rst

Joe