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.
- Restore master on instance.
- Restart instance with –m –c [-s InstanceName]
- Restore master (osql –E –S [machine\instancename])
RESTORE DATABASE Master FROM DISK = ‘M:\DRdb\System\master_forDR_Jul3_b.bak’WITH replace
- Instance automatically turns off.
- Restart instance with –Q –T3608 [-s InstanceName]
- Turn off instance after restart
- 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’,
NOUNLOAD, STATS = 10
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’,
NOUNLOAD, REPLACE, STATS = 10
- 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.
- 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 [‘+ sd.name +’] 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”’ +DF.name
+”’ to N”m:\drsqlserver\mssql$drsqlserver\data\’+sd.name+’_data.mdf”, MOVE N”’
+LF.name+”’ to N”N:\drsqlserver\mssql$drsqlserver\data\’+sd.name+’_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 = sd.name
left join msdb.dbo.backupmediafamily BMF on BMF.media_set_id = BS.media_set_id
–where databasepropertyex (sd.name,’status’) <> ‘online’
–where sd.name 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.
Testing after the DR migration
- Check for orphan users in all databases
DECLARE @command varchar (50)
SET @command = ‘exec sp_change_users_login ”report”’
exec sp_MSforeachdb @command
- Check for all the users have a default database
select * from master..syslogins where dbname in (”, NULL)
- 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”)
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
If ErrData <> “” then