September 9, 2010 at 5:24 am
As for the resource database in SQL2008. Wouldnt you be able to reinstall SQL on a different drive and then copy the old to the new location.
http://msdn.microsoft.com/en-us/library/ms190940.aspx
Im guessing "<drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\." is the folder where SQL is installed to (and not some hardcoded resourcedb location).
It also says that you cant backup the resourcedatabase. But that you can copy it and "restore" it as a file.
So that would be like a limited "move" of the resourcedatabase. You cant put it into a folder of your choice. But atleast you can chose which drive you want it on.
September 9, 2010 at 5:42 am
Hmm seems like we have a disagreement on moving the Resource DB.
Now which reference is Correct can it be moved or not? ? ?
http://technet.microsoft.com/en-us/library/ms345408(SQL.90).aspx
From the above reference (Bold emphasis added by this poster):
Moving the master and Resource Databases
The Resource database depends on the location of the master database. The Resource data and log files must reside together and must be in the same location as the master data file (master.mdf). Therefore, if you move the master database, you must also move the Resource database to the same location as the master data file. Do not put the Resource database in either compressed or encrypted NTFS file system folders. Doing so will hinder performance and prevent upgrades.
To move the master and Resource databases, follow these steps.
From the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
In the SQL Server 2005 Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.
In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.
Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.
The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.
Copy Code
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldfIf the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:
Copy Code
-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf
Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.
Move the master.mdf and mastlog.ldf files to the new location.
Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.
For the default (MSSQLSERVER) instance, run the following command.
Copy Code
NET START MSSQLSERVER /f /T3608
For a named instance, run the following command.
Copy Code
NET START MSSQL$instancename /f /T3608
For more information, see How to: Start an Instance of SQL Server (net Commands).
Using sqlcmd commands or SQL Server Management Studio, run the following statements. Change the FILENAME path to match the new location of the master data file. Do not change the name of the database or the file names.
Copy Code
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');
GO
Move the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the new location.
Set the Resource database to read-only by running the following statement.
Copy Code
September 9, 2010 at 5:50 am
Sorry all, I should have stated the version. As far as the reference in SQL 2008 this is where the reference is
http://msdn.microsoft.com/en-us/library/ms345408.aspx 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
September 9, 2010 at 5:52 am
bitbucket-25253 (9/9/2010)
Hmm seems like we have a disagreement on moving the Resource DB.Now which reference is Correct can it be moved or not? ? ?
http://technet.microsoft.com/en-us/library/ms345408(SQL.90).aspx
From the above reference (Bold emphasis added by this poster):
(snip)
Ron, this reference is information for SQL Server 2005. That can be seen by the "(SQL.90)" in the link, or by the "This page is specific to SQL Server 2005" box at the top of the page.
The corresponding page for SQL Server 2008 is at http://msdn.microsoft.com/en-us/library/ms345408.aspx. Here, the text reads:
Moving the Resource Database
The location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\. The database cannot be moved.
September 9, 2010 at 6:01 am
I had to do a little reading for this one. Thanks.
September 9, 2010 at 6:04 am
I also got this wrong by selecting none, but only because I am using SQL Server 2005 and was aware that the resource database location was/is dependent on the location of the master database. Since others have mentioned the change in 2008 I accept that I was wrong for the latest release of SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 9, 2010 at 7:05 am
Yes, me too worng....
The correct answer is none. As though resource database is readonly we can move it along with master.
http://msdn.microsoft.com/en-us/library/ms345408.aspx
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
September 9, 2010 at 7:49 am
free_mascot (9/9/2010)
The correct answer is none. As though resource database is readonly we can move it along with master.
http://msdn.microsoft.com/en-us/library/ms345408.aspx%5B/quote%5D
In SQL Server 2008, the Resource database cannot be moved, Your provided link clearly states that.:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
September 9, 2010 at 8:28 am
TravisDBA (9/9/2010)
free_mascot (9/9/2010)
Yes, me too worng....The correct answer is none. As though resource database is readonly we can move it along with master.
http://msdn.microsoft.com/en-us/library/ms345408.aspx%5B/quote%5D
In SQL Server 2008, the Resource database cannot be moved, Your provided link clearly states that.:-D
Unfortunately, your question didn't 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 9, 2010 at 8:33 am
Funny, I initially chose the Resource database cannot be moved, but then after checking my local BOL I changed to all can be moved. If you check the online BOL it shows it cannot be moved in SQL 2008 and 2008 R2. Although, I wonder if it really can be moved in 2008 (non R2) considering the inconsistent documentation.
From my local BOL ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/72bb62ee-9602-4f71-be51-c466c1670878.htm
Moving the Resource Database
In SQL Server 2008, the default location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\Binn\. The database can be moved; however, we recommend against moving it for two reasons:
Applying SQL Server service packs and hotfixes restores the database to the \Binn location.
Moving the Resource database in a failover cluster environment to a nonclustered location will cause failover cluster failure.
To move the Resource database, follow these steps.
September 9, 2010 at 8:39 am
Thanks for the Question. I was debating internally whether it was 2005 or 2008. I decided to go with 2008.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 9, 2010 at 8:55 am
da-zero (9/9/2010)
TravisDBA (9/9/2010)
free_mascot (9/9/2010)
Yes, me too worng....The correct answer is none. As though resource database is readonly we can move it along with master.
http://msdn.microsoft.com/en-us/library/ms345408.aspx%5B/quote%5D
In SQL Server 2008, the Resource database cannot be moved, Your provided link clearly states that.:-D
Unfortunately, your question didn't 😉
Reading is fundamental. I already stated in a previous post in this thread that I had omitted the version, and that was my bad, but the answer is correct for SQL 2008. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
September 9, 2010 at 9:19 am
I have corrected the question to say that it is for SQL Server 2008 R2 (using the latest reference), noted that it can be moved in 2005, and then awarded back points for everyone.
September 9, 2010 at 10:26 am
Steve Jones - Editor (9/9/2010)
I have corrected the question to say that it is for SQL Server 2008 R2 (using the latest reference), noted that it can be moved in 2005, and then awarded back points for everyone.
How kind. 😎
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 9, 2010 at 2:00 pm
Thanks Steve. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply