January 27, 2011 at 7:40 am
Hi,
I want to restore the model database from the latest backup , I am trying to create a scenario by deleting\moving to other location model.mdf and modellog.ldf but I have latest backup of model database . I am restarting the sql server using TRACE FLAG 3608 but when I am trying to restore the model from backup ,using SQLCMD, I could not and I am getting the following error:
SQLCMD
1> RESTORE DATABASE model FROM DISK='C:\model.bak' WITH REPLACE
2> GO
HResult 0x6D, Level 16, State 1
Shared Memory Provider: The pipe has been ended.
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Communication link fail
ure.
I have checked in configuration tools-->client protocols shared memory ..etc are enabled,but still I am getting the same error.
Please help me in resolving this issue.
Thanks
January 27, 2011 at 11:24 am
could you confirm the command you are using to start the sql server instance?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 27, 2011 at 9:39 pm
Hi, Perry Whittle
The command I am using to start sql service(MSSQLSERVER) is :
sqlservr.exe -T3608
I am trying to restore model database from the backup using SQLCMD and everytime I try to restore I am following error:
The SQL Server Network Interface library succssfully deregistered the Service Principal Name (SPN).
Please help me in resolving this issue.
Thanks
January 28, 2011 at 12:26 am
Hi
The command you need is
sqlservr.exe -c -m
Go to the DOS prompt and change directory to the Binn folder where SQL server is installed and run the command above. once started open a second DOS prompt and connect using SqlCmd to restore the model.
Does the backup you are restoring originally come from this server???
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 28, 2011 at 2:13 am
Hi Perry Whittle,
Thanks for your reply .
When I use the parameters ...\binn>sqlservr.exe -c;-m
I am getting back the ..\binn> prompt sql server is not waiting for client connections.Only when I use trace flag 3608 it is waiting for client connections bcos model.mdf , modellog.ldf is missing in datafolder.
When I use -T3608 and opening another cmd prompt SQLCMD and executing the below code I am getting the following error:
I am executing the below code on SQLCMD
C:\Users\administrator>SQLCMD
1> RESTORE DATABASE model FROM DISK='C:\model.bak' WITH REPLACE
2> GO
HResult 0x6D, Level 16, State 1
Shared Memory Provider: The pipe has been ended.
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Communication link fail
ure.
What other alternatives should I use now ?
Thanks
January 28, 2011 at 3:32 am
\binn>sqlservr.exe -c;-m
This is not correct, it should be
\binn>sqlservr.exe -c -m
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 28, 2011 at 3:41 am
Hi perry whittle,
How can I start sql server service without having model.mdf and modellog.ldf in datafolder.
When I try to do that it is not waiting for connections since it is not finding model data and log files thus giving error.That is why I was using Trace flag 3608 so that it should not check for system databases other than master.
But why it is giving me the error : shared memory provider:The pipe has been ended.
as soon as I try to execute restore command ?
Thanks
January 28, 2011 at 3:50 am
am i correct in saying that you have deleted the model.mdf and model.ldf?
My bad, the only database that can be restored in single user mode is the master database. If you deleted the model database files sql server will not start as it cannot re create the TEMPDB. Now, the original TEMPDB files should be still available as they were not overwritten.
âž¡ Copy them and rename them to model.mdf and modellog.ldf
âž¡ restart sql server service normally from services.msc (leave agent and all other services stopped)
âž¡ open command prompt and restore model from the backup
I have just tried this and it works fine!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 28, 2011 at 4:08 am
yes you are absolutely correct , I have copied the model.mdf and modellog.ldf to some other location to create scenario as if the model data and log files have been deleted, but I have the backup of model database as well which I am trying to restore but it is giving me named pipe errors and when I copy paste the model data and log file to their original location ie datafolder I am not getting any errors when service starts up.
Does model database has anything to do with tcp/ip , name pipes ,...connection info ?
When I try to restore from backup sql server service is getting disconnected with named pipe error.
Please let me know if u have any resolving action for this issue.
Thanks
January 28, 2011 at 4:18 am
when sql server is in single user mode you can only restore the master database!
single user mode is initiated by the following commands
sqlservr -m
sqlservr -f -T3608
Copy the tempdb files to the correct location for the model (wherever you deleted them from) rename them as already stated and then start sql server service only from services.msc
Once you have done this you may restore the model database from sqlcmd or SSMS. As i said i have tried this and it works flawlessly.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 28, 2011 at 2:38 pm
abdul.samad.121 (1/28/2011)
yes you are absolutely correct , I have copied the model.mdf and modellog.ldf to some other location to create scenario as if the model data and log files have been deleted, but I have the backup of model database as well which I am trying to restore but it is giving me named pipe errors and when I copy paste the model data and log file to their original location ie datafolder I am not getting any errors when service starts up.Does model database has anything to do with tcp/ip , name pipes ,...connection info ?
When I try to restore from backup sql server service is getting disconnected with named pipe error.
Please let me know if u have any resolving action for this issue.
Thanks
As another member mentioned, you cannot use trace flag 3608 to restore the model database. This trace flag prevents sql server from recovering any database but the master database.
You can do the following.
1. Restore you model.bak file onto another sql server of the same version and patch level as model_backup.
(Note, you cannot directly restore over the model database or at least I couldn't in my testing.)
2. Detach the database and copy the mdf/ldf files to the server you are trying to recover to.
3. Ensure that the file names are model.mdf and modellog.ldf.
4. Restart sql server and you should have your database back.
Alternatively, you could have copy the tempdb mdf/ldf files as another poster had suggested as the model mdf/ldf files to bring the sql server back up and then follow pretty much the same steps as above. You would want to do this if you don't have another sql server readily available.
March 5, 2012 at 12:18 pm
Sorry to bring this thread back from the dead, but I thought I would offer my dreadful experience with this exact same issue.
The whole issue started because of SQL server backups...that's right, it shot itself in the foot. Not sure why, but performing a maintenance plan backup put the "model" database into some sort of forever "restore" mode. Upon restarting MS SQL server, nothing will boot. The tempdb requires model apparently, so SQL server will not boot until model is not in restore.
Unfortunately, none of this methods in this thread work. Restoring from backups would never work, because of the "HResult 0x6D, Level 16, State 1
Shared Memory Provider: The pipe has been ended." error. That is really the issue, I'm guessing this is some sort of SQL Server bug. Searching google for the error brings up more people with the model db restore issue but no solutions.
I also tried the tempdb copy and rename to model, that did not work either.
My ultimate solution was to restore to a previous VMware snapshot..thank god for those, otherwise we would have been screwed. Luckily I'm primarily an Oracle DB, only doing this SQL server crap because I had to, but what a nightmare..all because of trying to backup. :crazy:
March 5, 2012 at 3:03 pm
you would have needed to restore the master database and then the model
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 11, 2012 at 4:55 am
Helloooo every one :-),
Most of the time I prefer to use DOS prompt to implement my files, but while using it this time I have lost my files and couldn’t find any way to get those lost files back. How to recover files deleted using dos command prompt.
Regards
Glacy
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply