0

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?

  • You could consider incremental backup or replication. – McNets Nov 5 '18 at 11:42
  • @McNets Thank you for reply, yes i am thinking but i am afraid of data losing data between editions, how should i design backup method? does backuping with maintenance plans and copying to Nas works? – Y. Ozdas Nov 5 '18 at 11:56
  • Yes, it works, but according to your comments it takes a lot of time. Differential backup helps you to reduce backup files size, so you can easily move this files to the NAS device. Differenctial backup, Have a look to : Transactional replication – McNets Nov 5 '18 at 12:05
  • Hello, as already mentioned you could start reading about the difference between simple recovery mode and full recovery mode, and how this refers to the types of backup that can be used. Then experiment with a test database and T-sql backup (and restore) queries to undertstand. I personally do not use Maintenance Plans to do backups but the solution of Ola Hallengren (ola.hallengren.com). I installed it on a database of its own (or a database only for dba-purposes). It takes some time to get into it, but once you get the hang of it, it is much more simple to administer. – luc Nov 8 '18 at 18:10
0
  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.

  1. 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).

  1. 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?

  • Thank you for reply, i am not professional on Database, but i am doing my best to keep it up, i am not sure what is simple recovery mode!, let me answer your questions, the bak files doesnt write on same file, every backup is new file and named with date and database name, i tried today to backup a working database which is 65 something GB and it took almost 2 hours to backup and 2-3 hours to transfer to NAS, Backups and transfer run after midnight so no one waits – Y. Ozdas Nov 5 '18 at 12:39
  • You need to tell your company that they almost certainly need to invest in hardware/infra, and they definitely need to invest in training, preferably both. – Aaron Bertrand Nov 5 '18 at 12:40
  • i am afraid complicated methods, i am looking something not complicated and not very hard. – Y. Ozdas Nov 5 '18 at 12:40
  • I have long said fast, cheap, easy: pick 2. You can't have all 3. – Aaron Bertrand Nov 5 '18 at 12:41
  • Thank you i will tell them but at least i am trying to get some information to stand against them and tell my needs. – Y. Ozdas Nov 5 '18 at 12:42

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.