I broke my database completely yesterday.
I was reorganizing a huge storage area into 6 different areas.
I forgot to remove one of the indices.
I aborted the ALTER STORAGE MAP.
Attempts to attach to the database informed me that the root storage area was corrupt.
It appears that I had placed a SET TRANSACTION BATCH UPDATE at the beginning of the process. It is well documented that if you abort a BATCH UPDATE transaction you will corrupt your database…I forgot and didn’t look at the commands carefully enough. I had tested this about 5 times on the backup system, but I moved the primary commands out of the test procedure…
Fortunately we have operator assisted backups – a full database backup every day after processing is complete. We take the backup and restore it to our backup system every weekday as well…so the process of restoring our database is pretty simple.
A reminder – we had a number of “FAILED” SQL/Services services. The simple answer was to do SQLSRV$SHUTDOWN followed immediately by SQLSRV$STARTUP.
The database was down for approximately 2 hours.