SQL Server recovery steps with progress check

———————————— Useful TSQL recovery commands ——————————————-

–Listing the files and paths in a SQL Server backup file. Useful for recovering with the MOVE command

RESTORE FILELISTONLY
FROM DISK = N’\\external_storage\backup_1_20150209060001.BAK’
GO

–Listing information from a SQL Server backup file. Designate the first file of the backup set only.

RESTORE HEADERONLY
FROM DISK = N’\\external_storage\backup_1_20150209060001.BAK’
GO

–Recovering a database while moving recovered files to different locations

declare @backupfilename1 nvarchar(2000)
declare @backupfilename2 nvarchar(2000)
declare @backupfilename3 nvarchar(2000)
declare @backupfilename4 nvarchar(2000)
declare @backupfilename5 nvarchar(2000)
declare @backupfilename6 nvarchar(2000)
set @backupfilename1 = N’\\external_storage\backup1.BAK’
set @backupfilename2 = N’\\external_storage\backup2.BAK’
set @backupfilename3 = N’\\external_storage\backup3.BAK’
set @backupfilename4 = N’\\external_storage\backup4.BAK’
set @backupfilename5 = N’\\external_storage\backup5.BAK’
set @backupfilename6 = N’\\external_storage\backup6.BAK’
restore Database[PGAS_TEST] FROM
DISK =@backupfilename1,
DISK =@backupfilename2,
DISK =@backupfilename3,
DISK =@backupfilename4,
DISK =@backupfilename5,
DISK =@backupfilename6
WITH REPLACE,
MOVE ‘backup1’ TO ‘E:\MicrosoftSQLServer\MSSQL10.DPGS00\MSSQL\DATA\data1.MDF’,
MOVE ‘backup2’ TO ‘E:\MicrosoftSQLServer\MSSQL10.DPGS00\MSSQL\DATA\data2.MDF’,
MOVE ‘backup3’ TO ‘E:\MicrosoftSQLServer\MSSQL10.DPGS00\MSSQL\DATA\data3.MDF’,
MOVE ‘backup4 TO ‘E:\MicrosoftSQLServer\MSSQL10.DPGS00\MSSQL\DATA\data4.MDF’;
GO

———————————— Typical recovery scenario ——————————————-

–If the server itself is restored, the SQL Server software directories will be present, but the software will need to be installed over again.

–Look for the error.log file under the SQL Server home (D: drive). This log contains and instance name, paths for data and log files, and the build number. Use the build number and go to this URL (http://sqlserverbuilds.blogspot.com/) to determine what version and what SP needs to be applied.

–Remove the existing SQL Server directories. Take note of directories for options like OLAP and Reporting Services.  These are options that will need to be selected during installation.

–Once the software is installed, restart the new instance in single-user mode (Go to SQL Server Configuration Manager and add ‘;-m’ to the end of the startup parameters for the SQL Server service under its properties)

–Restart the SQL Server Service

–Recover the master and msdb databases from backup

–Restart the SQL Server service after removing the ‘;-m’ from its startup properties in the SQL Server Configuration Manager

–Recover the remaining databases from backup

–Progress check query

SELECT
session_id,
start_time,
status,
command,
percent_complete,
estimated_completion_time /60/1000 as estimate_completion_minutes,
DATEADD(n,(estimated_completion_time /60/1000),GETDATE()) as estimated_completion_time
FROM sys.dm_exec_requests where command = ‘BACKUP DATABASE’ OR command = ‘RESTORE DATABASE’
/

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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