USE [DBA]
GO


DBCC CHECKDB ('<dbname>')


BACKUP DATABASE [ISRS] 	
 	TO DISK = N'\\<unc path>\<dbname>_<datetime>.BAK' 	
	WITH NOFORMAT, INIT,  NAME = N'ISRS UAT-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- Run This next block through the Verify

DECLARE @backupSetId int

SELECT @backupSetId = position 
  FROM msdb..backupset 
 WHERE database_name=N'<dbname>' 
   AND backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'<dbname>' )
   
IF @backupSetId is null 
  BEGIN 
	raiserror(N'Verify failed. Backup information for database ''<dbname>'' not found.', 16, 1) 
  END

RESTORE VERIFYONLY	
 	FROM DISK = N'\\<unc path>\<dbname>_<datetime>.BAK' 
	WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO


-- Run this to test the back up.  Once the Backup Verify is restored, go check the verify database

-- Check for logical file names and locations

RESTORE FILELISTONLY 
	FROM DISK = N'\\<unc path>\<dbname>_<datetime>.BAK'
GO


-- If anyone is in Verify, Kick them out

USE [master]
GO

ALTER DATABASE <dbname>_VERIFY SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- Restore Database to Verify

RESTORE DATABASE <dbname>_VERIFY
	FROM DISK = N'\\<unc path>\<dbname>_<datetime>.BAK'
   WITH   MOVE '<dbname>'		TO '\\<unc db fp>\<dbname>_VERIFY.MDF'
        , MOVE '<dbname>_LOG'	TO '\\<unc db fp>\<dbname>_VERIFY.LDF'
	, REPLACE
GO

-- Put the database into Multi user and read write mode

ALTER DATABASE <dbname>_VERIFY SET READ_WRITE
ALTER DATABASE <dbname_VERIFY SET MULTI_USER
GO