Backup routines

Hi there,

I am looking for some advice and opinions on daily backup routines on SQL2000 and SQL2005, I want to know what peoples best practices are for nightly full backups. Currently I have the following in place,

job: daily backup

step1. truncate log
step2. shrink log
step3. backup
step. updateusage

job weekly admin

step1. defrag indexes (sometimes re-index + update stats)
step2. truncate log
step3. shrink db
step4. checkdb

Does this look good enough?
is there anything else I should add?
What do you have in place currently?

Thanks for your help in advanceYou should do log dumps more frequently to improve recoverability in case of system failure.
I backup my databases nightly, and do hourly log dumps througout the day. All backups and logs are dumped to disk, and then copied to a network location to guarantee recoverability.|||and you ideally want to move a set of backups offsite and preferably not in the same town\city\suburban sprawl office park just in case of things like hurricanes\flooding\tornados.

another hint. you do not want to truncate your logs. you want to back them up.|||I am lazy and manage a number of different servers. My priorities are to have a consistent backup strategy from server to server and to ensure that the backup will always be there when I need it.

For SQL 2000:
I create two maintenance plans (skipping optimizations, shrinkage and integrity checks).
One maintenance plan is for all the system databases. I do a full backup on these nightly.
One maintenance plan is for all user databases. I do a full backup on these nightly and a transaction log backup hourly (in test, only every three hours).

Depending on the size of the backup, I will backup over the network (we are running 1 GB switched) or local to a dedicated dump disk. Generally, if the aggregate of user databases to be backed up is over 25 GB, then I will back it up locally (we use a SAN, I have a separate job that makes an image copy of the backup disk and runs it to tape once per week).

I set the retention policy to a value based on business requirements and available storage.

For SQL 2005:
I create two maintenance plans.
One does a full backup on all databases (user and system). This runs nightly.
The other maintenance plan does a transaction log backup every hour (three hours in test).
I then edit the maintenance plan to add the retention policy and cleanup activity logs and reports.

All that being said, when you design your backup policy, you need to understand:

1. How much data is your business willing to lose (all, some or none)?
2. How much time is your business willing to spend recovering the database (days, weeks, hours, minutes or seconds)?
3. How much money is your business willing to spend on backup and recovery?

These are the principle drivers for your backup strategy. The technology just lets you achieve the requirements that your business sets for you.

Once you have developed your backup strategy be SURE that you test it. Practice recovery at LEAST every three months. Practice different types of recovery (new build, recover to point in time, recover a full backup, etc).

Remember too, that there are other things that you need to backup on your database server besides just the databases:
1. Linked Server settings
2. DTS Packages
3. Logins
4. Jobs

Regards,

hmscott|||You should do log dumps more frequently to improve recoverability in case of system failure.
I backup my databases nightly, and do hourly log dumps througout the day. All backups and logs are dumped to disk, and then copied to a network location to guarantee recoverability.

If the DB is going to keep growing to a certain size, I would not shrink it, just for it to get to that size again, it's a wasted effort, plus you have to allocate all those extents again, and that's overhead.

I usually do

Full (user and system) - nightly with dbcc checkdb catalog, logins, dts jobs, backup devices, etc.
Differential - 3X a day
TLog - Every 1/2 to 1 hr
nightly idx defrag dbcc indexdefrag.
weekly reindex. dbcc reindex.