Skip to content

Instantly share code, notes, and snippets.

@mat3u
Created Mar 21, 2014
Embed
What would you like to do?
Restore database script
CREATE procedure [dbo].[db_restore](@Dbname nvarchar(200), @File nvarchar(max))
AS
BEGIN
declare @sql nvarchar(max)
declare @rowsPath nvarchar(max)
declare @logPath nvarchar(max)
declare @dbRows nvarchar(255)
declare @dbLog nvarchar(255)
IF (SELECT COUNT(*) FROM sys.databases WHERE name = @Dbname) = 0
BEGIN
set @sql=N'CREATE DATABASE ' + @DbName
exec sp_executesql @sql
END
declare @fileListTable table
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
[Type] char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(25,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,
IsReadOnl bit,
IsPresent bit,
TDEThumbprint varbinary(32) -- remove this column if using SQL 2005
)
INSERT INTO @fileListTable EXEC('RESTORE FILELISTONLY FROM DISK= N''' + @File + '''')
SELECT @dbRows = LogicalName FROM @fileListTable WHERE Type = 'D'
SELECT @dbLog = LogicalName FROM @fileListTable WHERE Type = 'L'
SELECT @rowsPath = physical_name FROM sys.master_files m
JOIN sys.databases d ON m.database_id = d.database_id
WHERE d.name = @Dbname AND type = 0
SELECT @logPath = physical_name FROM sys.master_files m
JOIN sys.databases d ON m.database_id = d.database_id
WHERE d.name = @Dbname AND type = 1
set @sql=N'alter database ' + @DbName + ' SET single_user with rollback immediate'
exec sp_executesql @sql
set @sql=N'RESTORE DATABASE ' + @DbName + ' FROM DISK = N''' + @File + ''' WITH FILE = 1, MOVE N''' + @dbRows + ''' TO N''' + @rowsPath + ''', MOVE N''' + @dbLog + ''' TO N''' + @logPath + ''', NOUNLOAD, REPLACE, STATS = 10'
exec sp_executesql @sql
set @sql=N'alter database ' + @DbName + ' SET multi_USER'
exec sp_executesql @sql
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment