May 26, 2013 at 11:44 pm
Hi,
I hope you are doing good.
Iam facing one problem with Restoration master Database in Sql server 2008r2(named instance) enterprise edition.
Actual problem with backup/restore in same named instance.but its not happening restoration.
iam following these steps:
backup of master Database and restore the same named instance with single user mode using sqlcmd utility.(stopped the services also)
command:
restore database master from disk='.....' with replace
go
its not happens restoration and it will shows the Error single_user mode"how to connect:sqlserver.exe...
Please kindly give me your valuable help or suggestions.
Thanks&Regards,
SQL server DBA
May 27, 2013 at 2:50 am
You cant restore ? Are you sure any of your application is connecting into ?
can you post the exact error message?
May 27, 2013 at 4:59 am
Thanks for your reply.But right now there is no application connect to the server.
SQL server DBA
May 27, 2013 at 5:27 am
Connect the SQL Server instance in Single User Mode and try restoring it.
How to Start Instance in Single User Mode.
Open Sql Server configuration Manager -> Sql Server Services -> Sql Server (Your Instance Name) Right Click -> Properties -> Advanced -> Startup Parameters -> Change -d to -m and restart the services.
May 28, 2013 at 1:54 am
Thanks for your reply.its already single user mode...but while im connecting through sqlcmd utility it's show the error.
restore database master from master_backup
go
Server: Msg 3108, Level 16, State 1, Line 1
RESTORE DATABASE must be used in single user mode when trying to restore the
master database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
For reference please go through the link (i think better understanding)
http://www.ablongman.com/samplechapter/0130622982.pdf
......
i appreciate your consideration and time and help...
Thanks&Regards,
SQL server DBA
May 28, 2013 at 6:56 am
the error message implies the service has not been started in single user mode. Have you got the right instance?
post exactly what you have done.
see http://msdn.microsoft.com/en-us/library/ms180965(v=sql.100).aspx
---------------------------------------------------------------------
May 28, 2013 at 12:45 pm
Below is a document we created in house for this. It should work without issue. of course edit the information to meet your server.
How to Restore Master from Backup
1) Ensure all SQL Services are stopped.
2) Locate the sqlservr.exe file. Normally this is located in the below location.
d:\Program Files\Microsoft SQL Server\MSSQL10_50.instance\MSSQL\Binn
3) Locate the master backup for SQL.
4) Open two command prompt windows.
5) Get to the location of sqlservr.exe and enter the below command.
For local installs of SQL “sqlservr.exe –f –m”
For named instances of SQL “sqlservr.exe –f –m –s instancename”
6) Using the other command prompt window, run the below command.
For local installs of SQL “Sqlcmd “
For named instances of SQL “sqlcmd -S full\instancename”
7) Type in the below command on a single line.
RESTORE DATABASE master FROM DISK = 'F:\MSSQL.1\MSSQL\Backup\master_backup.bak’ WITH REPLACE
8) After you press enter, you still need to type GO to complete the command.
GO
9) After the restore is complete, close the command prompt windows. Once all command prompt windows are closed, it will release SQL Server.
10) Start SQL server normally and you should be on the previous backup.
.
May 29, 2013 at 12:27 am
Using -f implies the -m parameter so just use either\or.
To ensure that no applications connect and take the single connection I prefer the following startup command
[Code]Sqlservr -c -m"SQLCMD"[/code]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply