Database Restore Problem

I've received several questions about backing up and restoring databases. Here's a reader's question that tackles a common restore problem.

Q: I recently migrated several Access databases to SQL Server 2005 Express by using the SQL Server Migration Assistant (SSMA). Next I backed up several databases and copied these backup files to another computer running SQL Server Express. To restore the files, I entered the following command in SQL Server Management Studio Express (SSMSE).

RESTORE DATABASE MyDatabase FROM DISK = 'c:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLBackupMyDatabase.bak'

But I received the following error message:

Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database 'MyDatabase' because it is in use by this session. It is recommended that the master database be used when performing this operation.

Does this message mean that I have to restore the master database before I restore the MyDatabase backup?

A: No, you don’t have to restore the master database before you restore a user database. This message is telling you that your user database connection is using the MyDatabase database. SQL Server Express can't complete the restore operation because the user database has an active connection with your current session. You’ll need to change your database connection to a different database before you attempt to perform the restore. You can use the T-SQL USE command, as I show in the following code, to change your current database connection to the master database before you perform the restore.

USE master
RESTORE DATABASE MyDatabase FROM DISK = 'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupMyDatabase.bak'

Hope this helps, and thank you for sending your question.

-- Michael Otey, [email protected]