SQL Server Performance Forum – Threads Archive

How does everyone backup?

Well we had the C drive of our main server become corrupt over the weekend. Nothing like a disaster to bring to lite the inadequcies of the backup plans. I figure that I would start a topic of what else do I need to be safe from data loss. Here’s my current list of activities that we are/will be doing to recover from emergencies. I would appreciate anyone that has any other reccommendations to please post them. We can take the loss of up to 24 hours of transactions, hence the nightly backup. We will probably be decreasing that time after we get a better backup/recovery plan in place. Nightly :
dump of all databases
Zip all dumps and transfer off server
BCP dump of all data tables
Zip all BCP dumps and transfer off server
Zip all external sql code(text files) and transfer off server Weekly
Burn copies of previous nights zipped files and take off site
Script all databases and jobs and burn to CD and take off site This is all that we are doing right now. We have found that it’s easier to transfer the zip files to a different machine than to dump them to tape. How does everyone on this forum handle backing up the boot disk drive?
Live to Throw
Throw to Live

Whats the size of your database/databases to be backed up and restoration drill -Johnson

http://www.sql-server-performance.com/greg_robidoux_interview.asp
http://vyaskn.tripod.com/maintaining_standby_sql_server.htm
http://sql-server-performance.bitpipe.com/detail/RES/1118070870_794.html Selecting the recovery model and backup options can be simple for your SQL Server implementation. The best scenario is to select the options that provide the most flexibility. The best method is to perform full backups as often as possible depending on the size of your database, along with differential backups and lastly with a combination of transaction log backups. The frequency is dependent on your environment and the acceptable data loss for your company. If you have extremely large databases it will be difficult to execute frequent full backups, so you need to look at a combination of options. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Didn’t put in database sizes. Sorry about that. Pulled an all day & nighter Sunday to Monday to get system back up at 6am. Database sizes vary from 5gig to 30gig. We try to keep the databases under 30gig cause they will compress down to 4-5gig. Anything bigger than 4-5gig times out on the network here cause it takes too long to tranfer across the network. I know it’s not right, but it’s what I have to deal with here. "to a different machine than to dump them to tape" – we dump them locally to disk, zip them, then tranfer them to a differnt machine. Live to Throw
Throw to Live

BCP dump and scripts of databases are useful if you have a table that someone dropped or wiped out on accident. We have actually had to restore more often from BCP dump than from a full dump. The big question that I had hoped to get answers on is how does everyone backup the root drive? I know it’s just a reinstall of the operating system, but then you have to go in and do all of the little tweaks that everyone does. Thanks for all of the replies.
Live to Throw
Throw to Live

For operating system tweaks I suggest to have a build disk with all configuration set about the server and then proceed on SQL SErver side as referred in the given links above. Rebuilding a server can be a daunting challenge with uptime requirements being at an all time high.
No operation is quick and clean, until unless you’ve tested it thorougly even in testing and during production incidents. Backup the existing server, rebuild the server with the additional disks, install Windows and SQL Server and then restore the databases. Always test the disaster recovery process in order to ensure the availabilty is not compromised with long delays. Log shipping and standby server is the concept where it helps a lot in this scenario. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I can explain how we do at our place. We have differnet servers for databases. the Biggest database size is 190GB, this is located in on server. This server, we put the recovery model is "simple" the reason is, we don’t have space to alocate transaction log, coz transaction log consumes huge space in disk. As a precationary we do take differential in table in one hour frequency, and daily full-backup at night. Both Differential and Full backups are in Tape (DLT). We are not using SQL backup, but use Veritas. The reason is, the compression in Veritas is much better, once we backed-up the data, its come down to 70GB (where my current DLT supports till 80 GB ). The other reason why we are using Veritas, we have backup solution for MS-exchange, and ADC backups etc are all through Veritas, which allows to online (live) data to be backedup. Once you take backup, try to take the "system state", this is avalable in windows backup also, this will help you to restore the registry settings as well. -Johnson

]]>