Automated Restore Database from Last Full Backup

Your rating: None Average: 5 (2 votes)

In my environment, I backup to many files (for faster backups), and need to restore to a custom location. This query gets latest full backup info and restores to the path you specify. Tested on SQL 2005/2008.

 

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 = 'E:\SQLData\'
SET @log_file_path  = 'E:\SQLLog\'
SET @DBName = 'AuctionMain'

--Get last full backup:
SELECT @BackupFiles=COALESCE(@BackupFiles + ',', '') + 'DISK = N'''+physical_device_name+''''
FROM msdb.dbo.backupset S
JOIN msdb.dbo.backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE backup_set_id = ( SELECT MAX(backup_set_id)
                  
FROM msdb.dbo.backupset S
                  
JOIN msdb.dbo.backupmediafamily M ON M.media_set_id=S.media_set_id
                  
WHERE S.database_name = @DBName AND TYPE = 'D')

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)