Script to Read Last full Backup and Restore With New Name

Query listed below will read the recent full backup from given database and restore it with new name.

--AUTORESTORE SCRIPT

DECLARE @NewDBName VARCHAR(250) ,

@DBName VARCHAR(250) = 'DEV_SFM',

@BackupLocation VARCHAR(2500),

@NewDataFileLocation VARCHAR(2500),

@DateStamp Varchar(8)

DECLARE --VARIABLES TO STORE PATH FOR SFM FILE GROUPS

@DEV_SFM VARCHAR(250),

@DEV_SFM_log VARCHAR(250),

@AMB VARCHAR(250),

@DM VARCHAR(250),

@IMM VARCHAR(250),

@ISS VARCHAR(250),

@EDI VARCHAR(250),

@RCM VARCHAR(250)

--READ LAST (FULL) BACKUP FOR GIVEN DATABASE NAME

SELECT @BackupLocation = physical_device_name

FROM msdb.dbo.backupset BS

INNER JOIN msdb.dbo.backupmediafamily BMF ON BMF.media_set_id = BS.media_set_id

WHERE Server_name = @@servername

AND database_name = @DBName

AND BS.type = 'D'--DATABASE

AND backup_finish_date = (SELECT MAX(backup_finish_date )

FROM msdb.dbo.backupset BS

WHERE Server_name = @@servername

AND database_name = @DBName

AND BS.type = 'D')

--ADD SUFFIX FOR NEW DATABASE NAME – MAKE SURE NO DATABASE EXISTS WITH THIS NAME FOR OTHER PURPOSE

SET @NewDBName = @DBName + '_AutoRestore'

--READ THE DATAFILE PATH FOR LOCATION

SELECT TOP 1 @NewDataFileLocation = physical_name

FROM master.sys.database_files

--PARSE THE DATAFILE PATH TO GET THE FOLDER

SELECT @NewDataFileLocation = REVERSE(@NewDataFileLocation),

@NewDataFileLocation = STUFF(@NewDataFileLocation, 1, CHARINDEX('\',@NewDataFileLocation, 0),''),

@NewDataFileLocation = REVERSE(@NewDataFileLocation)+ '\'

--GET A TIMESTAMP TO SUFFIX FOR DAFILES (THIS WILL HELP YOU TO UNDERSTAND ON WHAT DATE WAS THE DATABASE RESTORED)

SELECT @DateStamp = REPLACE(CONVERT(VARCHAR(10), GETDATE(),111), '/', '')

--ASSIGN DATAFILE VARIABLES WITH FULL PATH FOR EACH DATAFILE WITH DATESTAMP

SELECT @DEV_SFM = @NewDataFileLocation + @NewDBName + @DateStamp + '.mdf',

@DEV_SFM_log = @NewDataFileLocation + @NewDBName + @DateStamp + '_LOG.ldf',

@AMB = @NewDataFileLocation + @NewDBName + @DateStamp + '_AMB.ndf',

@DM = @NewDataFileLocation + @NewDBName + @DateStamp + '_DM.ndf',

@IMM = @NewDataFileLocation + @NewDBName + @DateStamp + '_IMM.ndf',

@ISS = @NewDataFileLocation + @NewDBName + @DateStamp + '_ISS.ndf',

@EDI = @NewDataFileLocation + @NewDBName + @DateStamp + '_EDI.ndf',

@RCM = @NewDataFileLocation + @NewDBName + @DateStamp + '_RCM.ndf'

--BACKUP EXISTS

IF @BackupLocation IS NOT NULL

BEGIN

--BRING DATABASE TO SINGLE USE MODE, INCASE THERE ARE ACTIVE CONNECTIONS

IF EXISTS(SELECT * FROM sys.databases WHERE name = @NewDBName)

BEGIN

EXEC ('ALTER DATABASE [' + @NewDBName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE')

END

RESTORE DATABASE @NewDBName

FROM DISK = @BackupLocation WITH REPLACE,

MOVE 'DEV_SFM' TO @DEV_SFM,

MOVE 'DEV_SFM_log' TO @DEV_SFM_log,

MOVE 'AMB' TO @AMB,

MOVE 'DM' TO @DM,

MOVE 'IMM' TO @IMM,

MOVE 'ISS' TO @ISS,

MOVE 'EDI' TO @EDI,

MOVE 'RCM' TO @RCM

END

ELSE

BEGIN

RAISERROR('Backup device could not found for database DEV_SFM',16,1)

END

There must be other ways as well… but this one reached my satisfaction. Always open to get suggestions to have better implementation.