August 10, 2011 at 3:30 am
Hi, Newbie here
I am trying to setup a maint plan for the following.
Database A (Live)
Database B (Test)
Once a week I would like to take the most recent backup from live and restore it to test. no option for this in maint plans so guess it would be a script required but as I'm new to SQL can anyone here assist with this.
Thanks
DJT
August 10, 2011 at 3:57 am
Hi,
You may want to investigate doing this using SSIS. I do something very similar but restore a copy of our latest DEV base database over my own local copy.
Essentially the package does the following things:
1) Backup the dev database
2) Copy the BAK file to my local machine
3) Restore the DB from the BAK file
4) Delete the BAK file to keep on top of drive space
The best thing i can suggest you do is follow the steps you would do to backup the database using the GUI, then hit the SCRIPT button at the top of the dialogue box. This will give you all the code you need. Then do exactly the same with the restore.
Thanks,
Simon
August 10, 2011 at 5:40 am
Thanks for the reply, Any suggestion to ensure that the DB i'm restoring to i will have exclusive access to, there will be occasions when users have not logged off over the weekend, so will want to disconnect all users form the DB before restoring. ?
Thanks
DJT
August 10, 2011 at 7:08 am
Hi,
Use this:
ALTER DATABASE YourDB SET SINGLE_USER
Then drop it.
Thanks,
Simon
August 10, 2011 at 7:51 am
djt78 (8/10/2011)
Thanks for the reply, Any suggestion to ensure that the DB i'm restoring to i will have exclusive access to, there will be occasions when users have not logged off over the weekend, so will want to disconnect all users form the DB before restoring. ?Thanks
DJT
I personnally use this sequence :
backup to local SHARED drive. Local because it's possible for a network error to corrupt the backup, and shared because I don't want to move them.
Then use a dev or test server to run the restore(s). This validates that the backup can be restored (kind of critical)
Since I restore to a new DB, I don't have to kick anybody out.
Once the restore is done I run checkdb() and log the output. If any errors warn everybody.
Drop the db immediately.
August 12, 2011 at 5:07 am
Thanks for the reply, Maybe i should provide a little extra info,
The LIVE db is for a Financial system and accessable to all as is the TEST. The test system is used to see the outcome of an entry before making the entry in live. so needs to be up to date. thats why i'm trying to automate the process, it's not being user to test the backup of live as may be people have thougth on here.
August 12, 2011 at 5:18 am
Log shipping could be what you need here. But keep in mind that this forces you to be in read-only mode on the test db.
Would that work for you?
August 12, 2011 at 5:28 am
unfortunately not, data needs to be read/write.
I was thinking at the moment along these lines,
Backup Live
Set testDB to single user
take test DB offline
take test db online
set test db to multiuser
restore the backup of live to test?
Would any of the above steps be block in test if a user had not logged off ?
August 12, 2011 at 7:23 am
Did you try the alter db set single_user option? I would also do with rollback immeditate. Drop it then restore the fresh version.
Warn people that on sunday night or whenever the db will be unavailable for x minutes while you refresh.
August 16, 2011 at 12:46 pm
to make sure the single user command works you could run this, which will rollback any transactions that are running on the database.
ALTER DATABASE [YourDbName]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
More details can be found in this blog post: http://blog.sqlauthority.com/2010/02/11/sql-server-alter-database-dbname-set-single_user-with-rollback-immediate/[/url]
Adam Durr
Remote DBA support
www.bluegecko.net
August 21, 2011 at 3:57 pm
create a job on your test server with following steps:-
1) Copy backup file from prod to test server. you may use command like
xp_cmdshell "copy source destination"
where source will be shared path in prod server
2) check if any connections are there to database B on test server and kill if any
select spid from sys processes where dbid='dbid of database B'
Here you may have to write cursor to kill all the spid's from above quay
3) Issue a command to take this database to read_only mode
4) Issue restore command.
Only problem here is that if you take backup by making use of MP in prod server, the backup file name will always be different as it will append date time at the end. So, either create a backup job manually or I think you can make use of BackupDevices
August 21, 2011 at 4:03 pm
Many companies forbid the use of xp_cmdshell.
I have done this using SSIS with a Script task using expressions.
I would share it with you but I do not have access to the code that I wrote to do this
If you want to start writing an SSIS Package I could probably help you.
Some of the other forum members shy away from SSIS and they may be able to provide you with an alternative to SSIS. .
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply