Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Moving System Databases Expand / Collapse
Author
Message
Posted Friday, March 21, 2008 2:22 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, February 6, 2014 9:39 AM
Points: 420, Visits: 487
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.usesage.com
Post #473101
Posted Friday, March 21, 2008 2:24 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:53 AM
Points: 1,145, Visits: 1,932
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.

Post #473103
Posted Friday, March 21, 2008 2:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:53 AM
Points: 1,145, Visits: 1,932
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.

Post #473106
Posted Friday, March 21, 2008 2:31 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:39 AM
Points: 916, Visits: 991
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? :D

David



Post #473107
Posted Friday, March 21, 2008 2:32 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:53 AM
Points: 1,145, Visits: 1,932
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.

Post #473108
Posted Friday, March 21, 2008 2:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:40 AM
Points: 5,916, Visits: 8,168
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 :)

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
Post #473116
Posted Saturday, March 22, 2008 5:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:03 AM
Points: 2,491, Visits: 1,026
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 ;)
Post #473199
Posted Monday, March 24, 2008 9:11 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 772, Visits: 1,183
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
Post #473515
Posted Monday, March 24, 2008 9:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
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
Post #473523
Posted Monday, March 24, 2008 10:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 10:46 AM
Points: 911, 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]
Post #473586
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse