Best Practice for Restoring a SQL Server Database

SQL Server Administration
1

Following conversation in another thread I thought I would post the syntax I use for restoring a database in case anyone has any suggestions / comments, or does it differently.

I have a Stored Procedure that I run which basically needs the Database Name (either to overwrite an existing database, or to create a new one) and a Path & Filename for the restore file.

Given those parameters my SProc will "suggest" the syntax for a full RESTORE command. I can then twiddle with the parameters (e.g. Target folder for the restored MDF/LDF files, or Logical Names etc., whether I want TLog files restored too, and so on) and eventually I wind up with the full syntax for a RESTORE which I can then execute.

My SProc does NOT make the Restore, it JUST generates the syntax.

Here's an example of a "session":

First up I type:

EXEC My_SP_Restore

(I usually prefix it with the name of the Admin DB, unless I am "in" that DB of course)

This is what I get:

WARNING            Database Name
------------------ -------------
Database not found NULL

-- Execute syntax:
------------------
(Version 150822.SQL2012)
Cut and paste the command below to your execute window.
Execute ONLY as many parameters as you have COMPLETED and then
hints will be displayed for subsequent parameters
-------------------------------------------------

note that the syntax example does include the admin database name so I can exec it anywhere. This is the sample syntax that the Sproc generates, which I then cut & paste over my original attempt thereby gradually refining it:

exec MyAdminDB.dbo.My_SP_Restore 
	@DatabaseName='MyDbName'		-- Database name, or '?' for a list
	**** DATABASE NOT FOUND ****
		ActualDBName1
		ActualDBName2
		...
,	@RestorePath='X:\Path'	-- Path to file to be restored
,	@RestoreFile='MyFileName.BAK'	-- Name of file to restore

I can now copy & paste one of the Actual DB Names from the list of databases, although that is optional (e.g. restoring to create a new database)

The @RestorePath example is the default backup path, but I can obviously change that to any other, non-standard- location.

So now I reissue the command as either:

exec MyAdminDB.dbo.My_SP_Restore 
	@DatabaseName='ActualDBName1'

or better still (because it saves an iteration) I also include the filespec to restore from:

exec MyAdminDB.dbo.My_SP_Restore 
	@DatabaseName='ActualDBName1'
,	@RestorePath='X:\Path'
,	@RestoreFile='ActualDBName1*FULL*.BAK'

and this time the example syntax is:

-- Execute syntax:
-----------------
exec MyAdminDB.dbo.My_SP_Restore 
	@DatabaseName='ActualDBName1'
,	@RestorePath='X:\Path'
,	@RestoreFile='ActualDBName1*FULL*.BAK'
	**** Database/Restore Filename Mismatch ****
--	  23/08/2015  04:01  12,618,240 ActualDBName1_20150823_040100_Full.BAK
--	  22/08/2015  15:13  12,533,248 ActualDBName1_20150822_151347_Full.BAK
...

After the @RestoreFile parameter it lists, in chronological order, all the matching filenames so that I can copy & paste one into the @RestoreFile parameter:

So now I execute this modified version:

exec MyAdminDB.dbo.My_SP_Restore 
	@DatabaseName='ActualDBName1'
,	@RestorePath='X:\Path'
,	@RestoreFile='ActualDBName1_20150823_040100_Full.BAK'

and I get this:

-- Execute syntax:
------------------
exec MyAdminDB.dbo.My_SP_Restore 
	@DatabaseName='TEMP_ActualDBName1'
,	@RestorePath='X:\Path'
,	@RestoreFile='ActualDBName1_20150823_040100_Full.BAK'
--	  23/08/2015  04:01  12,618,240 ActualDBName1_20150823_040100_Full.BAK
--	***** LOGICAL NAMES *****
-- RESTORE FILELISTONLY FROM DISK
--		'X:\Path\ActualDBName1_20150823_040100_Full.BAK'
-- 	ActualDBName1_dataType = D,
--		Original filename=E:\MSSQL\Data\ActualDBName1.mdf,
--		File Group=PRIMARY
-- 	ActualDBName1_logType = L,
--		Original filename=F:\MSSQL\Log\ActualDBName1.ldf,
--		File Group=[No group]
-- RESTORE HEADERONLY FROM DISK
--		'X:\Path\ActualDBName1_20150823_040100_Full.BAK'
-- 		User: MyDomain\SQL-Service,
--		ServerName: MyServerName,
--		DatabaseName: ActualDBName1,
--		DB Created: 28 Jun 2015 10:20:29:000,
--		Collation: Latin1_General_CI_AS,
--		BackupName: ActualDBName1( Full Backup ),
--		Description: Full Backup at 23 Aug 2015 04:01:00:600.

