Groups > microsoft.public.sqlserver.programming > #31298

Re: Master in single user mode

Newsgroups	microsoft.public.sqlserver.programming
Date	2016-06-29 09:13 -0700
References	(12 earlier) <DBA640EF-9EC7-44C7-A2ED-9416EA13C412@microsoft.com> <13AD8686-B4FE-4DA5-B732-8DBCA30FE027@microsoft.com> <0061612B-D977-4C80-8A37-53D1B8396E8B@microsoft.com> <fMmdnVx8cevTWUbQRVn_vwA@giganews.com> <19c1b6ae-c6c2-4e4b-ac5b-431c55df8866@googlegroups.com>
Message-ID	<71ac9ad1-a468-4792-a2cb-bc296a7f1dac@googlegroups.com> (permalink)
Subject	Re: Master in single user mode
From	stephane.heslouin@sealedair.com

Show all headers | View raw

humm  very interesting article and i tried this as a test :

-SQL 2008R2 DEV EDITION  

0/ Take fresh backup of master database on  SQL Instance SERVER1\DEV01

1/ Stop SQL Server on instance SERVER1\DEV01 

2/ Renamed the master DB files like following : -- Just in case  :-) ---
(master.mdf -> master_original.mdf) 
(mastlog.ldf -> mastlog_original.ldf) 

3/ Restore this backup to another SQL Instance SERVER2\TEST02 (server and  instance name completely different) however same SQL Edition/version and collation
NB - I restored it with name "master_SERVER1" and has been restored as a "normal" user database.

4/ Detach this "master_SERVER1" from SERVER2\TEST02

5/ Copy the master_SERVER1.mdf and master_SERVER1.ldf on SERVER1

6/ Renamed the files like following : 
(master_SERVER1.mdf -> master.mdf) 
(master_SERVER1.ldf -> mastlog.ldf) 

7/ Start SQL Server on SERVER1
--> Restart Successfull
BUT  master is in single user mode !! but does  not seem to impact the correct behaviour of the SQL instance

sp_helpdb 'master'   
-->  Shows master in MULTI_USER  mode

SELECT name, user_access_desc FROM sys.databases where name = 'master'
-->  Shows master in SINGLE_USER  mode

!! really weird !!!

Let's continue

8/ Take a backup of master database on SERVER1\DEV01

9/ Restore this backup with name "new_master" on SERVER1\DEV01 (so as a user database actually) on the directory where system databases reside

NB : restore is OK but the DB is in SINGLE_USER mode 
So i set it back to MULTI_USER mode
alter database new_master set MULTI_USER

10/ Stop SQL Server on SERVER1\DEV01

11/ Rename files like following : 
(new_master.mdf -> master.mdf) 
(new_master.ldf -> mastlog.ldf) 

12/ Stop SQL Server on SERVER1\DEV01
--> Successfull
master database is OK and no more on SINGLE_USER mode

YEAH !!!!!!!!

Back to microsoft.public.sqlserver.programming | Previous | Next | Find similar | Unroll thread

Thread

Re: Master in single user mode stephane.heslouin@sealedair.com - 2016-06-29 09:13 -0700

csiph-web