SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving System Databases


Moving System Databases

Author
Message
Joshua M Perry
Joshua M Perry
Right there with Babe
Right there with Babe (776 reputation)Right there with Babe (776 reputation)Right there with Babe (776 reputation)Right there with Babe (776 reputation)Right there with Babe (776 reputation)Right there with Babe (776 reputation)Right there with Babe (776 reputation)Right there with Babe (776 reputation)

Group: General Forum Members
Points: 776 Visits: 551
That has nothing to do with what appears to be a new question:

If you are moving system databases (master, msdb, model, tempdb), you would change the location of master by changing the startup parameters, bringing down the instance, and moving the master.mdf and master.ldf files.

In SQL Server 2005, how would you move the location of the msdb database?


You use the following procedure to move the location of the msdb database:

http://technet.microsoft.com/en-us/library/ms345408.aspx

You're also wrong about having to move mssqlsystemresource to the same location as master, even though that's what it says in books online it's not true. SQL Server 2005 runs just fine with those databases in different locations, even on different physical drives. I know this for sure because I currently run master mdf on the E drivem aster ldf on the F drive, and mssqlsystemresource is still on C. Moving mssqlsystemresource can actually cause SQL Server 2005 to not start.

Joshua Perry
http://www.greenarrow.net
Adam Bean
Adam Bean
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6188 Visits: 2222
Joshua Perry (3/21/2008)
That has nothing to do with what appears to be a new question:

If you are moving system databases (master, msdb, model, tempdb), you would change the location of master by changing the startup parameters, bringing down the instance, and moving the master.mdf and master.ldf files.

In SQL Server 2005, how would you move the location of the msdb database?


You use the following procedure to move the location of the msdb database:

http://technet.microsoft.com/en-us/library/ms345408.aspx


Ah, re-read the earlier posts ... as stated, the question didn't make any sense. We were referring to as what the next step would be.

---
SQLSlayer
Making SQL do what we want it to do.
Adam Bean
Adam Bean
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6188 Visits: 2222
Joshua Perry (3/21/2008)

Also, setting trace flag 3608 at startup and running in single user mode are the same thing, so two of the answers are identical.


Not entirely true. Trace flag 3608 prevents SQL Server from recovering any database except the master database. Single user mode is exactly that, only one connection allowed.

---
SQLSlayer
Making SQL do what we want it to do.
DavidSimpson
DavidSimpson
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1512 Visits: 1087
The question of the day has changed. When I answered it the question stated

"If you are moving system databases (master, msdb, model, tempdb), you would change the location of master by changing the startup parameters, bringing down the instance, and moving the master.mdf and master.ldf files.
Of the other three - which do you need to address second?"

Basically asking what sequence of databases to move... which does not matter. The answer then went on to state that MODEL should be moved next.

Now the question is asking about how to move the MSDB database... which Joshua you are correct.

Also, did not know that mssqlsystemresource could be in different location as master.. thanks for the tip. I may have to try this out when I get some time.

So Steve, do I get a retry on the QOTD since it's been changed? BigGrin

David



Adam Bean
Adam Bean
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6188 Visits: 2222
The question of the day has changed.


Ok, I thought I was going crazy ... I thought that is what the QotD said when I answered it as well ... thanks for clearing that up.

Also, did not know that mssqlsystemresource could be in different location as master.. thanks for the tip. I may have to try this out when I get some time.


The mssqlsystemresource db has to be in the same location as master ... I remember last time I moved the master db and didn't know about moving mssqlsystemresource, yeah ... SQL wasn't to happy about it.

---
SQLSlayer
Making SQL do what we want it to do.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18187 Visits: 12426
Steve Jones - Editor (3/21/2008)
I'll reword and change it around.


Hi Steve,

I hope everything went allright with the kid's tooth Smile

I have to agree with Joshua Perry on the new version of the question. All answer possibilities involve detaching and attaching - and whereas it IS possible to move a system database in that way, and I did indeed post a link to an MSKB article describing some gotchas with that approach, there is a far easier way documented in Books Online:

1) ALTER DATABASE to modify the files for msdb;
2) Shutdown the server;
3) Physically move the database files;
4) Restart the server;
5) Check that all has gone right.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
BarbW
BarbW
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3329 Visits: 1130
In addition to the question changing, it seems one of the answers (C) was correct for SQL 7.0 (as described in 224071 - Moving the MSDB database - SQL Server 7.0) and the version of SQL was not specified in the question.

Just adding my 2 cents to get my point back Wink
Jerry Hung
Jerry Hung
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3286 Visits: 1208
Not that I care, but it is one confusing question & answer
I have actually done this, and this is my SQL script for all-system-DB's-except-master and they did work, no trace flags used


--http://msdn2.microsoft.com/en-us/library/ms345408.aspx

-- STEP 1 Run and STOP SQL Server
-- model
ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = 'X:\SQLDATA\model.mdf')
ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = 'Y:\SQLLOGS\modellog.ldf')

-- msdb
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData, FILENAME = 'X:\SQLDATA\msdbdata.mdf')
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog, FILENAME = 'Y:\SQLLOGS\msdblog.ldf')

--tempdb
--USE master
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'X:\SQLDATA\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'Y:\SQLLOGS\templog.ldf')

-- STEP 2 COPY physical files

-- STEP 3 Start SQL Server


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3796 Visits: 857
Just curious, does the script assume that you have already made a copy of the original on X to drive Y? I'm guessing that the server has to be stopped for that to happen. Is there a way to move them without stopping the production server - on the fly so to speak?

Jamie
mojo-168709
mojo-168709
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1107 Visits: 831
The question was specifically for SQL 2005. So no -T3608 is required.

If you move a SQL 2000 model and msdb, the flag is required. And, if you move these two dbs together, there is a sequence issue when re-attaching: you have to attach model first.

As for moving mssqlsystemresource files to a folder different from where master db files reside, there was a bug in the RTM -- you wouldn't be able to restart SQL after applying SP1. Microsoft says it was fixed in SP2. Can any one confirm it was in fact fixed?[url=http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126672][/url]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search