What are some situations that would cause a SQL database to "break" or become corrupt and warrent a restore?

Question

I am converting from Access to SQL 2008 and I am wondering what are some of the common ways to "break" a SQL database?

In Access, you could have the DB open over the network and suddenly lose your network connection which would cause the DB to become corrupt and you would have to do compact and repair.  If your query is taking a long time and you just abbruptly do a CRTL Alt DEL and close the DB or software that also might cause the break. Many of these Access issues are because it is a File-Sharing Database...vs SQL is a true Client/Server DB.

What are some generic cases that might cause a SQL breakage?
Also, just so that I am not misunderstanding anything, there is no "Compact and repair" type feature in SQL server to fix the DB?  You need to have a backup of your MDF  files. Is that correct?

Thank you in advance.

Answer 1

If the host computer loses power the server will obviously crash. When SQL Server starts up again, it must "recover" the database. This isn't considered broken. It's a normal operation. Any transactions that were not fully completed are backed out. And then the database is usable.

Losing a network connection to a client isn't a problem (at least not for the database). When the connection is lost, the database engine will rollback any incomplete transactions and will be in a consistent state. Since the client didn't get a confirmation of any transactions, the client must be smart enough try again.

The most likely cause of a database corruption, is a disk problem. Such as the failure a whole disk or some bad sectors on the disk. To prevent data loss from a disk failure, you can setup up RAID or mirroring, or just make frequent backups of the database.

There are ways to repair damage. Start by looking in SQL Server Books Online under DBCC CHECKDB (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms176064.aspx There are other DBCC commands near that topic which address specific issues.

Compacting a table is usually done by rebuilding an index. Compact a whole database with the SHRINK options of ALTER DATABASE http://msdn.microsoft.com/en-us/library/bb522682.aspx

And yes, you should backup your database. Don't use Windows to backup the file. That will backup everything including empty space, and you will lose some important options. Use Transact-SQL BACKUP commands. You must backup the transaction log as well as the database. Start with http://msdn.microsoft.com/en-us/library/ms187510(SQL.100).aspx

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 2

Thanks Rick for the great information.  I am going to use my software to perform the Backup using the SQL commands.   On your comments regarding backing up the logs(at the end)....I use Recovery Model=Simple, and I am unable to backup logs.  I would have to switch to Full.

Is there any advantage to switching to Full?'

Using your example....I have valid backups of the MDFs......and the Server's HDD has a major failure. Cant I just attach the latest backup MDF's I have and that would give me a backup to the point of the backed up MDFs?

Based on my understanding, using Logs would allow me to restore and then include all the other transactions that aren't in the backup MDFs?

Answer 3

A production database with substantial activity can benefit from the full recovery model. A transaction log backup can occure frequently. As in a full backup every day, plus transaction log backups every 1 hour. If you are used to backing up an Access database once a week, then the simple recovery model would be fine. You risk losing any data that occurred since your last backup. Which could be a crisis, or could be a minor inconvenience.

After a failure, you don't "attach" the latest MDF. You replace a database from a backup by using the RESTORE command. You are correct that you would return to the point of the backup.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty