Backups: what we recommend.
Your database should (nay must) be backed up. Ideally off
When I started this blog, over five years ago, I recommended a
nice little product called SQLBackupandFTP. If you are in a large
organisation with your own IT department then you will not need it.
They will have all this organised.
If you are in a medium or fledgling company then this product is
even better today that it was years ago. These days it will backup
databases and logs, eamil you it has done it and even (paid
version) ftp them of to your inexpensive Amazon S3 account.
I recently experienced the solidity of SQLBackupAndFTP. It
will email you if there is an error. In this case, there was
a strange error saying the backup had completed successfully (it
restored OK on another machine so no argument there) but there was
still an error.
It reported (non techies can skip the description):
03/18/2013 11:45:15 Creating backup of
"server database" to
03/18/2013 11:45:52 ERROR: Attempt to
fetch logical page (1:376) in database 4 failed. It belongs to
allocation unit 562949959843840 not to 72057594045005824.
Could not insert a backup or restore history/detail record in the
msdb database. This may indicate a problem with the msdb database.
The backup/restore operation was still successful.
Processed 48992 pages for database 'Server database', file
'AVAPADat' on file 1.
Processed 2000 pages for database 'Server database', file
'AVAPAObject' on file 1.
Processed 4 pages for database 'Server database', file 'AVAPALog'
on file 1.
BACKUP DATABASE successfully processed 50996 pages in 36.274
seconds (10.983 MB/sec).
The statement has been terminated.
SQL Server: ".\AVA2008R2EXPRESS".
03/18/2013 11:45:52 ERROR: Job finished
The important thing was it reported the error and in sufficient
detail that we were able to determine for our customer that whist
their data itself was good, there was a problem with the MSSQL
Database Engine. MSSQL is about as solid a piece of software as it
is possible to get, so this is very unusual. In this case probably
cause by a recent machine freeze and restart.
The solution (non techies can skip this):
We ran DBCC CHECKDB on the customer database: all passed.
We ran DBCC CHECKDB msdb and it reported a crossed allocation in
the backup log table and otherwise ok. I was happy with that, if we
lost a backup record so be it.
II used the following script to repair msdb:
ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER WITH ROLLBACK
DBCC CHECKDB ('AdventureWorks2008R2',
ALTER DATABASE AdventureWorks2008R2 SET MULTI_USER;
No customer data was lost, it took under an hour to fully
resolve and everything was fine.
SQL Server - Repairing the
database using DBCC CHECKDB