Automating DB Refresh using SQL jobs. An alternative to Log Shipping.

We were asked recently to refresh development  DB from production DB on a weekly basis. I thought of automating it using jobs. I tried and its working successfully.

Following are the details of this process.

  • I created a folder on our backup share which will be used for this process..

On Prod Server I have Jobs to backup the database to our share:

  • Step 1 – Enable  Cmdshell

exec sp_configure ‘xp_cmdshell’, 1
go
reconfigure
go

  • Step 2 – Delete all files from share so that the folder is clear of any old files.

EXEC  xp_cmdshell  ‘Del \\SharePath\*.BAK’

EXEC  xp_cmdshell  ‘Del \\SharePath\*.SQL’

  • Step 3  –  Disable Cmdshell

exec  sp_configure  ‘xp_cmdshell’, 0
go
reconfigure
go

  • Step 4 – Backup database to share folder.

This is all we are doing on production server.

On Dev Server , I created a job to Restore the database from the backup of Prod we saved on share.

  • Step 1 – Save Dev Logins before DB overwrite\restore.
  • Step 2 – Save Dev users and permissions before we start restore.

(Refer earlier blog to save Logins, users and permissions to files.)

  • Step 3 –  Restore Prod Backup to Dev Database.
  • Step 4 – Drop all users of the restored database which came along with DB backup. This way all existing prod permissions will be wiped out.

(Refer earlier blog to save Logins, users and permissions to files.)

  • Step 5 – Create Dev Logins which we backed up before starting Dev restore.

To do this, the job step Type should be “Operating System” (CmdExec)

sqlcmd -d DB_Name -i \\SharePath\CreateLogins.sql

  • Step 6 –  Create Dev Users and give permissions as it was before DB restore. We backed this up before starting Dev restore we will use that backup.

To do this, the job step Type should be “Operating System” (CmdExec)

sqlcmd -d DB_Name -i \\SharePath\Createusers.sql

 

You can also add another step to fix orphaned logins if any.

To know more about running SQL statement from File or To Save output of any statement to file, please refer:   https://msdn.microsoft.com/en-us/library/ms170572.aspx

About: Niraj Kumar


Leave a Reply

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