Backups taking a looooooong time

JackieNuby

I've noticed recently my backups are taking a very long time.  It seems to be related to the option to delete files older than 30 days.  If I uncheck that option, the diffs are done in 5 minutes.  If the option is checked, they're still running the next morning.  Any ideas?

JackieNuby

There ended up being an issue with inherited permissions on the database folders created for each database.  Once i turned on inherited permissions for each database folder, the file cleanup now works.

petey2

Given that your retention period is 30 days, I'm going to assume this is on a network share.  The SQL Backup engine has to inspect each of the file on that network share to determine if it should be deleted, and would take a while if there are a lot of files on that folder.

One way to reduce the amount of files is to split the backups by database name and backup type if you have not already done so.  So instead of storing all backups in the same folder e.g. TO DISK = [\\share\backups\...] or COPYTO = [\\share\backups\], use TO DISK = [\\share\backups\<instance>_<database>\<type>\...] or COPYTO = [\\share\backups\<instance>_<database>\<type>\].

Another option, if space is not at a premium, would be to delete the old backups in a separate job, separate from the backup e.g.

EXEC master..sqlbackup '-sql "ERASE FULL_BACKUPS FOR mydb FROM DISK = [\\share\backups\*.sqb] KEEP = 30"'

See this page for the ERASE command details.

JackieNuby

I ran that command.  I've attached image of output.  I still have backup files for DATA_01 from 1/16/2020.  Why doesn't it work?

petey2

A few possibilities:

- are the backup files password protected?
- do the backup files belong to the same server/instance you are running the deletion from?
- are the backups that were not deleted full database backups?

JackieNuby

- are the backup files password protected? - No
- do the backup files belong to the same server/instance you are running the deletion from? - Yes, but I only have 1 SQL server, so how is that an issue?
- are the backups that were not deleted full database backups? - No.  I still have full backups in the folder older than 30 days.

petey2

When you use the ERASE command, it only deletes backup files:
-  belonging to the same server/instance as the server/instance you are running the command from
- for the specified database (DATA_01)
- for the specified backup type (FULL_BACKUPS)
- older than the retention period (30)

If all these criteria match the backup files that were suppose to be deleted but were not, could you please post the results of running a RESTORE SQBHEADERONLY command for any one of the backups that should be have deleted but was not?  E.g.

EXEC master..sqlbackup '-sql "RESTORE SQBHEADERONLY FROM DISK = [<filename>]"'

You can send me the details at peter.yeoh@red-gate.com if you prefer not to post it here.

JackieNuby

Just a thought.  I've had issues with the spinning green circle too and i decided to clear the backup/restore history via this help article: https://documentation.red-gate.com/sbu9/troubleshooting/deleting-backup-and-restore-history-manually .  Could that be the issue?

JackieNuby

Just emailed the "RESTORE SQBHEADERONLY" output.

JackieNuby

I think i may have files that exist now that are no longer in the backup history.  Can i rebuild the backup history from the existing files?  Then i could run the ERASE command to remove the old backups?

David_jack123

You may use the below query to track the progress of backup in SQL Server 2005 and above.
then you should try to use the latest server.
Thanks