USE [master]
GO


-- Check to see that the DB is in Good Health

DBCC CHECKDB ('<dbname>')
GO

-- Back up DB to a local drive on the server, not across the network

BACKUP DATABASE [<dbname>] 	
TO DISK = N'<fp>\<servername>_<instancename>_<dbname>_D<yyyymmdd>_T<hhmiss>.BAK'	
	WITH NOFORMAT, INIT,  NAME = N'<dbnaem> 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'<fp>\<servername>_<instancename>_<dbname>_D<yyyymmdd>_T<hhmiss>.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'<fp>\<servername>_<instancename>_<dbname>_D<yyyymmdd>_T<hhmiss>.BAK'	
GO

-- Test the Backup

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

-- Restore Database to Verify

RESTORE DATABASE <dbname>_VERIFY
FROM DISK =   	N'<fp>\<servername>_<instancename>_<dbname>_D<yyyymmdd>_T<hhmiss>.BAK'	
   WITH   MOVE '<dbname>'		TO '<fp DBFile>\<dbname>\<dbname>_VERIFY.MDF'
        , MOVE '<dbname>_LOG'	TO '<fp DBFile>\<dbname>\<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

-- Check out the Verify DB to make sure it looks good.

USE [<dbname>_VERIFY]
GO