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
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
.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
.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.
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)