Recover SQL 2000 from available backups.

Box was toast and database backups was what we had. To get this instance online we used a new box, renamed as like first, installed SQL server with same patch level however disk subsystem could not be mapped same way. Restoring user databases would have been straight but getting system databases was tricky. These are the steps that we followed.

  1. Restore master on instance.
    1. Restart instance with –m –c [-s InstanceName]
    2. Restore master (osql –ES [machine\instancename])

RESTORE DATABASE Master FROM DISK = ‘M:\DRdb\System\master_forDR_Jul3_b.bak’WITH replace

    1. Instance automatically turns off.
  2. Restart instance with –Q –T3608 [-s InstanceName]
    1. Turn off instance after restart
  3. Start SQL Service as in from Services.msc & restore System databases

RESTORE DATABASE [msdb] FROM  DISK = N’M:\DRdb\System\msdb_forDR_Jul3_b.bak’

WITH  FILE = 1, 

MOVE N’MSDBData’ TO N’m:\drsqlserver\MSSQL$DRSQLSERVER\data\msdbdata.mdf’, 

MOVE N’MSDBLog’ TO N’m:\drsqlserver\MSSQL$DRSQLSERVER\data\msdblog.ldf’, 


RESTORE DATABASE [model] FROM  DISK = N’M:\DRdb\System\model_forDR_Jul3_b.bak’

WITH  FILE = 1, 

MOVE N’modeldev’ TO N’m:\drsqlserver\MSSQL$DRSQLSERVER\data\model.mdf’, 

MOVE N’modellog’ TO N’m:\drsqlserver\MSSQL$DRSQLSERVER\data\modellog.ldf’,


    1. All the system databases are up while user databases are marked as suspect

select name , databasepropertyex (name,’status’) from sysdatabases

It was done this way because at step 1 after restoring master SQL knows where to look for files but that’s not correct as listed under master tables. SQL instance will not come up as it can’t find model and create tempDB, using trace flag 3608 causes SQL to start up only with master database. But without temp db you can’t restore any databases, an option is to edit master tables with and point to available model files or once can restart with -Q which will do this part for us. Restart the instance normally and restore rest original sys / user databases.


  1. Generate & run Scripts for FULL backup restoration (db other than SYS or suspect db). Scripts to be generated from MSDB & Master as available from latest tape backup.

select ‘RESTORE DATABASE [‘+ +’] FROM DISK = N”M:\DRdb\User\‘+ reverse(substring(REVERSE(BMF.physical_device_name),0, charindex(‘\’, REVERSE(BMF.physical_device_name),0))) +”’ WITH RECOVERY,REPLACE, MOVE N”’

+”’ to N”m:\drsqlserver\mssql$drsqlserver\data\’’_data.mdf”, MOVE N”’”’ to N”N:\drsqlserver\mssql$drsqlserver\data\’’_log.ldf”’


       sysdatabases sd left join (select rtrim(name) as name, dbid from sysaltfiles where fileid=1) DF

       on sd.dbid = DF.dbid

       left join (select rtrim(name) as name, dbid from sysaltfiles where fileid=2) LF

              on sd.dbid = LF.dbid

       left join msdb.dbo.backupset BS on BS.database_name =

       left join msdb.dbo.backupmediafamily BMF on BMF.media_set_id = BS.media_set_id


–where databasepropertyex (,’status’) <> ‘online’

–where not in (‘master’,’tempdb’,’model’,’msdb’)

       and convert(varchar(20),BS.backup_finish_date,112) = ‘yyyyMMdd’

and BS.user_name = ‘SqlBackupUserName’ and BS.type=’D’

Above script generates restoration statements, after model / msdb has been restored though you will require to recode file locations.

      Backup             M:\DRdb\User\

      MDF                 M:\drsqlserver\mssql$drsqlserver\data\

      LDF          N:\drsqlserver\mssql$drsqlserver\data\.

Testing after the DR migration

    1. Check for orphan users in all databases

DECLARE @command varchar (50)

SET @command  = ‘exec sp_change_users_login ”report”’

exec sp_MSforeachdb @command

    1. Check for all the users have a default database

select * from master..syslogins where dbname in (”, NULL)

    1. Check all users are able to connect top the server & dump to text file.

                     Set oFS = CreateObject(“Scripting.FileSystemObject”)

                     Set oTF = oFS.OpenTextFile(“UidPwd.txt”, 1)

                     Set oCn = CreateObject(“ADODB.Connection”)

                     Set oRs = CreateObject(“ADODB.recordset”)


                     ErrData =””

                     ConnData =””

                     Do while not oTF.AtEndOfStream

                     Str = oTF.Readline

                     On Error Resume Next

                     oCn.Open “Provider=SQLOLEDB;Data Source=aumel11db02,1463; User ID=” & mid(Str,                               1,Instr(Str,”#=-“)-1) & “; Password=” & mid(Str, Instr(Str,”#=-“)+3) & “;”

                     if err.number = 0 then 

                           oRs.Open “select user_name() as Usr, db_name() as DB”,oCn

                           ConnData = ConnData & ” ” & oRs.Fields(“Usr”) & ” connected to db -> ”  &                      oRs.Fields(“db”) &  vbCrLf




                           ErrData = ErrData & ” ” & err.Description & vbCrLf

                     end if


                     If ErrData <> “” then

                         wscript.echo ErrData

                         wscript.echo ConnData


                         wscript.echo ConnData

                     end if


About learnersstreak

For an insight in my doings, I'm involved in installation & support of SQL Server, performance optimization, virtualization & consolidation. At times into designing databases, more often with trouble shooting. Have automated some of the key tasks using DMO/SMO with VB script & powershell; leading to savings for a few professional tools. As with most DBA's I do all back-up / recovery for my bread & butter, while for high availablity I've implimented mirroring, replication & Log shipping as applicable with or without clusters. Doing SSIS for ETL as dataprovider.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s