How do I restore / duplicate(mirror) a "livedatabase" to a "testdatabase"
-
Hi there,
Im not a dba or so and very newbee regarding the MSSQL 2005 database env.
But maybe someone out there can help me out.
We have a SQL 2005 server with some production databases and test databases that we use here in the office.
Have a maintenance plan that creates backups (daily) of our databases (fully) and creates ex.
<databasename>_backup_200910060048.bak
Is there now some way to when a job is done and the backup is done (daily) to create a restore from
<database_production>_backup_200910060048.bak to <database_test>
Reason why I ask is we need to have "close to fresh" test-database from the real-production database.
So basicly I want to restore a realdatabase.bak to our testdatabase daily.
Not sure if this is the right way, maybe there is even some better ideas out there? :doze:
Regards,
Fredrik
-
Hi Fredrik,
You can set up log shipping. But you may need your DBAs to get involved. If thats not a feasible option, you can check my article which kind of deals with your scenario http://www.sqlservercentral.com/articles/Log+Shipping/62676/
The code supplied with the article will restore the backup files but will not recover the database so that further backups can be applied. So you will see a "Restoring..." message on the database. Someone in a separate forum thread on this site used the code and created an additional job step in to recover the database at the end of the process. Take a look at the article and the supplied code. You may get some ideas from it.
Girish
-
Hi Fredrik.
If you want to keep your test environment in step with live, there are a few ways you can do this.
Yes, you can automate a restore of a backup. It's not too difficult when you know how. I'm assuming you only need your test system to be as live at a given point in time.
As suggested, you can use log shipping to "top up" your test database and make it available using RESTORE LOG <log file> WITH STANDY=<standby log path>. This will give you a read only database which can be switched back to read logs again when you next need to update.
Another method is timed replication but this has problems as you may need to drop your dependencies and such as this can cause replication to fail.
Both of the above can be scheduled to run once at 7 PM say and continuously between 00:01 and 06:00. This will give you a test database that is identical to live at 6 AM.
If you need an exact copy of live and where data changes meed to be made, the restore from backup may be the best way to go.
Hope this helps.
-
Oppps just notice I have 2 accounts here at the moment.
1 private and one work.
Anyways,
Thxn guys.. seems like at least there is some options for it.
So will just check it up and from what I seen maybe I might go for a auto restore an hour or so after the "nightly" backup is done.
For now I have been doing manual restore from the nights backup of the production database to the test database in the mornings so they have a more or less identical database.
Replica seems to be a bit of a huzzle for this kind of "small" project.
So thnx again 🙂
Very nice forum this is btw..
-
If you only need to copy the data once a day. You can use SSIS to create a package that restore the database once a day.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply