0

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.

  • How you are taking the backup . I mean to say that through maintenance plan or TSQL script task schedule. – Md Haidar Ali Khan Sep 1 '16 at 7:53
  • Maintenance plan. – Java Main Sep 1 '16 at 7:54
1

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

| improve this answer | |
  • I am not going to change the backup location. Only thing i am going is to flush the folder so that backup will be only for the current week. The other backed up weeks will be on some separte folders copied there manually. This way i can avoid to have backups stacking on weekly basis in 1 huge file. – Java Main Sep 1 '16 at 9:29
  • That is certainly your choice - In your question, you asked for a better backup strategy, so I offered one :) – Scott Hodgin Sep 1 '16 at 9:32
  • I'd suggest you automate the copying and deleting of files by invoking a Powershell script before your backup process. Depending on what files you are copying (Full, Diff, Log), you might need to give them a different file extension so Powershell would know what files to work with. You could copy files older than 1 week (or whatever interval) and then delete them. There are plenty of Powershell examples out there that do this type of thing. – Scott Hodgin Sep 1 '16 at 10:07
  • Yes that is a good appparoach. However i only have 1 backup file that is an agregation of the 3 backup types. – Java Main Sep 1 '16 at 10:25
  • 1
    That's interesting that you use just 1 file. To be honest, I've never used that strategy. I've always dumped each backup to it's own file, specifically for file management and recovery purposes. – Jason B. Sep 1 '16 at 13:07
1

You have a serious risk here.

Right now, it appears (based upon your question) that you are backing up to a single file, which is why it is so large. You need to be backing up to different files if you don't do this you run the risk of corruption within one of those backups leading to your data being unrecoverable.

I would highly recommend looking at different subplans in your manintenance plan each to handle writing the backups to different files, and have those files rolled daily.

From a longer term perspective it would be worth expending time looking into how backups/restores work from a TSQL perspective, and then implement either your own, or some other solution to take backups, and get away from maintenance plans.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.