Sql-server – Scripting the copy of SQL Server production database to test environment

copysql serversql server 2014

We are introducing continuous integration on our project and we decided to automate the refresh of our test database instance with fresh production data.

I'm currently looking for the best way to go in order to avoid any production disruptions or slow downs.

I first though about using backup/restore command from a command line with the sqlcmd utility…

But it seems it's problematic because it solely generates a .bak file although this file is still "linked" with .mdf/.ldf files. So when restoring it in our test environment these .mdf/.ldf files are not found.

Then I went on specifying a 'WITH MOVE' options in the restore command in order to specify the location of the .mdf/.ldf file over a network share but I think the production server locks these file and prevent any read on them from the test server.

So basically, do you know a straightforward (and efficient) way to dump a database to a single file to easily load it in our test environment ?
Of course without disrupting the production environment.

Edit:

Both production and test SQL Servers version are 12.0.2000.

But I'm first testing loading from production to a local development SQL Server Express (also v12.0.2000)

Best Answer

But it seems it's problematic because it solely generates a .bak file although this file is still "linked" with .mdf/.ldf files. So when restoring it in our test environment these .mdf/.ldf files are not found. Then I went on specifying a 'WITH MOVE' options in the restore command in order to specify the location of the .mdf/.ldf file over a network share but I think the production server locks these file and prevent any read on them from the test server.

This whole statement does not really make sense. A backup file is not "linked" to the mdf/ldf files, it contains the files of that database in order to recreate them when you restore it. When you go to restore them on another server you add WITH MOVE and specify the location you want the mdf/ldf files to be stored at on that server. They do not link back to the original location where the backup was taken from, at all.

Using backups is the most common method for refreshing a development environment. You would generally base current you can keep the data in development (if needed) on how your backups are done in production (e.g. full/diff/log or just full/diff).

Related Question