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 «««45678»»

Moving the SQL 2005 System Databases Expand / Collapse
Author
Message
Posted Friday, July 17, 2009 12:08 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
I have not tried this yet but i do have the same question about mssqlsystemresource.mdf
as well as a database called "distmdl.mdf" as i am not sure what that is

Thanks
Jim
Post #755057
Posted Friday, August 14, 2009 1:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 2:58 AM
Points: 3, Visits: 120
Wow, long time since anyone has posted anything here! But the script was awesome except for one little thing...line 84: exit 0. I tried to run the script on a brand new installation and for some reason I got an error message at some point. I was looking closely as the script was running but it all went by so fast so couldn't see exactly where it went wrong and when the script was finished the exit command closed my window so I was unable to figure out exactly what had went wrong.

Not having done this before it took me "some time" (not too long actually) to figure out what went wrong and it was the ALTER DATABASE mssqlsystemresource MODIFY FILE query that had gone wrong and I had to do it manually to get sql server up again.

In conclusion I'm really happy with the script, it made it really easy for me to move the system databases...I would only remove line 84 next time I run it :)
Post #770725
Posted Friday, August 14, 2009 1:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 2:58 AM
Points: 3, Visits: 120
Bummer...I just read page 1 :S
Post #770727
Posted Wednesday, October 28, 2009 11:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 28, 2009 11:03 AM
Points: 1, Visits: 1
It must be something embrassingly easy i'm getting wrong but I just can't get the damn script to run.

This is the command i'm using:

C:\movedb>MoveSql2005SysDbs MSSQLSERVER D:\SQL_Database >> whatshappening.txt

and this is the content of "whatshappening.txt":

"This command MUST be run from the SQL Server and the script file MoveSql2005SysDbs.sql
must be in the same directory as the MoveSql2005SysDbs.cmd file."

I am on the SQL server and the *.SQL file is in the same directory - what am i doing wrong?

Post #810148
Posted Wednesday, October 28, 2009 11:27 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: Tuesday, October 21, 2014 2:10 PM
Points: 716, Visits: 466
Well, re-reading the script it tries to get the path of the executing batch file using this line:

set ScriptPath="%~dp0"

%0 is the full path of the currently executing batch file, and using %~dp0 should get the drive and path of that file. Then the lines that are stopping you are run:

if not exist %ScriptPath%MoveSql2005SysDbs.sql (
echo This command MUST be run from the SQL Server and the script file MoveSql2005SysDbs.sql
echo must be in the same directory as the MoveSql2005SysDbs.cmd file.
exit 1
)

So you might print out the ScriptPath variable using

echo ScriptPath=%ScriptPath%

before the lines that are stopping you to see if you can determine what went wrong.




Post #810158
Posted Tuesday, December 22, 2009 1:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 9:07 AM
Points: 2, Visits: 17
Hi Vince,

Thanks for the script, but i was unaware that all other services needed to be stopped prior to execution and have run into a problem. I think that one or several of the Alter Database commands failed to execute. The script moved the mysqlsystemresource files to the new location (same as where the master files are located), but when i try to start sql server, it appears that sql server is still looking for the resource db in the default install location on the c drive. Is this a simple fix? If so, could you or anyone else give me a hand with this.

Here are the last few lines from the latest errorlog:
2009-12-22 14:32:17.81 spid7s Starting up database 'mssqlsystemresource'.
2009-12-22 14:32:17.81 spid7s Error: 17207, Severity: 16, State: 1.
2009-12-22 14:32:17.81 spid7s FCB::Open: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf'. Diagnose and correct the operating system error, and retry the operation. /* ***** THESE FILES WERE MOVED TO D:\SQLServer ***** */2009-12-22 14:32:17.81 spid7s Error: 17204, Severity: 16, State: 1.
2009-12-22 14:32:17.81 spid7s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf for file number 1. OS error: 2(The system cannot find the file specified.).
2009-12-22 14:32:17.81 spid7s Error: 5120, Severity: 16, State: 101.
2009-12-22 14:32:17.81 spid7s Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
2009-12-22 14:32:17.81 spid7s Error: 17207, Severity: 16, State: 1.
2009-12-22 14:32:17.81 spid7s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.
2009-12-22 14:32:17.81 spid7s File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mssqlsystemresource.ldf" may be incorrect.
2009-12-22 14:32:17.82 spid7s Error: 945, Severity: 14, State: 2.
2009-12-22 14:32:17.82 spid7s Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2009-12-22 14:32:17.84 spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.


TIA,
Tim
Post #838206
Posted Tuesday, December 22, 2009 1:45 PM
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: Tuesday, October 21, 2014 2:10 PM
Points: 716, Visits: 466
Check the startup parameters for the SQL Server service, particularly the -m and -l parameters. See if they reference C: or D:. Change to D: if C: and start up again.

The mssqlserverresource database is expected to be found where the master database lives. So make sure the -m parm points to where master really is, and that the resource database is there too.



Post #838222
Posted Tuesday, December 22, 2009 2:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 9:07 AM
Points: 2, Visits: 17
Here's what's listed as the startup parameters under the advanced tab in Configuration Manager:

-dD:\SQLServer\master.mdf;
-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;
-lD:\SQLServer\mastlog.ldf

I see no reference to -m, is this something that i can add?

Also, on that same tab, the Data Path, Dump Directory, and still point to the initial install directory on C

This is a brand new server that our network admin installed sql on prior to the D drive existing. Would it be easier to uninstall and re-install directly to the D drive? If so, will SQL install to a non-system drive?

Thx for your assistance!
Post #838256
Posted Wednesday, February 24, 2010 7:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 4, 2012 7:38 AM
Points: 8, Visits: 40
I have been searching for a way to move all database files from old drives to new drives. I found this:
http://msdn.microsoft.com/en-us/library/ms345408%28SQL.90%29.aspx
which looks like what this article uses to drive the script. I have followed the Microsoft page above BUT it does not talk about if the environment is a cluster! Since I can't seem to find how to move master within the same instance BUT in a cluster, I tried the web page above.
When I get to the point that I need to move the resource database I cannot log in. So I run this:
NET START MSSQLSERVER /f /T3608

Looks like Sql Server starts from the service, BUT I cannot log into sqlcmd after this:
D:\>D:\MSSQL\90\Tools\binn\sqlcmd.exe -sMSSQLSERVER
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Now, I know this web site instructions work on Sql Server 2005 NONCLUSTERED. I have done this testing 5 times now without issue, but we do not have a clustered test environment to test.
I need to be able to move the resource database on a clustered environment.
Anyone try this?

Our environment:
SQL SERVER 2005 Enterprise 64-Bit SP3
Windows 2003 R2 Enterprise 64-Bit SP2

Any help would be appreciated!
Post #871908
Posted Wednesday, February 24, 2010 9:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 1:12 AM
Points: 371, Visits: 716
webooth (7/22/2007)


This is a issue that has been submitted to Microsoft connect.

The documentation states the resource database and the master database must be in the same folder.

What it does not say is the resource database log file must be in the master database folder.

So if you put log files on one disk and database files on another disk the install will fail unless you put a copy of the resource database log where the master database data file is. After it is updated you can just copy it back to the log file location and all is well.

Well - it pays to read thru the previous pages.
I have kept the res. and dist. MDF & LDF in the same folder as the MASTER & TEMP again both mdf & ldf and never had any problem. Service packs do get into trouble if these are not like this. SP3 also gets into trouble if your registry does not point to the "new" location of FTData ...

Vince's script works fine if you remember to stop ALL SQL-related services before running. Not tried with SQL64 bit but it seems there will be some extra issues so beware.
Post #872456
« Prev Topic | Next Topic »

Add to briefcase «««45678»»

Permissions Expand / Collapse