semi-automated script to restore a SQL Server (2008/2012) database from a .bak file

restore.from.bak.sql

-- Michael Paulukonis 2014
-- notes @ http://www.xradiograph.com/Programming/SQL
-- adapted from http://sqlanywhere.blogspot.com/2011/06/installing-sql-server-database-from-bak.html

-- ASSUMES the BAK file and dest db have the same names

-- step2 needs to pull the logical names out of the .bak file

-- original version scripted a DROP of existing table
-- I've removed it from here, in order to force a manual backup-vs-delete decision


-- work in master
USE master
GO


DECLARE @dbname AS VARCHAR(25)
SET @dbname = 'dbname'

DECLARE @ext AS VARCHAR(4)
SET @ext = RIGHT(@dbname, 4)

-- this is a poor check
IF (@ext = '.bak')
	RAISERROR('Remove the ".bak" from path "%s"', 20, 1, @dbname) WITH LOG

-- location of existing .bak (not where .mdf will end up)
DECLARE @bakpath AS VARCHAR(100) -- end path with slash
SET @bakpath = '\path\to\bak\'


DECLARE @lastchar as VARCHAR(1)
SET @lastchar = RIGHT(@bakpath,1)

IF (@lastchar <> '\')
	RAISERROR('"%s" DOES NOT END WITH SLASH', 20, 1, @bakpath) WITH LOG

-- db destination
DECLARE @dbpath as VARCHAR(100) -- end path with slash
SET @dbpath = '\path\to\db\'


SET @lastchar = RIGHT(@dbpath,1)

IF (@lastchar <> '\')
	RAISERROR('"%s" DOES NOT END WITH SLASH', 20, 1, @dbpath) WITH LOG


-- EXEC when you are confident
-- PRINT when you want to see the output (and maybe manually tweak/test)

PRINT (
'
-- step 1
CREATE DATABASE ' + @dbname + '
ON (
        NAME = ' + @dbname + ',
        FILENAME = ''' + @dbpath + @dbname + '.mdf'',
                SIZE = 300MB
        )
LOG ON (
        NAME = ' + @dbname + '_log,
        FILENAME = ''' + @dbpath + @dbname + '.ldf'',
        SIZE = 100MB
        )
--GO

-- step 2
-- only the two "LogicalNames" are important
-- TODO: uh, we are not actual grabbing these with this method....
-- ASSUMPTION: names are [dbname] and [dbname]_log
-- but that is not always the case
RESTORE FILELISTONLY
        FROM DISK = ''' + @bakpath + @dbname + '.bak''
--GO   

--step 3
RESTORE DATABASE ' + @dbname + '
        FROM DISK = ''' + @bakpath + @dbname + '.bak''
        WITH MOVE ''' + @dbname + ''' TO ''' + @dbpath + @dbname + '.mdf'',
                 MOVE ''' + @dbname + '_log'' TO ''' + @dbpath + @dbname + '.ldf'',
                 REPLACE
--GO
'

)