Restore database script

restore.sql

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