Team, Can you please help me to automate refresh the database from Prod to QA .Usually we will do it on demand basis from Dev team manually. Now I have been asked to do it daily. Can we do this using SQL Job? Any help with scripts is greatly appreciated Thank you
Yes, you can do this in a SQL Agent job. Below is just an example to generate a RESTORE statement for the least full backup of a database on a given server. if you utilized LinkedServers you could use a 4 part naming to determine this and just execute the RESTORE statement on the QA server. DECLARE @sql NVARCHAR(4000) SELECT TOP 1 @sql = 'RESTORE DATABASE ' + b.database_name + ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH RECOVERY, REPLACE, STATS=5' FROM msdb.dbo.backupset b INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id WHERE b.type = 'D' AND b.is_copy_only = 0 AND b.database_name = 'DatabaseNameGoesHere' ORDER BY b.backup_start_date DESC SELECT @sql --EXEC (@sql) Note: This assumes that the database physical locations are the same on both servers. If they aren't you'd have to modify the restore command to include a MOVE statement. This isn't the entire solution but hopefully will get you started. There also might be better examples on the interwebs somewhere. Powershell also might be a great solution although I've never tried it. Hope that helps!