Help me with sql backups please

Question

I'am facing some difficulties regarding my Sql Backup method, i am not sure if my method is correct, i am using a backup method described below;

1. Created a Full backup named Xbackup in Maintenance Plans, which backups in X External Drive with the extension of *.bak.
2. Once in a day Job created Automatically in Sql Server Agent .
3. Once in a day backup software transfers the bak files to a NAS device.
4. If *.bak files are older than 2 days a job deletes them on server
5. If *.bak files are older than 5 days a job deletes them on Nas Device

The difficulties are;

1. There are 7 database to backup and 7 *.bak files to copy, it is huge 330 GB to back up and transfer to NAS everyday and it is increasing up to 5 gb everyday.
2. Some times backups does not transfered correctly
3. it takes a lot of time to backup and transfer it to Nas device

Not: Nas device was shutting down when it finishes the transfer and Turn on when transfer time comes, but nowadays i canceled this task because of increasing file size i couldn't figure it out how long it takes to backup .bak files and how long Nas device has stay on in order to finish transfer.

What would be your suggestions?

Answer 1

1. There are 7 database to backup and 7 *.bak files to copy, it is huge 330 GB to back up and transfer to NAS everyday and it is increasing up to 5 gb everyday.

That is the size of the data you have. Either you need to back it up, or you don't. Are all of these databases in simple recovery mode? Should they be? One thing you could consider is taking full + diff/log backups and rotate the schedule. Database 1 gets a full backup on Sunday, and diff backups once a day for the rest of the week, and log backups throughout the day on each day. Database 2 gets a full backup on Monday, and diff backups once a day for the rest of the week, and log backups throughout the day on each day. And so on. This way you spread out the pain of the bigger backups throughout the week.

But as your data grows, you need to grow your capacity along with it, whether that's a better network pipe, a more reliable/bigger NAS, or both.

One thing to note is that if your backup file is getting bigger and bigger all the time, a very common issue is writing backups to the same file without using WITH INIT. We just talked about this here.

2. Some times backups does not transfered correctly

This does not seem like a database issue, sounds like infrastructure. Maybe you need a better NAS or a more reliable network or better "backup software" or just simply a better, more resilient copy method (e.g. RoboCopy).

3. it takes a lot of time to backup and transfer it to Nas device

Let's split those out.

1. If the backup takes a long time, again, consider whether you need to be taking full backups every day. This seems atypical unless you really could not care less about point in time recovery. If you do, and use full recovery model, this should help because (a) you won't have to perform a full, standalone backup every night, and (b) suspect the size of the log will be much better managed as well.

2. If the network transfer takes a long time, that is likely a symptom of your network and not anything you're going to be able to fix with code or changes to your backup routine. But this should be a background process anyway. Does it matter if it takes 10 minutes or 12 minutes? Is someone actively waiting on this copy to complete?