6

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)

Improve this question
2
  • What version of SQL Server?
    – user507
    Jun 10, 2015 at 13:04
  • @ShawnMelton : Yes, forgot to mention it (and just edited my post), it's both v12.0.2000.
    – Koresh
    Jun 10, 2015 at 13:11

2 Answers 2

Reset to default
5

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

Improve this answer
1
  • Thanks for the answer, you just fixed my understanding of the WITH MOVE option !
    – Koresh
    Jun 10, 2015 at 13:52
4

do you know a straightforward (and efficient) way to dump a database to a single file to easily load it in our test environment ?

Backup on PROD server and RESTORE it on Test server. Most efficient and reliable.

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.

Not true. SQL server does not lock BACKUP File. You have to use WITH MOVE and REPLACE on the test server since the database is already on test server and you are overwriting it with a fresh copy of PROD backup.

You can automate the whole process with SQLCMD / PowerShell and use SQL Agent job for regular scheduling to refresh your TEST server.

local development sqlexpress

SQL Server Express edition has various limitations.

Improve this answer
1
  • Thanks, I will use the REPLACE option to overwrite the test database.
    – Koresh
    Jun 10, 2015 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.