I don't think you can restore the Master after completing rebuildm.
Suggest you script any user objects, logins & pw in your master db, than
run rebuildm. You're going to have to de/reattach all user db. Try
this script.
CREATE PROCEDURE [dbo].[DetachAndReattach]
@oldloc nvarchar(100),
@newloc nvarchar(100),
@recovery nvarchar(20) = 'Simple',
@log tinyint = 1,
@data tinyint = 0
AS
/* This procedure generates scripts for detaching/reattaching dbs and
should be run before any files are moved.
Necessary perameters are the old location of the files (@oldloc) and the
new location (@newloc) */
SET NOCOUNT ON
DECLARE @cmdstr nvarchar(100)
DECLARE @strSQL nvarchar(250)
DECLARE @Filename1 varchar(100)
DECLARE @Filename2 varchar(100)
DECLARE @db varchar(50)
DECLARE db_curs cursor for
select [name] FROM sysdatabases
DECLARE db_curs2 cursor for
select [name] FROM sysdatabases
--Set @oldloc = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\'
--Set @newloc = 'D:\SQLServerLogFiles\Simple\'
--Set @recovery = 'Simple'
--Set @log = 1
--Set @data = 0
OPEN db_curs2
FETCH NEXT FROM db_curs2 INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM db_curs2 INTO @db
IF convert(nvarchar(20),DatabasePropertyEx(@db,'Recovery')) = @recovery
AND @db NOT IN ('master','msdb','tempdb','model')
BEGIN
PRINT 'EXEC sp_detach_db ''' + @db + ''''
END
END
DEALLOCATE db_curs2
PRINT '
--Script for reattaching dbs
'
CREATE TABLE #TempTable2
(
name_ nvarchar(200),
file_ nvarchar(200),
fileid_ int,
recovery_ nvarchar(20)
)
OPEN db_curs
FETCH NEXT FROM db_curs INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @strSQL = 'SELECT ''' + @db + ''', [filename], fileid, ''' +
convert(nvarchar(20),DatabasePropertyEx(@db,'Recovery')) + ''' FROM ' +
@db + '.dbo.sysfiles WHERE fileid = 1 or fileid = 2'
INSERT INTO #TempTable2 EXECUTE(@strSQL)
Set @filename1 = (SELECT file_ FROM #Temptable2 WHERE name_ = @db AND
fileid_ = 1) Set @filename2 = (SELECT file_ FROM #Temptable2 WHERE name_
= @db AND fileid_ = 2)
IF @data = 1 BEGIN Set @filename1 = REPLACE(@filename1, @oldloc,
@newloc) END
IF @log = 1 BEGIN Set @filename2 = REPLACE(@filename2, @oldloc, @newloc)
END
IF convert(nvarchar(20),DatabasePropertyEx(@db,'Recovery')) = @recovery
AND @db NOT IN ('master','msdb','tempdb','model')
BEGIN
PRINT 'EXEC sp_attach_db @dbname = N''' + @db + ''',
@filename1 = N''' + rtrim(@filename1) + ''',
@filename2 = N''' + rtrim(@filename2) + ''''
END
FETCH NEXT FROM db_curs INTO @db
END
SELECT * FROM #TempTable2
DROP TABLE #TempTable2
CLOSE db_curs
DEALLOCATE db_curs
SET NOCOUNT OFF
GO
-----Original Message-----
From: Mordechai Danielov
[mailto:mssqldba-ezmlmshield-x76268324.[Email address protected]
Sent: Tuesday, January 30, 2007 12:22 PM
To: LazyDBA Discussion
Subject: RE: changing tempdb collation
My users are having collation problems because all the databases are
have a
collation different from the tempdb. Since I can't just change collation
for
tempdb, I have to change server's collation. The only way to do that
seems
to be by running rebuildm. I want to minimize the post rebuild work, and
was
hoping that if I backup the master DB before the rebuild, and then
restore
it after the rebuild, I will avoid having to reattach DBs. I don't want
master and tempdb to have a different collation.
-----Original Message-----
From: Davis Ralph
[mailto:mssqldba-ezmlmshield-x49218337.[Email address protected]
Sent: Tuesday, January 30, 2007 12:44 PM
To: LazyDBA Discussion
Subject: RE: changing tempdb collation
DON'T KNOW. Every time I have tried a rebuild it was as if you
reinstalled SQL and started over.
Why are you doing this? There is a reason the master, msdn, and tempdb
are the same collation. If tempdb was different you would begin getting
collation conflicts on all comparisons using temptables etc. I've seen
this when loading a database of a different collation on a server.
Unless maybe you were using completely Unicode and then it would not
matter what collation tempdb was.
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Mordechai Danielov
[mailto:mssqldba-ezmlmshield-x81271848.[Email address protected]
Sent: Tuesday, January 30, 2007 11:34 AM
To: LazyDBA Discussion
Subject: RE: changing tempdb collation
Thanks,
I was afraid so. Is it possible to backup master db, run rebuildm to
change
its collation and then restore it (and MSDB I guess, because it also
gets
rebuilt, right?) back so that I don't have to reattach?
Mordechai
-----Original Message-----
From: Davis Ralph
[mailto:mssqldba-ezmlmshield-x60049643.[Email address protected]
Sent: Tuesday, January 30, 2007 12:15 PM
To: LazyDBA Discussion
Subject: RE: changing tempdb collation
I believe the server collation must change for any system databases to
change, and they all will be the same(master, msdb, tempdb, etc.)
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Mordechai Danielov
[mailto:mssqldba-ezmlmshield-x71819851.[Email address protected]
Sent: Tuesday, January 30, 2007 10:57 AM
To: LazyDBA Discussion
Subject: changing tempdb collation
Is there a a way to change tempdb collation, or do I have to change the
server collation for that?
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
****************************************************************
Confidentiality Note: The information contained in this
message, and any attachments, may contain confidential
and/or privileged material. It is intended solely for the
person(s) or entity to which it is addressed. Any review,
retransmission, dissemination, or taking of any action in
reliance upon this information by persons or entities other
than the intended recipient(s) is prohibited. If you received
this in error, please contact the sender and delete the
material from any computer.
****************************************************************
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
****************************************************************
Confidentiality Note: The information contained in this
message, and any attachments, may contain confidential
and/or privileged material. It is intended solely for the
person(s) or entity to which it is addressed. Any review,
retransmission, dissemination, or taking of any action in
reliance upon this information by persons or entities other
than the intended recipient(s) is prohibited. If you received
this in error, please contact the sender and delete the
material from any computer.
****************************************************************
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page