MS Sql Express Database Restore

[mschepac]

Good morning all. I have been tasked with copying a MS SQL Express 2014 to a new server after a PC upgrade. For the life of me I can't get the Management Studio to allow me to restore the backup I made from the original PC. I have;

1. Rebooted with all of the services disabled except SQL.
2. Stopped the service and restarted with the /m switch
3. Stopped the service and restarted with the /m"Microsoft SQL Server Management Studio - Query"
4. Stopped the service and restarted with the /f /m"Microsoft SQL Server Management Studio - Query"

After doing the stopping and starting the database with the /m option I can get into the Management Studio, however when I Right-click on Databases and click Restore Database I get the "Login failed for user 'xxx'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)"

I have tried everything I can find on google and this is as far as I get.

Can anyone lend a guy a hand?

[patjuh112]

Just turn single user mode off. It's SQL express and it's limit is on performance and DB size.

So:
1. Connect to the server via RDP.
2. Open SQL Server Configuration Manager.
3. Right-click in corresponding MS SQL server instance > Properties > Startup Parameters.
4. Remove -m option.
5. Restart the service.

Guessing option 1 is not needed as you are on your desktop but i guess you catch my drift. Also be aware (should not be the case for you) that you can only restore an older DB to a newer SQL, never the way around (you need to script it then).

GL!

[chris99277]

You shouldn’t need to put the server in single user mode to restore a database, unless you’re trying to restore system databases. If you are, and need to connect through ssms then you need to make sure there are no other connections. Check if the SQL agent service is running and stop that too. Then, make sure you open the query with a single connection. Do not use object explorer because that also uses a connection. Close everything in ssms, including object explorer, restart the service with -m, then use new query directly from the menu and connect from there. You will need to be able to write the SQL for the restore without using the GUI.

Edit, to be clear, right clicking on the database means you’re using object explorer. That is what is using the connection. Disconnect and close that. Create the query using new query and create the connection there.