Backups: what we recommend.
Your database should (nay must) be backed up. Ideally off site.
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 C:\Windows\TEMP\Pranas.NET\SQLBackupAndFTP\lcgxus5n (full)
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 (With Errors)
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 IMMEDIATE;
DBCC CHECKDB ('AdventureWorks2008R2', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE AdventureWorks2008R2 SET MULTI_USER;
No customer data was lost, it took under an hour to fully resolve and everything was fine.