September 8, 2015 at 10:53 am
Hi!
I installed 2 instances of MS SQL (a Default Instance and a SharePoint instance) on a Windows 2012 Server at work.
When completed with the installations, I restored the databases in the Default Instance successfully.
I am having troubles with the SharePoint Instance. Here is the main issue I am having:
Before restoring the databases, I first am trying to restore the 'master' database. However, this step keeps on failing on me. I have GOOGLED for some instructions; however, none of these worked with me. I am sure that I am not doing something right.
Any suggestions are highly appreciated.
Thank you,
Ballaket
September 8, 2015 at 11:07 am
Is there a particular reason why you're restoring the master database? Wouldn't it be easier if you copied the noncontained objects (logins and the like) with scripts?
-- Gianluca Sartori
September 8, 2015 at 11:16 am
Hi!
First of all, I would like to thank you for taking time to read and reply to my post.
I am certain that your suggestion is worth trying. The problem is that when I tried that with the Default Instance it was not successful. I ended up trying other suggestions, from Google. These suggestions were saying to just restore 'master'.
Back to you and thank you.
Ballaket
September 8, 2015 at 12:17 pm
If I understand correctly, you are moving your default and sharepoint instances from a previous server.
I have no direct experience with moving sharepoint databases, but I suspect that restoring the master database is not what you want to do.
Sharepoint will need some objects stored in system databases (logins, jobs and many other things). If your previous installation is still available, you can script those either manually or with tools such as ScriptSqlConfig
-- Gianluca Sartori
September 8, 2015 at 12:40 pm
Actually, what happened is that we lost our server that was hosting the these instances of MS SQL, due to a patch upgrade that, I believe, came with virus. We have backups in our Data Domain. This is why I am trying to restore master.
I am not sure how else I can do this.
Thanks,
Ballaket
September 8, 2015 at 3:57 pm
Oh, I see. Bad day...
You should be fine restoring your master database. What error are you getting?
-- Gianluca Sartori
September 9, 2015 at 5:11 am
When you restore master you have to put SQL Server into Single user mode first as no other connections can be accessing SQL Server.
What error(s) are you getting?
September 9, 2015 at 6:30 am
According to my reading, I am supposed to enter '-m' as parameter in the Startup Parameters window under the SQL Server Properties and click Apply and Ok.
After that, I am supposed to turn off the other services under the SQL Server Configuration Manager.
Then I would launch the command prompt and enter the following:
C:\> sqlcmd
1> RESTORE DATABASE master FROM DISK = 'backup location' WITH REPLACE;
2> GO
Alter failed for Database 'master'.
An exception occured wihile executing a Transact-SQL statement or batch.
Option 'SINGLE_USER'cannot be set in database 'master'.
September 9, 2015 at 7:52 am
Thank you for your comments.
Do you know of a better way to put master in Single_User?
September 9, 2015 at 2:33 pm
Don't bother changing the parameters of the service, jut run it manually.
Stop the service, open the service properties and copy the executable location. Paste it to a new command prompt window. Now take the startup parameters and copy/paste them to the command window. Now add the -m"SQLCMD" switch to the command line and run it.
Open another command prompt window, run
sqlcmd -Sserver\instance -E
and perform the restore.
-- Gianluca Sartori
September 9, 2015 at 4:05 pm
Hi!
I followed the instructions you gave me (I hope I followed your instructions correctly) and here are the error messages;
Instruction (part 1):
Don't bother changing the parameters of the service, jut run it manually.
Stop the service, open the service properties and copy the executable location. Paste it to a new command prompt window. Now take the startup parameters and copy/paste them to the command window.
Result (from Cmd prompt):
Multiple instances of SQL server are installed on this computer. Renter the command, specifying the -s parameter with the name of the instance that you want to start.
2015-09-09 16:39:33:43 Server SQL Server shutdown has been initiated.
Instruction (part 2):
Now take the startup parameters and copy/paste them to the command window. Now add the -m"SQLCMD" switch to the command line and run it.
Open another command prompt window, run
sqlcmd -Sserver\instance -E
and perform the restore.
Result (in Cmd prompt window):
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0: A network-related or instance-specific error has occured while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
September 9, 2015 at 5:26 pm
BallaKet (9/9/2015)
Hi!I followed the instructions you gave me (I hope I followed your instructions correctly) and here are the error messages;
Instruction (part 1):
Don't bother changing the parameters of the service, jut run it manually.
Stop the service, open the service properties and copy the executable location. Paste it to a new command prompt window. Now take the startup parameters and copy/paste them to the command window.
Result (from Cmd prompt):
Multiple instances of SQL server are installed on this computer. Renter the command, specifying the -s parameter with the name of the instance that you want to start.
2015-09-09 16:39:33:43 Server SQL Server shutdown has been initiated.
That's because the service also has -sInstanceName parameter. It's not visible in configuration manager, but you can see it in services.msc.
Instruction (part 2):
Now take the startup parameters and copy/paste them to the command window. Now add the -m"SQLCMD" switch to the command line and run it.
Open another command prompt window, run
sqlcmd -Sserver\instance -E
and perform the restore.
Result (in Cmd prompt window):
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0: A network-related or instance-specific error has occured while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
That's because sqlserver failed to start in the previous step.
-- Gianluca Sartori
September 9, 2015 at 5:33 pm
Please pardon my lack of good understanding. Do you have any suggestions on how to do it without getting good these errors again?
My default instance is named as GMSTDB2.
My Share point instance is named as GMSTDB2\SHAREPOINT.
September 9, 2015 at 5:34 pm
Please pardon my lack of good understanding. Do you have any suggestions on how to do it without getting these errors again?
My default instance is named as GMSTDB2.
My Share point instance is named as GMSTDB2\SHAREPOINT.
September 10, 2015 at 6:58 am
Stop your SQL Services
open a cmd window and run the following
sqlservr.exe -m -s GMSTDB2
open a new cmd window:
SQLCMD -s GMSTDB2
GO
RESTORE DATABASE master FROM 'Location of backup' WITH REPLACE
GO
Once the restore is complete. Close the windows and start SQL Service from configuration manager.
Repeat the steps for your SharePoint instance
References:
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply