April 14, 2011 at 9:56 pm
Hello,
Recently I faced a BSOD in my windows and I have reload the OS. All the SQL databases are gone since the C: drive is formatted. Once I re-installed OS, SQL server I created databases with the help of .bak of all my databases. However the most of databases are unable to be restored and gives IO inconsistency error. Some of them are restored with the same error specified. And finally the problems are somewhat settled with huge data loss. My question is
1. What is the best backup strategy for MSSQL such that after re installation of SQL server, the databases must be as before it was crashed with all permissions with minimal time.
2. What causes the IO error. I google it but could not get an accurate answer for what I am searching.
Note: I just back up my user databases and not the master file database. What does the master file do and does it have effect while restoring the database.
Any help/ideas will be highly appreciated.
Regards,
Riyas
April 15, 2011 at 12:00 am
1) Depends on your downtime and data loss allowance (RTO and RPO SLAs). In short, you need a backup strategy that will allow you to meet them. Find out what they are, then start designing.
Backup the system databases. There's all sorts of things in the system DBs that you don't want to be losing (logins, linked servers, jobs, job history, etc)
Run consistency checks often. When the DB is corrupt is too late. You need to know if you're backing up corrupt databases.
Test your backups. Restoring a backup is the only way to be 100% sure it will restore
Save the backups somewhere else. A backup stored on the same drive as the data file is a waste of space.
Test your recovery strategy.
2) Could have been anything in the IO stack. Buggy IO drivers, misbehaving filter driver, faulty hardware.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply