Restoring master database
-
I need to restore a master database from another box to a new SQL Server installation. When I attempt to do so, I get a message that the database must be in single user mode. When I try to set it to single user mode in Query Analyzer, I get the error:
"Option 'SINGLE_USER' cannot be set in database 'MASTER'.
sp_dboption command failed."
When I try it in Ent. Mgr, the Single user option under Properties/Options for the master database is greyed out. This is a brand new installation. This is SQL Server 2000, SP3, patchlevel 8.00.818. Any ideas would be greatly appreciated! Cheers!
-
You must start SQL Server in single user mode to restore master by using startup parameter '-m'
Steve
-
Something to think about.. Are you sure you want to restore master on another server? What would this give you? You probably want to restore/copy your databases. You MAY want to restore msdb (alternatively it may be easier to just copy/recreate the jobs you created.) but master.... I'm not sure its a good idea. Be sure you know why you want to do this.
Francis
-
I'm glad you asked that, in case I'm on the wrong path here. I need to retain all of the logins & passwords from the old server. I thought I would import sysxlogins, but I was unsuccessful. I run up against this every time I move & reattach datafiles to a new box: users are in databases, but logins are not in master db. Thanks.
-
I would recommend against trying to restore master for that purpose. If the logins are all you want check out the following KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133&Product=sql2k
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Here's a method I use when I want to copy userid and passwords from one server to another. You may need to resync Databse users after but this is fairly easy:
-- Setup a linked server called impserver from which the
-- standard logins needs to be transferred. You can call it
-- whatever you want & modify the linked server name also.
declare @login sysname , @password sysname
declare implogins cursor for
select name , password
from [impserver].master.dbo.syslogins
where isntname = 0 and charindex( 'repl_' , name ) = 0 and
charindex( 'distributor' , name ) = 0 and name != 'sa'
open implogins
while ( 'FETCH IS OK' = 'FETCH IS OK' )
begin
fetch implogins into @login , @password
if @@fetch_status < 0 break
exec sp_addlogin @login , @password , @encryptopt = 'skip_encryption'
end
deallocate implogins
go
Francis
-
Thanks to everyone for your outstanding advice! You saved my butt this week. THANK GOD I'm a better Oracle DBA!
-
As a recovering Oracle DBA myself, I can sympathize. The differences between Oracle and SQL Server can be really bewildering at first.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Hi! I also have the same problem. The difference is that I'm trying to restore Master (MSDB and Model as well) using SMO. Im trying to create an automated backup and restore using SMO. But everytime i come to the Master DB, I get an error. I already stopped the service and added the -m parameter to start SQL server in single user mode... but when I try to set the master db to single user using the SMO command:
dbsys = svr.Databases['master'];
dbsys.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
dbsys.DatabaseOptions.Alter();
I get the error message : alter failed for DatabaseOptions 'Microsoft.SqlServer.Management.Smo.ObjectKeyBase'.
Im not sure if im in the right thread but I hope somebody would be able to help me... Thanks very much in advance!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply