MS SQLServer database nightly refresh script and solution

A daily refresh copy of the production database will be created on the mirror database server, The purpose is to offload the reporting activities from the production database.  This will also intended to alleviate the database locks for users that are performing non-reporting activities and to gain improved performance when generating the reports.  The following steps will outline how to create the automated database refresh process.

Create the daily refresh database.

1.       Create the daily refresh database by restoring a backup of the Production database from \\shared.

2.       Name the database, PROD_Refresh_Reports.

Create a Linked Server

1.       Create a Linked Server to the primary database server .  This will be used to retrieve the backup information of the Prod database to verify if a backup file was created.

Create daily refresh SQL Agent job.

The job is scheduled to run Monday – Friday at 3:00am.  The job will check if the database backup of PROD was completed.  A notification of the jobs success or failure will be sent to the DBA team.

Script for the database refresh:

DECLARE @failureTEXT nvarchar(400)

Set @failureTEXT = 'The backup job on prod failed.  No backup file was created.'

DECLARE @successTEXT nvarchar(400)

Set @successTEXT = 'The database restore was successful.'

DECLARE @filename nvarchar(500)

DECLARE @currentyear nvarchar(4)

SET @currentyear = year(getdate())

DECLARE @currentmonth char(2)

SET @currentmonth = SUBSTRING(CONVERT(nvarchar(6),getdate(), 112),5,2)

DECLARE @currentday nvarchar(2)

SET @currentday = SUBSTRING(CONVERT(nvarchar(8),getdate(), 112),7,2)

--Query the linked server prod to find the database backup file name for Prod created for the current day.

DECLARE @backuppath nvarchar(500)

select @backuppath = physical_device_name from agends515.msdb.dbo.backupmediafamily where physical_device_name like 'S:\\shared\PROD_backup_' + @currentyear + '_' + @currentmonth

+ '_' + @currentday + '%' + '.bak'

--If the database backup file was not created for the current day, abort and send an email to notify the DBA team.

IF @backuppath is null

BEGIN

--Send email using database mail on failure.

EXEC msdb.dbo.sp_send_dbmail

    @recipients = 'dba@corp.ca',

    @subject = 'prod_Refresh_Reports database restore',   

    @body = @failureTEXT,  

    @body_format = 'TEXT'; 

END

--If the database backup file was created for the current day, refresh the prod_Refresh_Reports database from the backup file.

Else IF @backuppath is not null

BEGIN

--Define the path and file name of the database backup that will be used for the refresh.

SET @filename = 's:\shared\' + substring(@backuppath, 5, 500)

USE master

--Kill existing connections on the prod_Refresh_Reports database before refreshing.

ALTER DATABASE prod_Refresh_Reports SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

--Refresh the database.

RESTORE DATABASE [prod_Refresh_Reports] FROM  DISK = @filename WITH  FILE = 1, 

MOVE N'PROD' TO N'E:\folder\prod_Refresh_Reports.mdf', 

MOVE N'PROD_log' TO N'E:\folder\prod.ldf', 

NOUNLOAD,  REPLACE,  STATS = 10

--Set the database to simple recovery mode.

ALTER DATABASE [prod_Refresh_Reports] SET RECOVERY SIMPLE WITH NO_WAIT

--Send email using database mail on success.

EXEC msdb.dbo.sp_send_dbmail

    @recipients = 'dba@corp.ca',

    @subject = 'prod_Refresh_Reports database restore',   

    @body = @successTEXT,  

    @body_format = 'TEXT'; 

USE prod_Refresh_Reports

--fix the userID mismatch

EXEC sp_change_users_login 'auto_fix','serviceuserid'

END