Restore Master Database in SQL Server

Before the master system database can be restored, it is required the SQL Server instance to be in single-user mode.

For information about how to start SQL Server Instance in Single User Mode read previous blog post:
Start SQL Server Instance in Single User Mode

To restore a full database backup of master database, use the following statement in the sqlcmd utility:

For example:
RESTORE DATABASE master FROM DISK = 'D:\Backup\Master.bak' WITH REPLACE;
GO

To connect to a named instance, the sqlcmd command must specify the -S ComputerName\InstanceName option.

After master database is restored, the instance of SQL Server shuts down and terminates the sqlcmd process. Before you restart the SQL Server instance, remove the single-user startup parameter, and start the Microsoft SQL Server Service in normal (multi-user) mode.

If you get the error message when you are trying to connect to the SQL Server instance that is in single-user mode:

Login failed for user 'domain\username'. Reason: Server is in single user mode. Only one administrator can connect at this time.

It means that there is something using your single connection. You need to find the service or connection that uses up the only connection and stop it before you can log in. Check SQL Agent, SQL Reporting Services, SQL Analysis Services...

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">