refresh development databases
-
Hello,
The way we used to do dev refresh in SQL 2000 stopped working once we migrated to 2005. I am wondering if someone could help me out here.
We have production db servers hosted by another company. We have a standby db server hosted locally. Transaction logs come from production every half an hour and get applied to the standby dbs at night. In the past, whe we do dev refresh, we just shut down the standby sever and copied database files over to dev and turn the databases on from the data and log files, then crunched data from there, finally brought the dbs online for dev. But in sql 2005, I could no longer bring the database online because it complains on the database being in standby mode and cannot be restored. I could bring full backups from production and restore them, but the problem is the network line between hosting and us local is very tiny, it will take days to transfer data over even after being zipped, plus its a lot of manual operations.
I wonder if there is a way to get around in sql 2005, maybe there are some new features out there?
Thanks for your input.
Kathleen
-
I have not experimented with your exact scenerio but since you can copy the database file and logs from the Standby machine to the Development machine I would assume your data pipe between the two is fine. Since you are used to shutting down the Standby and Development machines while copying the file I would think the following should work without additional disruption:
Detach the database from the standby server (sp_detach_db), copy the file to development server. Attach the database back to the stand by server (sp_attach_db), then run sp_attach_db on the development server and attach the copy. This is much faster than backup, copy and restore but I don't know if you will run into problems with the log shipping replication setting on the development server. I would think it would attach just fine and you could simply have a script that disables the log shipping activity on the development copy.
HTH,
James.
-
where i work we just restore from tape to dev or QA every month or whenever they request it
-
Thanks for the response. Sorry I was not quite clear earlier. The standby server is actually local. The pipe can handle log transfers fine. It was a long time ago that we transferred full backups from production to initialize the standby databases and then we keep applying the transaction logs since then. The standby server is used for reporting purpose.
In sql 2000, I can just attach the data files and transaction log files fine without taking the standby databases out of restoring mode, but in sql 2005, it complains on the databases being in restore mode and cannot bring them online. It seems like the only choice is to bring the standby databases into recovery first before copying the files, but we cannot do that because that will require new full backups from production to initialize them.
-
Restoring from tape could work, but then its like additional service we have to request from the hosting company.
Thanks for helping.
-
Have you tried Backup and Restore operations. After restoring to the DEV machine you could run sp_removedbreplication. This is a lot slower than Detach and Attach but might work.
I'm an curious about one thing, the error you get, does it occur when detaching from the standby server or when attaching to the dev server? If it's on the dev server, does the database actually attach?
James.
-
Do you mean backing up the database on standby server? I dont think you can back up a standby database.
The error I got is from attaching the database using the data and log files from the standby databases. The error is something like: cannot attach the database while its in restore. But there was no problem with sql server 2000.
-
Your probably right, it would be like trying to backup a database this is in the process of being restored. Doesn't make much sense.
While I can't duplicate your situation easily I was able to start a restore on a database (used norecovery option) thus leaving it in "Recovery" mode, then detach it and reattached it to another server without problems (just got a transaction rolled back message). So I have to assume you are not really detaching the database from the standby server (as that would probably destroy it's standby status) but just copying off the files after the server is shutdown.
While I have not tried this, have you considered "Creating" a database of the same name, with files located in the exact same locations as on the Standby server, on the Dev Server (everthing would have to match exactly). Then copy the files off the Standby over the Dev files. Start DEV and see if it will bring the database up in "Standby" mode. You could then simply run the recovery procedures on DEV to bring the database online. (I've never used a standby database)
I doubt that will work since some of the replication process information is stored outside the actuall database. But its possible it will simply recover the database automatically.
-
I just tried to first create a database in dev with the exact same name and location as the standby database, then shut down the sql sever service, copied the data files and log files over from standby database to replace the original files, restarted sql server service, I got 'database cannot be opened due to inaccessible files' error. So I think there's more state info about the database besides the database name, location, etc stored somewhere like the system databases in order to restore the database.
Thanks for trying to help me out.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply