Database refresh from Production to Dev

Question

Database refresh from Production to Dev

Chaitanya Kiran 841

We want to restore some databases from Production server to Dev server. We want to create a job that automatically restores a fresh copy of these databases to Dev server on 10th of each month. Could you please advise on how to do this.

0 comments

3 answers

Your answer

Answer 1

It is almost impossible to give an answer to that question, because there is so much It depends in that question. For instance, is there a daily full backup from production that you can use?

At my client we restore a copy of production to two different test environments nightly. In our case, we don't rely on the backups taken by the IT department, because their backup schedule does not meet our needs. So we have a backup job on the production server at 3:40 in the morning. (For us the time of day is important, but it may not be equally important if you are only going to this monthly.) This backup job writes the backup to a file share on the dev/test server.

We used to have Agent jobs to restore the database, but we have changed this to DevOps pipelines, because for one of the environments we also want to deploy the most recent bits from our main branch. For your case, I will assume that DevOps pipelines is over the top, but that you can simply schedule a job that runs the restore. Exactly how that script would look like depends on your needs. What we do in our SQL script is that we restore the database as OurDBNew and once the restore has completed, we rename the existing database OurDB to OurDBOld and then we rename OurDBNew to OurDB. This is to avoid that the database is inaccessible for longer time. What we also do is to copy permissions and users from OurDBOld to the new OurDB. This can be practical if you have assigned different permissions in the Dev environment and want to keep them.

As for setting up the schedule, that should not be difficult. We do this only daily basis, but the UI in SSMS makes it easy to set up jobs that runs only once a month.

Erland Sommarskog 137.3K Reputation points MVP Volunteer Moderator

2026-08-07T09:20:04.4733333+00:00

So we have a backup job on the production server at 3:40 in the morning.

I should add that this backup runs with COPY_ONLY. This is important to include this option when you run out-of-band backups in case the regular backup scheme includes differential backups. (COPY_ONLY prevents clearing of the differential map.)

Answer 2

Deepesh Dhake 1,165

I'd suggest putting a SQL Agent job on the Dev server (rather than Prod) that pulls the latest Prod backups from a shared network folder and restores them. That keeps the load off Prod and avoids granting Prod any rights on Dev. You'll want to make sure the Dev SQL Agent service account has read access to that share.

For each database, the job would grab the newest full backup by its actual file timestamp rather than by filename, so you're never guessing which copy is freshest.

For each database in your list, the job kicks off active sessions (single-user with rollback), restores over the existing Dev copy with replace, relocates the data and log files to Dev's paths automatically, then returns it to multi-user.

Fixing orphaned users so Dev logins map correctly, and setting the recovery model to Simple on Dev so you're not accumulating log backups you don't need there.

A single monthly schedule set to day 10, running in a quiet window such as 1 AM, attached to the job and pointed at the Dev instance.

Chaitanya Kiran 841 Reputation points

2026-08-07T06:49:16.39+00:00

How to find out the latest full backup and how to restore? Could you please provide T-SQL script
Erland Sommarskog 137.3K Reputation points MVP Volunteer Moderator

2026-08-07T09:17:55.9966667+00:00
Here is a query that gives you information about the most recent full backups for a database:

SELECT bs.database_name, bs.name AS backup_name, bs.user_name AS backup_user, bs.backup_start_date, bs.backup_finish_date, CASE bs.type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' WHEN 'F' THEN 'Filegroup (full)' WHEN 'G' THEN 'Filegroup (diff)' WHEN 'P' THEN 'Partial (full)' WHEN 'Q' THEN 'Partial (diff)' END AS type, bmf.physical_device_name FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id WHERE bs.database_name = 'YourDB' AND bs.type = 'D' ORDER BY bs.backup_start_date

The tricky part is that the backup files may not be accessible from the dev server. Some backups may also be snapshot backups taking by a third-party tool and not directly usable. This is something you will need to figure out on your own.

As for the RESTORE, you run

RESTORE DB FROM DISK = '<backupfile'> WITH MOVE '<datafile>' TO '<newpath.mdf>', MOVE '<logfile>' TO '<newpath.ldf>'

I have attached an abbreviated version of the RESTORE script we use. It is still fairly elaborate, though. It not only restores the database, but it also copies users and permissions from the old database. The script also goes at length to make sure that we are actually able to set the databases in single-user mode. Alas, it still happens occasionally that the restore job fails because of conflict with other users.

You will have to review this script and adapt to your needs. Note that in our script the name of the backup file is hard-coded, since we take an extra backup for our restore.

refreshfromprod.sql.txt

Deepesh Dhake 1,165

@Erland Sommarskog query gives you all the backups. If you want the latest full backup, latest differential backup and all the log backups after the full backups then you can also look into following query:

DECLARE @DatabaseName SYSNAME = N'DBName';
-- Latest full backup
DECLARE @LastFull DATETIME;
SELECT TOP (1) @LastFull = backup_finish_date
FROM msdb.dbo.backupset
WHERE database_name = @DatabaseName
  AND type = 'D'
ORDER BY backup_finish_date DESC;
-- Latest full
SELECT TOP (1)
    bs.database_name,
    'FULL' AS backup_type,
    bs.backup_start_date,
    bs.backup_finish_date,
    bmf.physical_device_name,
    bs.first_lsn,
    bs.last_lsn,
    CAST(bs.backup_size / 1048576.0 AS DECIMAL(18,2)) AS size_mb
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = @DatabaseName
  AND bs.type = 'D'
ORDER BY bs.backup_finish_date DESC
--UNION ALL
-- Latest diff after that full
SELECT TOP (1)
    bs.database_name,
    'DIFF',
    bs.backup_start_date,
    bs.backup_finish_date,
    bmf.physical_device_name,
    bs.first_lsn,
    bs.last_lsn,
    CAST(bs.backup_size / 1048576.0 AS DECIMAL(18,2))
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = @DatabaseName
  AND bs.type = 'I'
  AND bs.backup_finish_date >= @LastFull
ORDER BY bs.backup_finish_date DESC
-- All log backups after the full
SELECT
    bs.database_name,
    'LOG',
    bs.backup_start_date,
    bs.backup_finish_date,
    bmf.physical_device_name,
    bs.first_lsn,
    bs.last_lsn,
    CAST(bs.backup_size / 1048576.0 AS DECIMAL(18,2))
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = @DatabaseName
  AND bs.type = 'L'
  AND bs.backup_finish_date >= @LastFull
ORDER BY backup_finish_date;

Answer 3

Another option is to use dbatools and PowerShell. You can use the code below. The question is whether or not you have the same file structure (Disk Drive letter, paths) as the Production one. If you do then you don't have to worry about other parameters. If they are not the same, you can see below the command other switches. Let me know if you have other questions.

https://dbatools.io/Get-DbaDbBackupHistory

https://dbatools.io/Restore-DbaDatabase

Get-DbaDbBackupHistory -sqlinstance prodinstance -Database (comma separated list of databases by name) -LastFull |
   Restore-DbaDatabase -Sqlinstance devinstance -TrustDbBackupHistory -WithReplace 

# If you have different drives and paths, then you can use 
# -DestinationDataDirectory D:\newpath -DestinationLogDirectory F:\newpath