2

I am working on a SQL Server on which I have three databases:

  1. User: This is the one where real data resides
  2. Test
  3. Development: This is used for testing and developement.

From time to time I want to copy the content of the user database into the test and / or development database to make sure that the developement and test environments are as close as possible to the user environment.

I thought I could use the "Copy Database Wizard" for this, but it fails with an error

"Object reference not set to an instance of an object

after I have chosen which hosts to copy from and to (localhost in both cases). I have not been able to find any solutions to this error.

Question

  1. Does anybody have any ideas about what causes this and how to correct the problem (maybe I need to install something or do something with the setup of the server?)

  2. What would be the selected way of doing the transfer if I cannot use the Copy Database Wizard? - This is to be done, I would guess, about once a month.

If it matters, I have full admin access on the database server but not on the underlying windows server.

Improve this question
2
  • 4
    If you want a complete copy of the User database, I suggest backup/restore, perhaps leveraging your regular User database backups for this purpose. You may need a post restore script to setup the security for the target environment (e.g. create test/dev users or remap existing users to SQL logins in the target environment).
    – Dan Guzman
    Commented Apr 15, 2015 at 10:52
  • 1
    @DanGuzman Yes, use BACKUP and RESTORE WITH REPLACE option.
    – Łukasz Kastelik
    Commented Apr 15, 2015 at 11:37

1 Answer 1

Reset to default
3

Are you just looking for a complete copy of the data to completely replace what already lives on the destination instance? In that case, a full backup of the source database and a restore into the destination instance would be the cleanest and easiest route.

If you are looking at select data copying (i.e. you don't want all data, or you don't want to replace the destination data) then you'll need to have a logic layer between the two data sources. Some ideas behind this would be SSIS, PowerShell, or any other tool to do some brain work between both SQL Server databases. The tool to use would depend on the specifics of your need as well as your comfort level with all the options.

Improve this answer

Your Answer

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

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