disaster recovery stragedy

[swoodring]

I want to make sure that we have a reliable backup/restore method in place. our database is used for our datawarehouse and is loaded up with daily sales info each night.

Our scenerio is as follows:
Do a full backup everynight of all the dbs. We only have one production db and a test version of it. Then all the default db that came with sql pubs, northwind, tempdb, master, msdb, model.
Do log file backups during work hours 8 - 5 Pm every 2 hours.


We only have one server so I can't try it out on another server.

We have a test version of the production database except less data. So I restored that db to a new db and compared the 2 and everything looks the same. Should I also try restoring the other pertinent db to another db name to make sure they restore as well . I.e master, model, msdb

What do other people do to test there backup plans. How ofter should I test them out to make sure they are reliable.

I'm fairly new to this dba role so any suggestions would be greatly appreciated. Thanks

 

[tlbroadbent]

It is always a good idea to test your backup plan by restoring databases from backup. We usually append a suffix of _Restore to the database name to test the restore. For example, we would restore AcctPayDB to AcctPayDB_Restore. We are running SQL 7.0.

It is extremely important to have backups of master, msdb and model databases. Test the restore process occasionally to make sure your backups are good.

Another benefit of testing the restores is getting a handle on the time required to restore your system. In case of a system crash, everyone will want to know how long it will be down.

Your plan looks relatively sound. I would do log backups more frequently than every two hours. Most of our transaction log backups occur every 1/2 hour. The frequency of log backups depends on the frequency and volume of transactions processed. You may want to look into differential backups.

Read Backing Up and Restoring dabases in SQL BOL. Also check out these articles.

Create a complete backup solution:

http://gethelp.devx.com/techtips/thesqlpro/10min/10min0101/10min0101.asp

Backup strategies:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9629

Restoring databases:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9808

Speed database recovery with differential backups:

http://www.swynk.com/friends/boyle/dbbackup.asp

Terry

http://members.home.net/tlbroadbent/index.htm

 

[swoodring]

Thanks for the links I'll check them out. The reason we only do log backups every 2 hours is that our databases are used for a datawarehouse and during the day after the load up of the data is complete for the day no new data is loaded. Unless of course I'm doing something with it.

 

[tlbroadbent]

As I said, log backup frequency depends on activity. If you have no activity, then you have no need to do log backups. Terry

http://members.home.net/tlbroadbent/index.htm