February 5, 2009 at 9:36 am
Hi everyone,
I have server A and sever B, we need to move the nightly backup over every morning and do a restore in this development server. I have already created and succeeded in schedule for backing up the database from server A to server B.
However, we need Server B do an automatic restore from the backups every day. Is ther any scheduled task that I can do in maintenance plan and do the restore on there? If not, is there an scripts that can create this automatic restore? even if there is a script and i cna put that in a operation sytem job?
Thaks for any infromation
February 5, 2009 at 9:42 am
February 5, 2009 at 10:36 am
Carolyn,
Thanks for the information, is log shipping under a feature in maintenance plans in SQL 2005? Cna you give me a little bit of more detail please? It is not the same as export data, is it?
February 5, 2009 at 11:27 am
its not in the maintenance plan..
however you can use it using management studio..
Are you taking full backup at night or taking tx backup also
February 5, 2009 at 11:59 am
Full bakup
February 5, 2009 at 12:04 pm
I had a quick look around for articles on setting up log shipping, I have documentation for how I set it up but I need a warm standby server so I am restoring logs on a secondary server every 15 minutes, so my notes might not be what you want. I have found the following link http://www.informit.com/articles/article.aspx?p=331402 which seems to be a useful starting place for more information.
February 5, 2009 at 12:09 pm
top level view
Backup the database with initalize so you have only one bak file all the time
use xp_cmdshell or CLR to copy the file to the destination
Restore with move (force)
for syntax i would need some time...
do i understand your scenario properly
February 5, 2009 at 12:36 pm
There are a whole bunch of ways that you could do this, obviously everything mentioned above will work just fine.
If you want to create a custom SSIS package to copy the backup (that is already being taken anyway) and then restore it, it would be very simple to do since you would just need a filesystem task to copy the job and a Execute SQL task to restore the DB. Schedule that package to run through the SQL Scheduler.
You could also just create a job that has a single Scripting step to copy the file followed by a T-SQL step to restore the database.
What are you the most comfortable with? That is really the question here.
February 5, 2009 at 1:38 pm
The following process was developed in 2000 environment, but should work in 2005 if xp_cmdshell is enabled.
Create a stored procedure on server A that dynamically creates the T-SQL necessary to do a database restore relative to server B. The stored procedure should use an output parameter that contains the dynamically created T-SQL.
Look up syntax for RESTORE DATABASE and MOVE files. This will help define what is needed for the dynamic SQL. Some of the required parameters may be found by the following:
1. The latest backup file may be found by querying backupmediafamily and backupset tables (Joined) in msdb.
2. Query sysfiles on server A for the logical data and log file names.
I would set the restored database name (B) and data/log paths as parameters in the stored procedure. Depending on the restored database name, you may be able to dynamically create the data/log file names.
On server A, set up a job that executes xp_cmdshell. The command should call the osql utility. In turn, the osql utility should call the above stored procedure and output its results to a file. Make sure server B has access to the file location.
On server B, set up a job to do the database restore by executing xp_cmdshell, which calls osql utility to execute the generated restore script: exec master..xp_cmdshell 'osql -E -n -i "file name with full path"'
Server B will also need access to server A backup file. If not, the file will have to be copied to a location where it does have access.
I know it seems a bit ugly and outdated given the newer technology in 2005, but I’ve been running this for a long time without any problems.
Have fun!
February 6, 2009 at 9:54 am
All,
Thank you so much for help, I feel a if I have found a jewel in my lost coat pocket by joining here. I thought keeping it simple would be more reliable, however i don't want to do something idiotic at the same time as well. I have 3 scripts, the frist one I have written (the most basic one) which I guess would be the idiotic one. The second one is the command shell one, I have the most basic experience with cmd so I appreciate any help. the third one is from an article that i can modify.
RESTORE DATABASE [Dev_DB] FROM DISK = N'C:\devDB.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
February 6, 2009 at 12:51 pm
You may have to play with the restore options a little to fit your exact requirements, but if it is a fixed file name and restoring over an existing database you probably can use the following:
RESTORE DATABASE xxx FROM DISK = '\\server\path\filename' WITH REPLACE
If there are likely to be connections open against the original database then you may have to add the following immediately before restoring:
ALTER DATABASE xxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE
February 6, 2009 at 1:32 pm
To go with the getting rid of all user you may have to run a mass kill program to get rid of the connections. I have not used to single user enough to be confident it will remove all users.
If time is an issue you can drop the data base and restore as it is somewhat faster. I you don't this don't forget a script to set the logins to match the new server either by matching DBID's or using the sp_change_users_login or users will not be able to connect to the database.
This is where the copy database task from SSIS may be more useful as it matches the logins for you.
February 8, 2009 at 3:15 am
i wouldnt personally enable\use xp_cmdshell
schedule a windows task to run a batch file. The batch simply copies the file from one location to the other (maybe a mapped drive to server B???).
Incidentally, Redgate backup has an option to copy the backup file to a network location. Not sure if any other backup software packages offer this functionality, but its way better than enabling xp_cmdshell
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply