Sql-server – SQL Server 2008R2 Database refresh in isolated environment

sql serversql-server-2008sql-server-2008-r2

I would greatly appreciate your help on my database "refresh" issue.

The scenario:

I have 12 databases on a QA server that were restored there from a Production server approx. 2 years ago.
Now QAs need to sync those 12 databases with databases on a Prod server; all account logins (and corresponding permissions) have to remain unchanged in QA environment – QAs only need to "refresh" databases so that data is as current as it is in Prod environment.

For security reasons there's no (and cannot be) connection between two servers (hence it is not possible to import data with append option), so I had to request DBAs on that Prod server to backup databases and to place backup files in a shared folder (already there).

My question is – what is the best way to "refresh" 12 databases in QA environment – is it to delete/drop old databases and restore them from backup files (then what would happen to the current QA server logins?) or is it to try to restore databases from backups without dropping 12 old databases and is this possible, would be data just appended to existing data and current logins stay unchanged ?

Thank you in advance for any input.

Best Answer

Just restore them over top.

RESTORE DATABASE db1 FROM DISK = '\\share\folder\file.bak'
  WITH REPLACE, RECOVERY;

You may have to use the MOVE option if the folder paths for data/log files don't match, e.g.

RESTORE DATABASE db1 FROM DISK = '\\share\folder\file.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'data_file_name' TO 'D:\SQLData\file.mdf',
  MOVE 'log_file_name'  TO 'E:\SQLLogs\file.ldf';

You can get the file names (and anticipate their sizes, too) from:

RESTORE FILELISTONLY FROM DISK = '\\share\folder\file.bak';
Related Question