remote restore and file deletion
mike c
Posts: 8
Because of network issues and file size I have replicated NAS attached to my prod server and replicated to a remote backup server. What I need to do is take a backup locally, copy it to the NAS drive. I would like to retain 2 days worth of backups locally on the server but only the most current on the NAS drive. From the backup server I need to be able to restore the file from the NAS drive or possibly move it from NAS to the local backup drive and restore from there. I currently have local backups set up but I am doing the rest manually. I need to automate this process. Suggestions?
Comments
How often are you taking these backups?
You can set your SQL Backup job to backup locally, copy the backup file to your network drive, deleting local copies of the backups older than 2 days and backups on your network drive older than 1 hour (This is the current minimum) with a command similar to the following :
Scheduling the restore part of this is a little more tricky, how often are you going to be restoring? Are these going to be Full Backups, Transaction log backups?
Is this just for one database or several?
Redgate Foundry
Redgate Foundry
Sorry I presumed you were using version 5.
There isn't a workaround to set different retention periods for each location but we can create a script that will restore the most recent backup file from the network share when there are many files.
I have the code as part of another script so will take me a short while to cut out the right bits.
I will post it here/pm it to you depending on how big it is.
Obviously if there isn't enough space on your network share to hold a few days worth of files we will need to think of something else.
Redgate Foundry
Please note that it won't stay in this location indefinitely, please e-mail support@red-gate.com if it isn't available for download and quote this forum post.
This script will restore a full backup for all databases on the server it is run against, replacing the older database. You can exclude specific databases at the top.
Please let me know if this helps.
Redgate Foundry
EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [Northwind] FROM DISK =
''\\store\Backup\FULL_SQL2005_Northwind_*.sqb'' WITH RECOVERY, REPLACE"'
on the backup server. It runs fine and return a 0 return code. Unfortunately there are no tables in the database and it seems to run too quickly. There is only ever one backup on the SAN drive but it is almost like SQL Backup can't see the drive. I cannot restore the file graphically using RedGate because it cannot see the drive so that is my assumption for the job. I don't understand the 0 return codes. I just want it to restore whatever file is there.
If you check the SQL Backup log file for the restore, are there any messages pertaining to a successful or erroneous restore?
You should be able to find the logs on the restoring server here by default :
%allusersprofile%\Application Data\Red Gate\SQL Backup\Log\<instance name>
Redgate Foundry
RESTORE DATABASE is terminating abnormally.
2/7/2009 6:00:21 PM: Server: Msg 3101
Exclusive access could not be obtained because the database is in use.
Nothing runs on this box (supposedly). Gives me another avenue to research though. Thanks for yoiur help I will post my findings.
If you make sure USE [master] is at the top of your script that may do the trick.
Redgate Foundry