Restore Database From Latest Restore

Here is a bit of code, adapted from Nigel Rivett’s original. You can use this to copy one database over another or restore the most recent database over itself.

--BEGIN TRAN

IF EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[s_RestoreLatestBackup]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE [dbo].[s_RestoreLatestBackup]
GO

CREATE PROC s_RestoreLatestBackup
@dbname VARCHAR(128) ,
@destdbname VARCHAR(128) ,
@localBackupPath VARCHAR(200) ,
@localDBPath VARCHAR(200) ,
@localLogPath VARCHAR(200) ,
@recipients VARCHAR(128),
@filenamesuffix VARCHAR(50)
AS /*
exec s_RestoreLatestBackup
@dbname = 'hrmprot' ,
@destdbname = 'HRMPROT2',
@localBackupPath = 'K:\Backups\' ,
@localDBPath = 'I:\Data\' ,
@localLogPath = 'J:\Logs\' ,
@recipients = 'myemailaddress',
@filenamesuffix = '_2'
*/

DECLARE @cmd VARCHAR(2000) ,
@filename VARCHAR(128) ,
@s VARCHAR(128) ,
@i INT ,
@d DATETIME ,
@sql NVARCHAR(2000) ,
@StartDate DATETIME

SELECT @StartDate = GETDATE()

-- drop database
IF EXISTS ( SELECT *
FROM master..sysdatabases
WHERE name = @destdbname )
BEGIN
SELECT @cmd = 'drop database ' + @destdbname
EXEC (@cmd)
END

-- get latest backup filename
SELECT @cmd = 'dir /B ' + @localBackupPath + @dbname + '\*.*'
PRINT @CMD
CREATE TABLE #a ( s VARCHAR(2000) )
INSERT #a
EXEC master..xp_cmdshell @cmd

DELETE #a
WHERE s IS NULL
OR s NOT LIKE '%_backup_%'

SELECT *
FROM #a

SELECT @filename = MAX(s)
FROM #a

-- Get files in backup
SELECT @cmd = 'restore filelistonly from disk = ''' + @localBackupPath
+ @dbname + '\' + @filename + ''''
PRINT @cmd
CREATE TABLE #files
(
[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 ,
[IsReadOnly] BIT ,
[IsPresent] BIT ,
[TDEThumbprint] VARBINARY(32)
)
INSERT #files
EXEC ( @cmd
)

SELECT *
FROM #files
-- buld the restore command
SELECT @cmd = NULL ,
@s = ''

WHILE @s < ( SELECT MAX(LogicalName)
FROM #files
)
BEGIN
SELECT @s = MIN(LogicalName)
FROM #files
WHERE LogicalName > @s


SELECT @cmd = COALESCE(@cmd + ', move ', '') + '''' + @s
+ ''' to '''
+ CASE WHEN #files.Type = 'D' THEN @localDBPath
ELSE @localLogPath
END
+ CASE WHEN #files.Type = 'D' THEN + LogicalName + @filenamesuffix + '.mdf'
ELSE LogicalName + @filenamesuffix + '.ldf'
END
+ ''''
FROM #files
WHERE LogicalName = @s
END
SELECT @cmd = 'restore database ' + @destdbname + ' from disk = '''
+ @localBackupPath + @dbname + '\' + @filename + ''' with move '
+ @cmd -- + ', standby = ''' + @localBackupPath + 'standby.fil'''

PRINT @cmd

--restore the database
select (@cmd)

exec (@cmd)

drop table #files
drop table #a

-- Check that the restore was successful
select @sql = 'select @i = count(*) from ' + @dbname + '..sysobjects select @d = crdate from master..sysdatabases where name = ''' + @dbname + ''''
exec sp_executesql @sql, N'@i int out, @d datetime out', @i out, @d out

/*
if @d > @StartDate and @i > 20
begin
if @recipients is not null
begin
select @cmd = 'restore ' + @filename + ' completed successfully - started ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 108)
exec master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
end
end
else
begin
select @cmd = '********************* restore ' + @filename + ' failed - started ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 108)
if @recipients is not null
begin
exec master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
end
raiserror(@cmd, 16, -1)
end
*/
go

--COMMIT TRAN

Leave a Reply

Your email address will not be published. Required fields are marked *