Advertisements

Stored Proc for auto restoring a database

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

Advertisements

One Response

  1. […] my compadre DBA has a much more SQLY restore script here —-> https://michaelellerbeck.com/2015/01/23/stored-proc-for-auto-restoring-a-database/ […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: