MS SQL Server 2008 restoring DBs with SMO corrupts resourceDB

Question

I am currently facing a very strange issue with one of my database instances. I wrote a small application to backup a set of databases from one MS SQL Server 2008 instance and restore the same set on top of existing DBs on a second instance. Both instances are running on top of Windows Server 2008R2 machines. Backups and restores are done using MS SQL Server Management Objects (SMOs), built into a simple C# Windows Form.

After I restore the DBs on the second instance, I encounter a few issues:

• Right-clicking on a database and select Properties gives me the following error message |-> Cannot show requested dialog (SqlMgmt) |-> An exception occurred while executing a Transact-SQL statement or batch |-> Could not continue scan with NOLOCK due to data movement. (Microsoft SQL Server, Error: 601)
• I cannot open Activity Monitor (same error message)
• SQL Error log reports the Resource DB is corrupted ("SQL Server detected a logical consistency-based i/O error: invalid protection option. It occurred during a read of page ... in database ID ... at offset ... in file ...")
• the SELECT SERVERPROPERTY('ResourceVersion') command gives out a version number
• copying the mdf and ldf files for the resource DB from another SQL Server (and restarting the services) solved the issues

Now I know the resource DB is supposed to be read-only and outside of HW issues there's no way it can get corrupted, which really has me wondering, where did I mess up? And why does replacing the resource DB fix my issue?

Has anyone ever encountered the same issue?

Update: Backups are performed with the following commands:

Backup backup = new Backup();
backup.Action = BackupActionType.Database;
backup.Database = databaseName;
backup.CopyOnly = true;
backup.Checksum = true;
backup.BackupSetDescription = "Full backup of " + databaseName;
backup.BackupSetName = databaseName + " Backup";
backup.Initialize = true;
string backupFilePath = backupLocation + "\\" + databaseName + ".bak";
backup.Devices.AddDevice(backupFilePath, DeviceType.File);
Microsoft.SqlServer.Management.Smo.Server smoServer = new Server(connectionString);
smoServer.ConnectionContext.StatementTimeout = 0;
smoServer.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect;
smoServer.ConnectionContext.Connect();
backup.SqlBackup(smoServer);
smoServer.ConnectionContext.Disconnect();

..and verified as follows:

Restore restore = new Restore();
string backupFilePath = backupLocation + "\\" + databaseName + ".bak";
Microsoft.SqlServer.Management.Smo.Server smoServer = new Server(connectionString);
BackupDeviceItem deviceItem = new BackupDeviceItem(backupFilePath, DeviceType.File);
restore.Devices.Add(deviceItem);
bool verified = restore.SqlVerify(smoServer);

Restores are performed with:

Microsoft.SqlServer.Management.Smo.Server smoServer = new Server(connectionString);
smoServer.ConnectionContext.StatementTimeout = 0;
smoServer.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect;
smoServer.ConnectionContext.Connect();
Database db = smoServer.Databases[databaseName];
Restore restore = new Restore();
string backupFilePath = backupLocation + "\\" + databaseName + ".bak";
BackupDeviceItem deviceItem = new BackupDeviceItem(backupFilePath, DeviceType.File);
restore.Devices.Add(deviceItem);
restore.Database = databaseName;
restore.Action = RestoreActionType.Database;
restore.ReplaceDatabase = true;
restore.SqlRestore(smoServer);
db = smoServer.Databases[databaseName];
db.SetOnline();
smoServer.Refresh();
db.Refresh();
Restore rest = new Restore();
rest.Devices.AddDevice(backupFilePath, DeviceType.File);
bool verifySuccessful = rest.SqlVerify(smoServer);
smoServer.ConnectionContext.Disconnect();

I know the connection should actually disconnect itself once the backup or restore is complete, and that does actually connect and disconnect correctly, this latest version with manual connect and disconnect was an experiment to check if there was any difference in the end result. There wasn't.

Both backups and restores are executed in a background worker, inside try...catch loops, and no exceptions are raised.

I believe the params are self-explanatory, but if something is unclear I'd be happy to clarify.

Update 2: After a more thorough round of testing, I was unable to reproduce the resource DB corruption issue. Ergo I am blaming this on planetary alignment when I first tried out the restores. Thanks Jeroen for the help and the hints!

Answer 1

There's no reason to assume your backup/restore had anything to do with the database failing. Do the usual dance when you encounter corruption: run DBCC CHECKDB, check your hardware for errors, see if there are cumulative updates applying to your version that might resolve corruption issues. If you can consistently reproduce the error by restoring your databases, even on another machine with the exact same version of SQL Server, you may have found a bug MS is interested in. Otherwise, I'd take a long, hard look at the hardware.

The fact that replacing the resource database fixed it does not in any way imply you've found and fixed the underlying problem, just that it's not corrupted anymore. This still tells you nothing about the cause of the corruption.

And just for completeness: nothing you do short of manually editing database files (through DBCC PAGE or just by writing to it outside SQL Server) is a legitimate reason for database corruption, so you definitely didn't "mess up".