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:
(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)