Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

databases reads inconsistency I/O error Expand / Collapse
Posted Thursday, April 14, 2011 9:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 9, 2012 7:30 AM
Points: 1, Visits: 16

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.

Post #1093893
Posted Friday, April 15, 2011 12:00 AM



Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 45,619, Visits: 44,147
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1093916
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse