Backup of a heavily used / modified Database 1

[AdmREA]

Hello together

The Situation is as followed:

1 MS SQL-Server with an very often modified DB. This System runs and the DB will be used heavily. My Job ist now, to implement an "free-of-costs" Backup-Solution (means: no additional Agents, etc. for our NetBackup-Solution).

What I've done is:
I've made 3 Backup-Devices, let's say A, B, T. In the Device A, I peform a full Backup each Noon in the Device B I perform a full Backup at ten p.m. each day. Each night, this two BAK-Files will be backed up by NetBackup to a tape. To the Device T, I perform an Transaction-Log Backup each 5 Minutes.

What do you think about this kind of Backup? Is it completly wrong, are there better, more secure variations?

Thank you very much for your help in advance.
André

 

[nigelrivett]

You might want to include differentials to make recovery faster and also to reduce the risk of corrupt tr log backups.

Make sure you test the backups - don't just assume they will be restorable.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[AdmREA]

Thank you for this hint. Space for Backups should not be a Problem, so I've done this: Full Backs in different Devices at 6am, 12, 6pm and 12, Differential in different Devices at 9am, 3pm, 9pm and 3am. Between this periods, I backup the tr log each 5 Minutes. During the night (after 3am), Netbackup saves the BAK-Files.

I din't worked with MS SQL or SQL yet bevore, so I have a next question: I thought, that the tr log will bee "deleted" when I perform a Full Back but after performing a few Full Backs by Hand I have still all "5-minutes-steps" in my tr log-bak file.

 

[nigelrivett]

The tr log is not deleted but the entries that are backed up are released and so will be overwritten when the current file entry cycles round.

If you didn't run any tr log backups then the file will just keep growing.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[AdmREA]

I explained it wrong: of course it isn't the tr log itself, its the 5-minute-bak-file which grows and grows. I will have to delete the old entries from time to time. In recapitulation, we can say when at 4.23pm something happens:
restore from Full Back from 12, restore from Diff Back from 3 pm, Restore all 5-minutes-trlog-Backs from 3.05 till 4.20, that's it.

 

[RonPro]

You might want to do this as a maintenance plan. If you use the wizard to define the maintenance plan, it will also allow you to specify that backup types (full or tr logs) should only be retained for a given amount of time. Therefore, you can say that tr logs should be kept for 6 hours (or 1 day, etc) and full backups are kept for 2 days etc.

Also remember that diff backups are *all* changes since the last full backup, so you really don't need to keep more than one of these. I do mine twice per day and simply over-write the diff backup every time.