Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » How to Restore model database from backup ?
Add to briefcase
14 posts, Page 1 of 2
12»»

How to Restore model database from backup ? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
abdul.samad.121
Posted Thursday, January 27, 2011 7:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:13 AM
Points: 19, Visits: 107
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
Post #1054581
Perry Whittle
Posted Thursday, January 27, 2011 11:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 5,201, Visits: 11,149
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"
Post #1054792
abdul.samad.121
Posted Thursday, January 27, 2011 9:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:13 AM
Points: 19, Visits: 107
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

Post #1055068
Perry Whittle
Posted Friday, January 28, 2011 12:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 5,201, Visits: 11,149
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"
Post #1055122
abdul.samad.121
Posted Friday, January 28, 2011 2:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:13 AM
Points: 19, Visits: 107
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
Post #1055159
Perry Whittle
Posted Friday, January 28, 2011 3:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 5,201, Visits: 11,149
\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"
Post #1055191
abdul.samad.121
Posted Friday, January 28, 2011 3:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:13 AM
Points: 19, Visits: 107
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
Post #1055194
Perry Whittle
Posted Friday, January 28, 2011 3:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 5,201, Visits: 11,149
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"
Post #1055200
abdul.samad.121
Posted Friday, January 28, 2011 4:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:13 AM
Points: 19, Visits: 107
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
Post #1055205
Perry Whittle
Posted Friday, January 28, 2011 4:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 5,201, Visits: 11,149
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"
Post #1055210
« Prev Topic | Next Topic »

Add to briefcase
14 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.