July 28, 2013 at 7:47 am
Problem. I need to make available a copy of one of the databases in our dev environment everyday to a software tester. The database will need to have the ability to be written to not just read - I was going to go down the road of a daily snapshot but that would be read only as i understand it ?
In effect the software tester needs to be able to do whatever needs to be done in order to test things, be it add tables, delete tables and then just delete the database at the end of the day then start again the next day with another up to date copy from the dev environment.
Can anybody advise of the best method(s) for this ?
July 28, 2013 at 8:17 am
You might want to read this:
http://msdn.microsoft.com/en-us/library/ms188664(v=sql.90).aspx
It shows you how to use the Copy Database Wizard which is available.
July 28, 2013 at 8:47 am
Copy daily backup from production, restore. Will be faster than the copy database wizard and far less impact on production since you'll be leverageing the daily backups already being run.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2013 at 8:48 am
Since this is common task for DBA's I would spend some time to put together a nice solution.
The size of the database is probably the decider for the type of solution you would implement.
If we are not talking VLDB's, I use SSIS to take care of the task for me.
Have a look at an old answer from another thread:
raadee (5/20/2013)
It would contain a couple of tasks and I will try do describe them for you.Not sure if you know anything about SSIS but it might give you an idea on an easy solution.
Scenario:
- Server A dumps full database backups to disk every night.
Tasks included in SSIS package.
1. File Watcher task.
This is a free downloadable task that is not included in BIDS.
This task will watch for your backup file (that you define) in server A backup directory and when the backup of your database is completed, it will continue to the next task. (The value of this task is that it waits for file to complete instead of triggering on file created)
Basically you start this task on server B before the backups start on server A and it will wait for the backup of the database you want on server A to complete. This way you can use existing backups on server A and you do not need to configure server B to run at a specific time to get the backup file, just a schedule package to run hour prior backup on server A and let it run for x amount of hours.
2. File system task.
When backup of your database is done and task 1 detected it, next task is just to copy the file from Server A to server B.
Since this package will be scheduled by SQL agent on Server B, the agent account needs read rights on Server A backup location to be able to copy the file.
3. Execute SQL task.
When task 2 is finished the next step is to restore the database.
You can just do a manual restore of the database and before you press ok just script it out and
use the generated code in this task.
Before the actual restore begins you should add some code where all transactions are rolled back on server B and the database is set to single user mode so that the restore does not fail.
4. Execute SQL task.
You could add an extra task if you need to add new users to your database in Server B if they do not exist in server A.
It might look like a lot of work but once you do this, you can reuse the package the next time you need similar tasks done.
July 28, 2013 at 2:11 pm
Thanks for the suggestions everyone. I think i will go with Gail's suggestion - i have just tested this method using Redgates sql backup pro - it actually gives you an option to restore to new database so i shall use that option and simply call the db Test - this database is then created on the fly and the production backup can get restored there. Great stuff !
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply