USE [master]
GO

/****** Object: StoredProcedure [dbo].[usp_AutoRestoreDB] Script Date: 1/23/2015 9:21:22 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_AutoRestoreDB]
AS
SET NOCOUNT ON;

DECLARE
@BackupFiles VARCHAR(500)
,@data_file_path VARCHAR(512)
,@log_file_path VARCHAR(512)
,@RestoreFileList VARCHAR(2000)
,@RestoreStatement VARCHAR(3000)
,@MoveFiles VARCHAR(2000)
,@DBName VARCHAR(150)

DECLARE @filelist TABLE
(
LogicalName NVARCHAR(128) NOT NULL
,PhysicalName NVARCHAR(260) NOT NULL
,[Type] CHAR(1) NOT NULL
,FileGroupName NVARCHAR(120) NULL
,Size NUMERIC(20,0) NOT NULL
,MaxSize NUMERIC(20,0) NOT NULL
,FileID BIGINT NULL
,CreateLSN NUMERIC(25,0) NULL
,DropLSN NUMERIC(25,0) NULL
,UniqueID UNIQUEIDENTIFIER NULL
,ReadOnlyLSN NUMERIC(25,0) NULL
,ReadWriteLSN NUMERIC(25,0) NULL
,BackupSizeInBytes BIGINT NULL
,SourceBlockSize INT NULL
,FileGroupID INT NULL
,LogGroupGUID UNIQUEIDENTIFIER NULL
,DfferentialBaseLSN NUMERIC(25,0) NULL
,DifferentialBaseGUID UNIQUEIDENTIFIER NULL
,IsReadOnly BIT NULL
,IsPresent BIT NULL
,TDEThumbprint VARBINARY(32) NULL
)

SET @data_file_path=’\\TAR-DBDBS\C$\SQLDATA\’
SET @log_file_path=’\\TAR-DBDBS\C$\SQLLOGS\’
SET @DBName=’DB’

–Get last full backup:
SELECT
@BackupFiles=COALESCE(@BackupFiles+’,’,”)+’DISK = N”’+physical_device_name+””
FROM
DBDBS.msdb.dbo.backupset S
JOIN
DBDBS.msdb.dbo.backupmediafamily M
ON M.media_set_id = S.media_set_id
WHERE
backup_set_id = (
SELECT
MAX(backup_set_id)
FROM
DBDBS.msdb.dbo.backupset S
JOIN
DBDBS.msdb.dbo.backupmediafamily M
ON M.media_set_id = S.media_set_id
WHERE
S.database_name = @DBName
AND Type = ‘D’
AND user_name = ‘user’
)

SELECT
@RestoreFileList=’RESTORE FILELISTONLY FROM ‘+@BackupFiles+’ WITH FILE = 1 ‘

IF (@@microsoftversion/0x1000000)&0xff >= 10 –TDE capability
BEGIN
INSERT INTO @filelist
(
LogicalName
,PhysicalName
,Type
,FileGroupName
,Size
,MaxSize
,FileID
,CreateLSN
,DropLSN
,UniqueID
,ReadOnlyLSN
,ReadWriteLSN
,BackupSizeInBytes
,SourceBlockSize
,FileGroupID
,LogGroupGUID
,DfferentialBaseLSN
,DifferentialBaseGUID
,IsReadOnly
,IsPresent
,TDEThumbprint
)
EXEC (@RestoreFileList)
END
ELSE
BEGIN
INSERT INTO @filelist
(
LogicalName
,PhysicalName
,Type
,FileGroupName
,Size
,MaxSize
,FileID
,CreateLSN
,DropLSN
,UniqueID
,ReadOnlyLSN
,ReadWriteLSN
,BackupSizeInBytes
,SourceBlockSize
,FileGroupID
,LogGroupGUID
,DfferentialBaseLSN
,DifferentialBaseGUID
,IsReadOnly
,IsPresent
)
EXEC (@RestoreFileList)
END

–next version, do a count on filename, any >1 put in alternate data/log location.
SELECT
@MoveFiles=COALESCE(@MoveFiles+’,’,”)+’MOVE N”’+LogicalName+”’ to N”’
+CASE
WHEN type = ‘D’ THEN @data_file_path+RIGHT(physicalname,CHARINDEX(‘\’,REVERSE(physicalname),1)-1)
WHEN type = ‘L’ THEN @log_file_path+RIGHT(physicalname,CHARINDEX(‘\’,REVERSE(physicalname),1)-1)
ELSE ‘Full Text – code not complete’
END+””
FROM
@filelist

SELECT
@RestoreStatement=’RESTORE DATABASE [‘+@DBName+’] FROM ‘+@BackupFiles+’ WITH FILE = 1, ‘+@MoveFiles+’, NOUNLOAD, REPLACE, STATS = 20′

PRINT @RestoreStatement

EXEC(@RestoreStatement)

GO