Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: Master in single user mode

Back to article view | Back to microsoft.public.sqlserver.programming

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

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

#31298 — Re: Master in single user mode

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 !!!!!!!!

[toc] | [standalone]

Back to top | Article view | microsoft.public.sqlserver.programming

csiph-web