I am working on a SQL Server on which I have three databases:
- User: This is the one where real data resides
- Test
- 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
-
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?)
-
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.
Best Answer
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.