I created SQL server scheduled backup as follow :
-
Full: 2 times a week
-
Differential: 6 hours a day
-
Log: 3 hours
The problem I am having is that after one month I end up with a backup file of size 30GB and that is too heavy to restore, knowing that one full backup is only 1 GB max.
Can I, for example, each week save the .bak file (created as described above) somewhere and delete it from the SQLSERVER backup folder (delete it just after the last log backup that is before the next full backup occurs), so that the next week a new Full backup will be created then Differential and Log.
Or you may have a better strategy, then please elaborate.
Best Answer
Sql Server doesn't care 'where' your backups are ultimately stored. Having said that, I can assure you that Sql Server 'knows' where the backups were 'created' (physical location) and stores that information in the MSDB database. Sql Server would try to use that information to generate a restore via SSMS GUI and the files wouldn't be there. Moving backup files around will also complicate an attempt to automate a restore plan by utilizing the information in the MSDB database tables because the files are in a different place than where they were originally created. And, I think you definitely want an automated restore plan, especially with trying to restore a bunch of logs.
In my opinion, the 'better' strategy is to allocate a separate disk drive or share with enough space to hold your intended backups. I personally don't like maintenance plans for backups because they don't give you enough options. A better choice is to use Ola Hallengren's maintenance scripts (backup, being one) - https://ola.hallengren.com/sql-server-backup.html