remote restore and file deletion

mike cmike c Posts: 8
edited February 11, 2009 4:19PM in SQL Backup Previous Versions
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?


  • Hi Mike,

    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 :
    EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [AdventureWorks] TO DISK = ''C:\Backup\<AUTO>.sqb'' WITH COMPRESSION = 1, COPYTO = ''\\Storage\Backups'', ERASEFILES = 2, ERASEFILES_REMOTE = 1h, FILEOPTIONS = 1"'
    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?
    Matthew Flatt
    Redgate Foundry
  • The backups are once a day. I need to restore once a day. Customer only needs to be able to restore to last nights backup. if I set the backup job to delete any file on the NAS drive older than say 12 hours than there should only ever be one file for the backup server to see on the san. That should make it easier. Maybe some kind of script to read in the .sqb filename from the drive and place it a s a variable in the restore script?
  • If there is only ever 1 backup file on the NAS you could run something like this :
    EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [Northwind]  FROM DISK = ''\\store\Backup\FULL_SQL2005_Northwind_*.sqb'' WITH RECOVERY, REPLACE"'
    Matthew Flatt
    Redgate Foundry
  • Tried to apply the code and kept getting syntax error. Couldn't figure it out for the longest time. Then I hit the answer. We are running SQL Backup 4.6...ERASEFILES_REMOTE isn't valid yet. Is there a workaround?
  • Hi Mike,

    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.
    Matthew Flatt
    Redgate Foundry
  • The script can be downloaded from here.

    Please note that it won't stay in this location indefinitely, please e-mail [email protected] 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.
    Matthew Flatt
    Redgate Foundry
  • I ran the script
    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.
  • Hi Mike,

    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>
    Matthew Flatt
    Redgate Foundry
  • 2/7/2009 6:00:21 PM: Server: Msg 3013
    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.
  • Same error as before. Exclusive access could not be obtained. There are no locks on this database. there are no processes using this database. There are no tables in this database. I cannot understand what the error message is refering to. What do the Msg numbers signify? Any suggestions?
  • Are you in the context of the database when running the restore?

    If you make sure USE [master] is at the top of your script that may do the trick.
    Matthew Flatt
    Redgate Foundry
  • That resolved it.....Thank you
Sign In or Register to comment.