Here’s a handy-dandy proc for restoring a database backup file. You simply pass in the path of the backup, your new DB name, and the locations for data and log files.
This is especially helpful when your database has multiple files (10, 20, 50 files) due to partitioning or separated file groups. But even if you only have 2 files, it is still a time-saver.
USE Admin
IF OBJECT_ID('dbo.RestoreScriptGenerateFromBak') Is Not Null
DROP PROC dbo.RestoreScriptGenerateFromBak
GO
CREATE PROC dbo.RestoreScriptGenerateFromBak
@PathFilename varchar(300),
@NewDBName varchar(100),
@DataPath varchar(200),
@LogPath varchar(200)
AS
/*
DATE
AUTHOR REMARKS
4/16/14
PPaiva
Initial creation.
DESCRIPTION
Creates a restore script for a given backup file
with a common specified destination folder for data
files, and another for log files.
This is especially useful when making a copy
of a multi-file
(10, 50,
100+ files) database, as in the case of
partitioning and/or multiple file groups.
Click on Messages tab
to review script.
DEPENDENCIES
Admin.dbo.udfGetFilenameOnly()
RESTORE FILELISTONLY
USAGE
Exec RestoreScriptGenerateFromBak
'C:\Sql\Bak\ProdDB_20121116.bak',
'MyDevDB',
'E:\Sql\Data',
'L:\Sql\Logs'
Exec RestoreScriptGenerateFromBak
'C:\Paul\SqlDataFromClient\Sopa\Sopa_Feb27.bak',
'SopaFeb27',
'E:\Data',
'F:\Logs'
DEBUG
RESTORE FILELISTONLY
FROM DISK = 'C:\Paul\SqlDataFromClient\Sopa\Sopa_Feb27.bak'
*/
SET NOCOUNT ON
DECLARE @Sql varchar(Max),
@crlf varchar(2),
@Path varchar(200)
SET @crlf = CHAR(13) + CHAR(10)
SET @DataPath = dbo.udfAddBackslashIfNec(@DataPath)
SET @LogPath = dbo.udfAddBackslashIfNec(@LogPath)
CREATE TABLE #FileList(
LogicalName nvarchar(128) NOT NULL PRIMARY KEY,
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(22,0),
DropLSN numeric(25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit,
TDEThumbprint varbinary(32)
)
-- Determine version of SQL
DECLARE @Version varchar(10)
SELECT @Version = Convert(varchar(10), ServerProperty('ProductVersion'))
IF Left(@Version, 2) = '9.'
ALTER TABLE #FileList DROP COLUMN TDEThumbprint
SET @Sql = 'RESTORE FILELISTONLY
FROM DISK = ''' + @PathFilename + ''''
INSERT INTO #FileList
Exec (@Sql)
SELECT *
FROM #FileList
ORDER BY FileID
DECLARE @LogicalName nvarchar(128),
@Filename varchar(300),
@FileID int,
@MaxFileID int,
@Type char(1),
@NumIterations int
SELECT @MaxFileID = MAX(FileID),
@FileID = 0,
@NumIterations = 0,
@Sql = 'RESTORE DATABASE [' + @NewDBName + ']
FROM DISK = ''' + @PathFilename + '''
WITH '
FROM #FileList
WHILE @FileID < @MaxFileID
BEGIN
SET @NumIterations = @NumIterations + 1
-- Get next file
SELECT @FileID = MIN(FileID)
FROM #FileList
WHERE FileID > @FileID
-- Get other attribs for this FileID
SELECT @LogicalName = LogicalName,
@Filename = dbo.udfGetFilenameOnly(PhysicalName),
@Type = Type
FROM #FileList
WHERE FileID = @FileID
IF @NumIterations = 1
SET @Sql = @Sql
ELSE
SET @Sql = @Sql + '
'
--SELECT @Type Type, @DataPath DataPath,
@LogPath LogPath
IF @Type = 'D'
SET @Path = @DataPath
ELSE
BEGIN
SET @Path = @LogPath
-- Force suffix to be .ldf if a log file
SET @Filename = REPLACE(@Filename, '.ndf', '.ldf')
SET @Filename = REPLACE(@Filename, '.mdf', '.ldf')
END
SET @Sql = @Sql + 'MOVE ''' + @LogicalName + ''' TO ''' + @Path + @NewDBName + '_' + @Filename + ''',' + @crlf
END
-- Remove last comma
SET @Sql = LEFT(@Sql, len(@Sql) - 3)
Print @Sql
