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

Re: Master in single user mode

X-Received	by 10.98.82.12 with SMTP id g12mr8178518pfb.14.1467216828973; Wed, 29 Jun 2016 09:13:48 -0700 (PDT)
X-Received	by 10.157.37.28 with SMTP id k28mr225482otb.18.1467216828842; Wed, 29 Jun 2016 09:13:48 -0700 (PDT)
Path	csiph.com!weretis.net!feeder6.news.weretis.net!news.glorb.com!jk6no6589508igb.0!news-out.google.com!o189ni10996ith.0!nntp.google.com!r1no6609106ige.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	microsoft.public.sqlserver.programming
Date	Wed, 29 Jun 2016 09:13:48 -0700 (PDT)
In-Reply-To	<19c1b6ae-c6c2-4e4b-ac5b-431c55df8866@googlegroups.com>
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=165.225.76.81; posting-account=itFk3goAAADhtISKstf4txdg9WNyps7U
NNTP-Posting-Host	165.225.76.81
References	<9B1A6DA9-8163-478D-AC8A-B52B0F9BE59B@microsoft.com> <pnm4d4tquj2qkpks0dq9ldjhkh7qftv0ff@4ax.com> <0617091A-6AF9-4AE7-B0DE-36A7507D22E5@microsoft.com> <fgt4d4p79qc73nq5216tocija7evkm2lcq@4ax.com> <D973CCB1-D488-4AE0-877F-5493C9D0DE68@microsoft.com> <kr25d4p61o1pu3o6m0udi9cjpnkr7vqjup@4ax.com> <1D564503-99B7-4972-848C-F3F5E79879E6@microsoft.com> <krb5d49d9r2sein3bkt448rpmcjfek5390@4ax.com> <CE91C5A0-4A4B-4179-ABF3-57568CCB99CD@microsoft.com> <4vf5d4da28ssr1dpvm6r7j8satq0tfb6i9@4ax.com> <#W#ooXiGJHA.3936@TK2MSFTNGP03.phx.gbl> <7F5B31D9-2BA3-460B-8080-B3DEFDF41740@microsoft.com> <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>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<71ac9ad1-a468-4792-a2cb-bc296a7f1dac@googlegroups.com> (permalink)
Subject	Re: Master in single user mode
From	stephane.heslouin@sealedair.com
Injection-Date	Wed, 29 Jun 2016 16:13:48 +0000
Content-Type	text/plain; charset=UTF-8
Xref	csiph.com microsoft.public.sqlserver.programming:31298

Show key headers only | 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