What is the SQL Server backup and maintenance plan?

Question

As a continuation of this question, I am wondering what SQL Server backup / maintenance plan is and how I can improve my work.

I am currently launching two plugin maintenance plans using a plugin.

The first one works at night and does almost everything ...

• Full backup of the database and transaction log
• integrity check, index rebuild, repeated statistics, etc. (I checked everything except incremental backup)

The other runs every three hours and performs incremental backups (I'm paranoid, I know this is probably too much).

Backups to disk, full backups are sent to the SAN, stored for a week.

Do you think this is a smart plan? Any suggestions?

EDIT: This is SQL Server 2005. The database is 5 GB, growing by about 1 GB / month.

Answer 1

That sounds good. I am more paranoid. I make two daily full backups and hourly backups of transaction logs. Depends on the size of the database or course. Backups are made directly to disk and then written to tape at night.

You probably don't need to perform maintenance tasks every day. I only do them on weekends, with the exception of one table that we review every night. Again, this depends on the size and activity of the database.

If you have enough disk and disk space, you can back up your disk backups to save space and make it easier to transfer to tape or another place.

Answer 2

You should talk with your users / clients / data keepers - as you call them. They need to have a clear idea of ​​how much work they can lose. Write down the SLA if you don't have one. You don't want any surprises when it comes to bad news.

They should also understand that recovery takes time. You need to plan a recovery plan to create an acceptable recovery time. This can mean a daily full backup, 4 differentials and log backups every 5 minutes. It's not crazy or paranoid, as Marcus Erickson said, it all comes down to your information and the value of the dollar that your organization puts on it.

Answer 3

I don’t think you are paranoid by running backups every 3 hours. Basically, your backup plan should be measured by your recovery requirements. How long you can afford to sleep while you recover, and how much data you are ready to lose until you fall. For SQL Server, you can reduce the amount of data that you are prepared to lose by adding transaction log backups to the backup plan. Many people do this every few minutes depending on the number of transactions that go through the system. To restore, you simply restore the last full, last increment, and then all backups of the transaction log from incremental. This may give you minimal data loss, but it may take a little to use all the transaction log backups. I see quite regularly: Full backup - weekly Incremental backup - night time Log backup - every few minutes depending on requirements (may be useful once per hour, etc.)

Answer 4

Do not forget to make firedrills, where you are actually trying to restore from the backups you created (to the test system). This should be done once a month.

Answer 5

At a minimum, I recommend that my customers perform an overnight full database backup and then back up their transactions every 3 hours. It always amazes me how many people never set up a backup. These are always bad challenges.

Answer 6

In my opinion, the best way:

Make a full database backup every 12 hours

BACKUP DATABASE database TO DISK = 'd:/full.bak'

differential backup every six hours, in case of failure the recovery process is simplified

BACKUP DATABASE database TO DISK = 'd:/diff.bak' WITH DIFFERENTIAL

and, of course, backups of transaction logs that are best done every hour.

BACKUP LOG database TO DISK = 'log.bak'

If, in the event of a failure, the recovery process will be as follows:

• Last full backup
• Last differential backup
• Latest transaction log

Admittedly, it is better to use the full recovery model to provide instant recovery.