A Script A Day - Day 16 - Database Restore

Today’s script is one I have used more times that I care to remember.  As a DBA database backups and restores are your bread and butter, they are second nature (or should be;).  By this I mean good old T-SQL not using SSMS, I can honestly say I have never backed up or restored a database using SSMS, I have used EM in SQL 2000 in my pre DBA days though.

So for all you DBA’s forgive the basic nature of the script but not everyone knows T-SQL and thus wouldn’t be able to restore a database using it.  See my Database 101 post on humility. 

For everyone else the script will restore the SQLServer365 database from a backup and will overwrite the existing files.  It uses RESTORE FILELISTONLY to get the files in the backup and sp_helpfile to get the existing file locations.  The LocicalName column values from RESTORE FILELISTONLY are used to specify what we are MOVEing and the filename column values from sp_helpfile are used to specify where we are MOVEing them TO.

/*

      -----------------------------------------------------------------

      Restore Database

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Set database to the user database

USE SQLServer365;

GO

-- Get file list from backup

RESTORE FILELISTONLY FROM DISK = 'D:\Backups\SQLServer365_20120222.bak';

GO

-- Return database file locations

EXEC dbo.sp_helpfile;

GO

-- Set database context to master

USE master;

GO

-- Restore the database

RESTORE DATABASE SQLServer365

FROM DISK = 'D:\Backups\SQLServer365_20120222.bak'

WITH REPLACE, MOVE 'SQLServer365_Data' TO 'D:\SQL\Data\SQLServer365_Data.mdf', MOVE 'SQLServer365_Log' TO 'D:\SQL\Log\SQLServer365_Log.ldf';

GO

Enjoy!

Chris