Manage backup biles on network share

Osolage · September 25, 2008 12:49PM

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

jerryhung · September 26, 2008 10:01AM

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

petey · September 26, 2008 10:51AM

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'

MasterCephus · September 30, 2008 9:28AM

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.

petey · September 30, 2008 12:13PM

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.

MasterCephus · September 30, 2008 12:17PM

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?

petey · September 30, 2008 12:25PM

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.

MasterCephus · September 30, 2008 12:43PM

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.

petey · September 30, 2008 12:50PM

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.

Illuminations2k · November 25, 2008 3:22PM

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?

Matthew Flatt · November 26, 2008 10:31AM

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.

Illuminations2k · November 26, 2008 12:14PM

Matthew,

Thanks for the quick reply. That worked perfectly!

-Adrian

MasterCephus · December 2, 2008 10:12AM

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?

Matthew Flatt · December 2, 2008 11:36AM

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.

MasterCephus · December 2, 2008 11:51AM

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...

Matthew Flatt · December 2, 2008 11:56AM

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.

MasterCephus · December 2, 2008 12:01PM

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?

Matthew Flatt · December 2, 2008 12:03PM

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.

MasterCephus · December 15, 2008 9:39AM

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

Matthew Flatt · December 15, 2008 12:50PM

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,

MasterCephus · December 15, 2008 1:37PM

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.

Matthew Flatt · December 16, 2008 1:55PM

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?

MasterCephus · December 23, 2008 8:57AM

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?

MasterCephus · December 24, 2008 8:27AM

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.

MasterCephus · January 5, 2009 8:54AM

Any ideas on this?

michaelfried · January 5, 2009 7:18PM

MasterCephus wrote:

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

MasterCephus · January 5, 2009 8:36PM

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

michaelfried · January 5, 2009 8:43PM

MasterCephus wrote:

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

MasterCephus · January 5, 2009 8:45PM

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.

michaelfried · January 5, 2009 9:02PM

MasterCephus wrote:

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

MasterCephus · January 6, 2009 8:59AM

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...

Manage backup biles on network share

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk