Error in attaching mdf file from a crashed server to a new server

Question

One of our server hosting an important database crashed.

The sysadmin team could only access the files by erasing the password with a tool and then log in to access the .MDF files.

I copied the data to the new server and attempted to attach it, but I get the following error:

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

CREATE FILE encountered operating system error 32 (The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf'. (Microsoft SQL Server, Error: 5123)

Answer 1

In general if you have a crashed server and need to bring up your databases on a new server you want to restore from backup not copy and reattach the mdf files. The crash could have caused corruption that will not exist in the backup.

You can restore your system databases as well although it isn't always recommended. If you are going to you need to make sure your new system is as close to the old as possible. Particularly the patching level for SQL Server.

If you do not have backups, and you absolutely have to have your system databases from the old server then you have two options. You can either attach as a different dbname and copy information over (msdb and model only since it won't work for master). Or you can tell your instance that the new location of the system database is actually the correct one.

So in your case you would copy the mdf & ldf msdb files to your new server. Again make sure your patching is the same as the old server. Then follow the instructions in this link to "move" your msdb files. In other words you are going to tell it that the files from the old server are actually the correct ones. Once done you will need to re-start your new instance. Then make sure you keep the original msdb files from the install just in case.

Of course the other way to "move" your msdb files is to shut down your instance, move/rename the installed msdb files and copy in your old msdb files making sure they are in the same location & have the same name. Then restart your instance. If anything goes wrong you can simply move the installed msdb files back into place (while the instance is shut down) and try a different method.

Answer 2

Looks like you are trying to attach user databases and system databases and the new server is complaining about the file being in use.

Msdb cannot be attached like a user database. If you don't have vital data in this database, I would suggest to leave it alone and don't try to restore/attach it. If you have some data you want to recover (jobs, alerts, database mail...), you can attach it with a different name and extract the information you need.