and it also adds these parameters (only available once Database Name and File Name are available)

,	@MSSQLDataPath='E:\MSSQL\Data'	-- Path to SQL data location
,	@MSSQLLogPath='F:\MSSQL\Log'	-- Path to SQL Log location
,	@sLogicalDataName='ActualDBName1_data'	-- Logical name of DATA (See below)
,	@sLogicalLogName='ActualDBName1_log'	-- Logical name of LOG (See below)
-- Comment this IN to add TLog files to the Restore Command:
--,	@sTLogFile='ActualDBName1*Trans.bak'	-- Filepec for LOG backup files

This is now the final syntax - if I EXEC this there is enough information to generate the RESTORE command, and that will be what I get next.

This shows exactly which file I will be restoring, the headers from the file and so on so that I can check that I have got everything right. I can check original file path / drives that the backup was made from, date/time, collation, all that sort of stuff

There are also a few warnings that can be generated - e.g. if Target Database Name does not match, in some way, the Restore Filename. That has saved me, in the past, when restoring LOTS of files to a new server when I am in a hurry I can mixup the Filename and the Target Database name and restore the wrong thing to the wrong place.

It also sets Logical Names for Data and Log. I have preferred formats for these, so if the Backup file logical names are different the restored database will be changed to my these parameter values. This is particularly important when I restore a database to a different name, and ensures that the Logical Names match the target Database names, and not the Source Database

If I comment IN the @sTLogFile parameter the RESTORE syntax will include commands for all the TLog files found (I will have to delete any that are too early / too late)

2

This is the actual generated syntax

-- Execute syntax:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
------------------
-- INSTRUCTIONS:

-- Check you want to restore ** MyServerName : TEMP_ActualDBName1 **

-- Make, optional, alterations to this script IF

--   o You also have Transaction or Differential backups to restore
--   o You want to, also, update statistics
--   o You need to change the restored database to READ_WRITE - e.g. it was READ_ONLY at backup 

-- Then execute the script below

-- ==================== START ==================== --

PRINT 'Note database settings before and after'
exec sp_helpdb 'TEMP_ActualDBName1'
PRINT '----------------------------------------'

