Comments
Sort by recent activity
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 (specified using COPYTO) 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 (specified using DISK) 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 (values 1 + 4):
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? / comments
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 i...
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. / comments
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 ...
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 [LIST OF DBS] TO DISK = ''D:\MSSQL10.MSSQLSERVER\MSSQL\Backup\<TYPE>\<DATETIME mm_dd_yyyy>\<AUTO>.sqb'' WITH COMPRESSION = 2, COPYTO = ''\\homebackup\FULL'', ERASEFILES_ATSTART = 3, FILEOPTIONS = 1, INIT, MAILTO = ''EMAIL ADDRESS'', THREADCOUNT = 4, VERIFY"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END
/ comments
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 ...
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\<AUTO>.sqb'' WITH COMPRESSION = 2, COPYTO = ''XXXX'', ERASEFILES_ATSTART = 3, INIT, MAILTO = ''XXX'', THREADCOUNT = 4, VERIFY"'
I don't have the FILEOPTIONS in my script... / comments
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...
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? / comments
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?
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? / comments
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...
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? / comments
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...
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. / comments
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 c...
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. / comments
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 ...
Thanks! I wasn't going to post that information [image]
I was wondering if there was a sales website where I could log in and find that information out. I'll contact your sales team. Thanks! / comments
Thanks! I wasn't going to post that information
I was wondering if there was a sales website where I could log in and find that information out. I'll contact your sales team. Thanks!