Manage backup biles on network share

OsolageOsolage Posts: 15
edited January 22, 2009 9:23AM in SQL Backup Previous Versions
I haven't had time to determine whether there's an effective way to use the copy to "Copy backup to network" feature. It seems that when I do this, the original backup file remains on the local disk. Is there a way to force SQL Backup to delete the files on the local disk after the backup is complete? Additionally, is there a way to update the backup catalog with the location of the corresponding file on the network share? (if this is not handled in SQL Backup 5 but is handled in SQL Backup 6, please let me know.)

Thanks,
Ra Osolage
Thanks,
Ra Osolage
«1

Comments

  • I am sure RedGate staff will get to you with a better answer

    But there is a command argument to control how many files/days(?) to keep network backup files

    and next version of SQL Backup GUI should have better "network" backup management as far as I recall reading on this forum
    Jerry Hung
    DBA, MCITP
  • peteypetey Posts: 2,358 New member
    Is there a way to force SQL Backup to delete the files on the local disk after the backup is complete?
    If you mean if SQL Backup can delete the local copy after the backup file is copied to the remote share, no, there is no such option. The only deletion option is to delete old backup files in the same folder everytime a backup runs, as mentioned by Jerry.
    is there a way to update the backup catalog with the location of the corresponding file on the network share?
    No, not in the msdb tables. SQL Backup does store details of the backup files in a SQL Server Compact database, located in the "C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Data\<instance name>" folder. In the backupfiles table, there is a 'file_type' column. A value of 'P' indicates a primary backup file. A value of 'C' indicates a copied file.

    You can use the 'sqbdata' extended stored procedure as a proxy to query this database e.g.
    EXEC master..sqbdata 'SELECT * FROM backupfiles'
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I would like to have the ability to make a backup locally, then move it to a network share and then have the local copy deleted. What's the use of moving the backup to the network share if you're keeping a local copy still?

    For instance, My SQL server has about 750GB hard drive and about 400GB of SQL data. My full backups compressed to about 70GB. I would like to move that 70GB of data to another network share and bring it back or restore from the network location when needed...

    That options isn't available now? If not, is that a planned feature? This has to be a popular way of doing backups I would think.
  • peteypetey Posts: 2,358 New member
    The main issue I have with such a feature is that I've seen one too many files that were 'successfully' copied over to a network share, only to turn out to be corrupted/incomplete. You should only delete your original backup file if you are able to successfully restore your remote copy, or at least verify it using the checksum feature in SQL 2005/2008.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Sure I can see that, but shouldn't you leave that to the user? If the user wants to be cavalier and do backups like this, then have a check box to delete from original location after copy. If the file is corrupted, then a lesson is learned by the person using the software.

    Shouldn't this be a user selection?
  • peteypetey Posts: 2,358 New member
    Unfortunately, without a good backup to compare to, it's all too easy to lay the blame on SQL Backup when the copied file is later found to be corrupted.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Then add a line that reads: "WARNING: Deleting original backup file can expentially increase the chances of a corrupt backup. By checking this box, you indicate that you are willing to take that chance."

    or something...

    Otherwise, people are forced to have to manually delete backups locally which can get irritating to have to remember that it has to be done.
  • peteypetey Posts: 2,358 New member
    Otherwise, people are forced to have to manually delete backups locally which can get irritating to have to remember that it has to be done.
    SQL Backup only prevents you from deleting the new backup file. You can always delete the older backups using the ERASEFILES/ERASEFILES_ATSTART option. E.g.
    EXEC master..sqlbackup '-sql "BACKUP DATABASE AdventureWorks TO DISK = &#91;g:\backups\&lt;AUTO&gt;&#93; WITH ERASEFILES = 2h" '
    

    will delete all full database backups of the AdventureWorks database in the g:\backups\ folder older created more than 2 hours ago.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Along this same line of thought. is there a way to have the copied file be deleted in accordance with the regular delete policy?

    For example, all files older than 3 days are deleted on the local and the copied location?
  • Hi Illuminations2k,

    You can achieve this by having WITH ERASEFILES = x, FILEOPTIONS = 1 in your job which runs. This will then erase both local and remote files according to the same retention period.

    Replace x with the number of days you wish to keep the files for. If it is only hours use xh.

    I hope this helps.
    Matthew Flatt
    Redgate Foundry
  • Matthew,

    Thanks for the quick reply. That worked perfectly!

    -Adrian
  • EXEC master..sqlbackup '-sql "BACKUP DATABASE AdventureWorks TO DISK = [g:\backups\<AUTO>] WITH ERASEFILES = 2h" '

    Sorry for not understanding, but where do I execute this at? Do I do this in SQL Server?

    Where do I run this at?
  • Your command will delete the primary backup file and this can be done through the SQL Backup GUI on either the Backup or Scheduled Backup wizard. The option is :

    Delete existing backup files in this folder for the selected databases.

    If you want to also delete files in a secondary location (Copy backup to network) you need to add FILEOPTIONS = 1 to your command if running it manually or edit the SQL Server Agent job in SSMS/EM to include FILEOPTIONS = 1 in the job step.

    Running manually would become :
    EXEC master..sqlbackup '-sql "BACKUP DATABASE AdventureWorks TO DISK = &#91;g:\backups\&lt;AUTO&gt;&#93; WITH ERASEFILES = 2h, FILEOPTIONS = 1" '
    

    I hope this helps.
    Matthew Flatt
    Redgate Foundry
  • edit the SQL Server Agent job in SSMS/EM to include FILEOPTIONS = 1 in the job step

    When I get to the last screen on the edit job (Step 5 of 5) and I can see the script when I click the script tab, but I can't edit it.

    Where do I actually edit the script that is generated?

    My script has the following (I put X's in where the stuff you shouldn't care about):
    DISK = ''XXXXX\&lt;AUTO&gt;.sqb'' WITH COMPRESSION = 2, COPYTO = ''XXXX'', ERASEFILES_ATSTART = 3, INIT, MAILTO = ''XXX'', THREADCOUNT = 4, VERIFY"'
    

    I don't have the FILEOPTIONS in my script...
  • If it is a scheduled job, you will need to open SSMS/EM, expand SQL Server Agent>Jobs and right click on the job you wish to edit, choosing properties.

    On this screen click on Steps, make sure the step is highlighted and choose Edit.

    You should now see the backup command that the job will run, here you can edit it.

    If you are just using the Backup Wizard, you need to copy the the script from the script tab, paste it into SSMS/QA and then edit it to include FILEOPTIONS = 1 before executing it.
    Matthew Flatt
    Redgate Foundry
  • cool!

    So just so I am totally clear, when I add the "FILEOPTIONS = 1" to the script, it will then delete the local and network copy of the backup. Is that correct?
  • Yes, it will delete any files in both folders which are older than the hours/days specified and are of the same type/database/instance/server as the one being backed up.
    Matthew Flatt
    Redgate Foundry
  • Sorry to bring this back up, but our SQL server just slowed to a huge crawl late Friday afternoon. After investigating, I noticed that I had about 7 days worth of backups on the local and network share. The FILEOPTIONS = 1 did not do anything.

    Now when I try to edit the backup job in SQL Backup, I get "The GUI does not support editing the command you selected"

    I was able to edit it before I changed the script in SSMS/EM. Now I am unable to do so.

    Any ideas? I really don't want to have to manually delete backups that are older than my specified timeframe because I will forget to do it and I will get issues like I did Friday.

    Here is my script:
    DECLARE @exitcode int
    DECLARE @sqlerrorcode int
    EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASES &#91;LIST OF DBS&#93; TO DISK = ''D:\MSSQL10.MSSQLSERVER\MSSQL\Backup\&lt;TYPE&gt;\&lt;DATETIME mm_dd_yyyy&gt;\&lt;AUTO&gt;.sqb'' WITH COMPRESSION = 2, COPYTO = ''\\homebackup\FULL'', ERASEFILES_ATSTART = 3, FILEOPTIONS = 1, INIT, MAILTO = ''EMAIL ADDRESS'', THREADCOUNT = 4, VERIFY"', @exitcode OUT, @sqlerrorcode OUT
    IF &#40;@exitcode &gt;= 500&#41; OR &#40;@sqlerrorcode &lt;&gt; 0&#41;
    BEGIN
    RAISERROR &#40;'SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode&#41;
    END
    
  • Hi,

    Unfortunately after editing your script to include these parameters you can no longer edit it in the SQL Backup GUI.

    What version of the SQL Backup Server Components are you running?

    Could you please use Help>Check for updates in the SQL Backup GUI to upgrade to 5.4 and then upgrade the Server Components?

    Thanks,
    Matthew Flatt
    Redgate Foundry
  • I have done that. I have version:
    SQL Backup version: 5.4.0.55
    Service Application Version: 5.4.0.55

    I had manually upgrade to version 5.4 and then when it started it told me my server components needed to be upgraded so I did.
  • If you review the SQL Backup log files for when you expect files to be deleted, are there any error or warning messages?

    How long ago did you upgrade to version 5.4?
    Matthew Flatt
    Redgate Foundry
  • sorry for not responding.

    Here is the situation now...

    The same script is being used from above, but only the network files are being deleted like they should...I looked in the help file and here is what the help said about fileoptions:
    Specifies whether old backup files are to be deleted or overwritten in the primary backup folder and any COPYTO folders. Specify the sum of the values that correspond to the options you require:
    
    1
     Delete old backup files in the secondary backup folders &#40;specified using COPYTO&#41; if they are older than the number of days or hours specified in ERASEFILES, ERASEFILES_ATSTART, or ERASEFILES_REMOTE.
     
    2
     Delete old backup files in the primary backup folder &#40;specified using DISK&#41; if they are older than the number of days or hours specified in ERASEFILES, ERASEFILES_ATSTART, or ERASEFILES_REMOTE unless they have the ARCHIVE flag set.
     
    4
     Overwrite existing files in the COPYTO folder.
     
    
    Valid values are 1 to 7.
    
    If you specify option 1 or 2 you must also set the age of the files to delete using ERASEFILES, ERASEFILES_ATSTART, or ERASEFILES_REMOTE. For example, to delete old backup files in the COPYTO folder that are older than 5 days:
    
    BACKUP DATABASE... WITH COPYTO = ... , ERASEFILES = 5, FILEOPTIONS = 1
    
    To overwrite any existing files in the COPYTO folder and also delete old backup files in the COPYTO folder that are older than 5 days &#40;values 1 + 4&#41;:
    
    BACKUP DATABASE... WITH COPYTO = ... , ERASEFILES = 5, FILEOPTIONS = 5
    

    So reading from that, my FILEOPTIONS flag needs to be set to 3 if I want to delete files at BOTH locations that are older than the specified ERASEFILES flag...

    is that right?
  • ok Setting FILEOPTIONS = 3 didn't work...in fact it didn't delete on local disk and on the network...

    By reading what the FILEOPTIONS flag does, I have to think this has to be a bug...

    If I set it to 1, it will delete the files from the network. Tonight I will run a backup with the flag set to 2 to see if it deletes the files from disk. If that works, then there should be no reason that 3 would not do both (from what the help file explained the flag).

    Any help on this would be great.
  • Any ideas on this?
  • ok Setting FILEOPTIONS = 3 didn't work...in fact it didn't delete on local disk and on the network...

    By reading what the FILEOPTIONS flag does, I have to think this has to be a bug...

    If I set it to 1, it will delete the files from the network. Tonight I will run a backup with the flag set to 2 to see if it deletes the files from disk. If that works, then there should be no reason that 3 would not do both (from what the help file explained the flag).

    I have the same situation on my system. Make sure the Windows OS Archive Flag is not set on the local system for the files. SQL Backup will not delete these files if the Archive Flag is set (even if the file meets the date time criteria).

    Mike
  • are you talking about removing the read-only flag from the backup folder?
  • are you talking about removing the read-only flag from the backup folder?

    No. One of the File Attributes Windows puts on a File is "Archived" (A). Read-Only (R), Hidden (H), System (S) are some other File Attributes.

    The Archived Flag is designed to be used by backup systems so when a file is created it gets the Archive Flag. When certain Windows backup software backs up the file, it removes the Archive Flag. This is Windows way of protecting files that have not been backed up (to tape or whatever) from being deleted prematurely.

    SQL Backup product looks for that flag and if it is set, it will not delete the local .sqb files.

    In Windows Explorer, right-click on the File and choose properties. Then click on the "Advanced" button in the Attributes section of the form. The "File is ready for archiving" will be checked if the Archive Flag is true.

    Mike
  • ok, all of my backup files are set to that as you said...but how do I automatically turn that off? That seems like a pretty rough manual process to have to go in there every day and uncheck the newly created backup files so they will be deleted when I want them to be deleted.
  • ok, all of my backup files are set to that as you said...but how do I automatically turn that off? That seems like a pretty rough manual process to have to go in there every day and uncheck the newly created backup files so they will be deleted when I want them to be deleted.

    There is no way (that I know of) to prevent Windows from setting this flag when a file is created or modified.

    However, you can run a command to remove that flag on an entire directory and all its subdirectories. Just put the following command in a batch file and run it on your desired schedule:
    attrib -A D:\DatabaseBackups\*.* /S /D
    


    Mike
  • That's interesting...

    If that's right (which I am not doubting you, just talking out loud), then there is no way to naturally delete the local files that you create...so the FILEOPTIONS flag is meaningless without doing something manually...

    I am fixing to try and run a backup after I remove the archive flag. Coincidentally, the network files have been deleting (when I had the proper FILEOPTIONS flag set) even when the archive flag was set on them...
Sign In or Register to comment.