USE master	-- (Can't sit in the database whilst its being restored!)
GO

-- ALTER DATABASE TEMP_ActualDBName1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE TEMP_ActualDBName1 SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE TEMP_ActualDBName1
	FROM DISK = 'X:\Path\ActualDBName1_20150823_040100_Full.BAK'
	WITH
		REPLACE,
		NORECOVERY,
		STATS = 10,	-- Show progress (every 10%)
	MOVE 'ActualDBName1_data' TO 'E:\MSSQL\Data\TEMP_ActualDBName1.mdf', 
	MOVE 'ActualDBName1_log' TO 'F:\MSSQL\Log\TEMP_ActualDBName1.ldf'
GO
/*
-- Template for DIFF restore:
RESTORE DATABASE TEMP_ActualDBName1
	FROM DISK = 'X:\Path\TEMP_ActualDBName1_yyyymmdd_hhmm_Diff.BAK'
	WITH
		STATS = 10,	-- Show progress (every 10%)
		NORECOVERY
*/
GO

/*
-- Template for LOG restore:
RESTORE LOG TEMP_ActualDBName1
	FROM DISK = 'X:\Path\TEMP_ActualDBName1_yyyymmdd_hhmm_Trans.BAK'
	WITH
		NORECOVERY
--		, STOPAT = '19991231 23:59:59.999'
*/
GO
-- Now activate the database
RESTORE DATABASE TEMP_ActualDBName1 WITH RECOVERY
GO

-- Optional setting of database options:
-- ALTER DATABASE TEMP_ActualDBName1 SET MULTI_USER, READ_WRITE WITH ROLLBACK IMMEDIATE
GO

-- Rename logical names:
	ALTER DATABASE TEMP_ActualDBName1 MODIFY FILE (NAME = 'ActualDBName1_data', NEWNAME = 'TEMP_ActualDBName1_data')
GO
	ALTER DATABASE TEMP_ActualDBName1 MODIFY FILE (NAME = 'ActualDBName1_log', NEWNAME = 'TEMP_ActualDBName1_log')
GO

PRINT '----------------------------------------'
exec sp_helpdb 'TEMP_ActualDBName1'
PRINT '----------------------------------------'
PRINT ''

USE TEMP_ActualDBName1
GO

-- Include if a Data Consistency Test is required on the restored database 
-- DBCC CHECKDB('TEMP_ActualDBName1') WITH ALL_ERRORMSGS, NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY
GO

-- List any User/Group profiles that need creating/reinstating
PRINT '-- *** Cut&Paste the following code and EXECUTE any appropriate snippets ***'
SET NOCOUNT ON
SELECT	
	'PRINT ''User: ' + COALESCE(U_name, '[NULL]') + ''';',
	CHAR(13)+CHAR(10)+ CASE WHEN L_sid IS NOT NULL 
		THEN '-- User ' + COALESCE(U_name, '[NULL]') + ' already exists on server'
		ELSE 'EXEC sp_addlogin @loginame=''' + U_name + ''', @passwd =''password'', @defdb=''' + db_name() + '''' 
			+ CHAR(9) + '-- Only add if required!!'
		END,
	CHAR(13)+CHAR(10)+ '-- EXEC '
	+ CASE WHEN db_name() LIKE '%[^A-Za-z0-9_]%' THEN QuoteName(db_name()) ELSE db_name() END
	+ '.dbo.sp_dropuser @name_in_db='
	+ '''' + U_name + '''	-- Remove this user if access is no longer required to this DB',
	CHAR(13)+CHAR(10)+'EXEC '
	+ CASE WHEN db_name() LIKE '%[^A-Za-z0-9_]%' THEN QuoteName(db_name()) ELSE db_name() END
	+ '.dbo.sp_change_users_login @Action=''Update_One'', '
	+ '@UserNamePattern=''' + U_name + ''', '
	+ '@LoginName=''' + U_name + ''''
FROM
(
SELECT DISTINCT
	[U_name] = U.[name],
	[L_sid] = L.sid
FROM	sysusers AS U
	LEFT OUTER JOIN
	(
		sysmembers AS M
		INNER JOIN sysusers AS G
			 ON G.uid = M.groupuid
	)
		 ON M.memberuid = U.uid
	LEFT OUTER JOIN master.sys.sql_logins AS L
		 ON L.[name] = U.[name] COLLATE SQL_Latin1_General_CP1_CI_AS
where	    U.islogin = 1 
	and U.isaliased = 0 
	and U.hasdbaccess = 1
	and 
	(
		   G.issqlrole = 1 
		or G.uid is null
	)
	AND U.name NOT IN ('dbo')
) AS X
ORDER BY CASE WHEN L_sid IS NOT NULL THEN 1 ELSE 2 END, [U_name]
-- Update statistics **OPTIONAL**:
-- It may be necessary to update statistics after restore,
-- but this can take a long time

-- EXEC sp_updatestats
GO

PRINT '-- My_SP_Restore DONE'
-- ==================== END ==================== --
3

A couple of observations:

My SProc sets the TARGET location for MDF / LDF as per configuration for THIS server. It ignors anything in the restored file about the original location of the file. Commonly I am restoring from Production to DEV or from an Old Server onto a New Server and preferred folder for Target Server is my better choice.

I think the default (in SSMS GUI) is to retain the path from the restored file?

Similarly with the Logical Names. I change them to match the Database Name by default, rather than Retain them. If I restore a database from a backup file originally made from the FOO_PROD database (which might have itself been renamed from FOO_TEST) to BAR_DEV I don't want to retain the original FOO logical name. YMMV :sunglasses: