July 17, 2009 at 12:08 pm
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
August 14, 2009 at 1:36 am
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 🙂
August 14, 2009 at 1:37 am
Bummer...I just read page 1 :S
October 28, 2009 at 11:03 am
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?
October 28, 2009 at 11:27 am
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.
December 22, 2009 at 1:26 pm
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
December 22, 2009 at 1:45 pm
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.
December 22, 2009 at 2:16 pm
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!
February 24, 2010 at 7:27 am
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!
February 24, 2010 at 9:10 pm
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.
February 25, 2010 at 6:01 am
I saw this article thinking that it would help or have a hint on how to get my situation to work. I commented here, because this script would not work for me if I used it. I understand what needs to take place with the resource database for 2005. Whether or not Vince's script works was never my question.
I again have gotten the web page to work which this script is based on if you look at it! Now, if you look at the document on web page:
http://msdn.microsoft.com/en-us/library/ms345408%28SQL.90%29.aspx
ALL, steps work for ALL databases, at least for me, when you are performing this on a NON-clustered Sql Server 2005 server. The web page nor this chain never mention any difference for whether or not this can be performed on a cluster.
Now, for me, this step fails when performed on a Sql Server 2005 64-bit Enterprise Edition SP3 Cluster:
...
7.) 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.
NET START MSSQLSERVER /f /T3608
8.) 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.
I use this statement for step 8:
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.
I have started a chain in the administration forum. I post it as a question more than a comment. If you have a solution to how to get this to work on a cluster, then please comment there. To this point no one has commented to that post.
Thanks
February 25, 2010 at 6:36 am
Mike,
Sorry, never have had occasion to work with a clustered SQL Server, so I'm not one who can help.
Vince
March 22, 2011 at 5:27 pm
Excellent script! Worked great but I had to execute it twice. One important tip: insert the "pause" DOS command before any "exit" commands, otherwise your command window will close before you get a chance to read enough of the output. I had opened a cmd window and then executed it inside of that, thinking that when it finished it woud leave me in the original cmd window, but unfortunately it closed that as well when it exited and I had nothing to look at.
Before the DOS window closed, I also got an error that my account couldn't access the database while in single user mode. Then I realized what else was accessing the server: don't forget to close SQL Management Studio if you have it open, lol.
I commented everything out that didn't refer to the mssqlsystemresource database and ran it again (after closing SQL Mgmt Studio) and it completed successfully.
Thanks again for a wonderful script! But please resubit a version that doesn't close the window when it exits (or at least inseart the pause commands).
Steve
September 2, 2011 at 7:32 am
I thought I saw a post stating that someone had issues on a 64-bit system, but I don't recall seeing a post addressing it. Has anyone tried the script on a 64-bit system? Did you have any problems?
Thanks,
Bob
September 18, 2012 at 7:30 am
Hopefully someone still watches this. I ran this and everything looked great and moved as expected but the services did not start. When you attempt to start the services for this instance, I get the following in the logs:
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\mssql\MSSQL$WO\MSSQL.7\MSSQL\DATA\mssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.
That path is the old path and mssqlsystemresource.ldf and .mdf files are both in the new path as expected. Why is it still looking for it in the old path? I need to get his instance back up and running and don't know what I can do now.
Thanks.
Viewing 15 posts - 61 through 75 (of 75 total)
You must be logged in to reply to this topic. Login to reply