April 11, 2007 at 10:32 am
Okay having just run SP2 again on a clean SQL 2005 installation with the mdf's & ldf's moved to their respective homes. When SP2 was upgrading the 1st named instance I saw several new files get created in the 'original' data folder.
These were distmdl.mdf & .ldf (I delete these as part of your script as they are not needed) & mssqlsystemresource1.ldf further into the upgrade process the file gets renamed to mssqlsystemresource.ldf. Shortly after this I see the hotfix.exe error box appear.
Does this mean I just need to change in the script when the service pack runs and move the resource db after the sp upgrade? But surely I will then have the same problem when another sp get's released?
help...
April 11, 2007 at 10:47 am
Mark,
PM me at vince.iacoboni location db.com (replace location with the symbol that is above 2 on the keyboard ). I will include my latest script that checks and sets the registry for SQL 2005 instances. It might help you see if you have registry entries pointing to the original file locations that SP2 is reading.
Vince
April 12, 2007 at 2:28 am
Thanks for the reply Vince. I have sent you a pm hopefully this will help pinpoint the problem.
Have you successfully used your script to split the locations of the system db's & mssqlsystemresource.mdf & .ldf files and then tried to install SP2? Was everything ok?
Thanks again for all your help.
May 29, 2007 at 3:43 am
I should point something out that has caused me grief.
Doing a test run on my desktop worked like a charm, but when it came time to run it on our production box, I discovered that x64 machines (ha ha!) have TWO copies of CMD.exe - one that recognises the 'NET' command and one that doesn't. As a result, the script half executed and gave me a major problem.
Eventually I got everything back up, but am now faced with:
1) I cannot "ALTER DATABASE mssqlsystemresource" because that database "does not exist"; and
2) my master and mssqlsystemresource files are in different directories (which should not be the case.)
So, at the moment everything "works", but I'm just dreading the issues further down the track.
I would like some advise on how to fix the above two problems, if anyone can help.
Thanks,
S.
July 22, 2007 at 8:50 am
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.
July 22, 2007 at 9:00 am
Great article. This is a tedious chore and any help is appreciated.
Two minor items.
1. A commented sample in the code showing how to run the procedure. I noticed someone used quotes.
2. The ability to put log files and data files on different paths. Some of us separate the log and data files and put them on different drives.
September 28, 2007 at 7:31 am
Hi vince,
I think this is a great script. I wish I had seen it when is was first posted 🙂
I wonder if you have updated or modified it in the past year? One suggestion I have is to provide for a separate T-Log file path. I guess the only requirement would be to add an additional command line parameter and a new path variable.
I don't do a lot of admin but I do a lot of build/deploy/install and these techniques will be very useful to me.
Ray
September 28, 2007 at 8:08 am
Great suggestions. If I get time to make the update I'll try to let you know. Work's picked up so not sure if/when that will happen...
Vince
September 28, 2007 at 8:09 am
Hi Ray, as you can see it is much requested. If I get the time I'll let post an update...
Vince
September 28, 2007 at 9:34 am
Interesting article, Vince. I was thinking that if one did not have a test/development machine available, you could always look at installing a test instance on one of your main boxes and working from there, assuming higher ups would let you get away with it. 😛
I definitely appreciate your talking about that system database. We will be bringing up 2K5 in the near future, we're exclusively a 2000 shop right now, and I've never heard of that database before. I think you've just saved me a world of grief!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
September 28, 2007 at 10:48 am
Here is something else about that system database. It has to be located in the same folder as the Master database.
We like to have the database and log files on different drives.
When we tried to install a SP for the first time we could not start the server. It had tried to update the resource database log file on the same drive the master database data file was. We just copied the log over to the other drive and things ran fine.
September 28, 2007 at 11:01 am
Hi Vince,
Wow, nice script. I recently had a hosting company setup a VPS with SQL Server 2005. In there email to me they strongly recommend putting the data files and log files on a separate drive than the C drive, however, they didn't themselves setup the system databases this way. Very frustrating. Anyway, I am looking to use your wonderful script and would like to second the request for the ability to specify a separate location for the ldf files. I thank you so much, in advance for anything you can do.
Take care,
Tom
September 28, 2007 at 12:50 pm
OK, you guys got your wish. Please note that this is untested. I hope someone will test it and post the results! [/i]
Here is the MoveSql2005SysDbs.CMD file:
*****************************************
[font=Courier New]@echo off
rem MoveSql2005SysDbs.cmd - Execute commands to move system databases
if "%2"=="" goto syntax
set InstName=%1
set NewPath=%2
if !%3 equ ! (
set NewLogPath=%2
) else (
set NewLogPath=%3
)
set ScriptPath="%~dp0"
if /I %InstName% equ MSSQLSERVER (
set ServiceName=MSSQLSERVER
set AgentServiceName=SQLSERVERAGENT
set SQLName=.
) else (
set ServiceName=MSSQL$%InstName%
set AgentServiceName=SQLAgent$%InstName%
set SQLName=.\%InstName%
)
rem Ensure we can find our SQL script file
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
)
rem Check viability of parameters before any changes happen...
sqlcmd -E -S%SQLName% -Q"print 'Instance name $(InstName) verified.'"
if errorlevel 1 (
echo Sql Server %SQLName% not found, please check instance name parameter.
exit 1
)
dir %NewPath% > nul
if errorlevel 1 (
echo New Path %NewPath% not found, please check path parameter.
exit 1
)
dir %NewLogPath% > nul
if errorlevel 1 (
echo New Log Path %NewLogPath% not found, please check logpath parameter.
exit 1
)
rem Now start modifying the system database locations
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILENAME = '%NewPath%\model.mdf')"
if errorlevel 1 goto AlterErr
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE model MODIFY FILE (NAME = 'modellog', FILENAME = '%NewLogPath%\modellog.ldf')"
if errorlevel 1 goto AlterErr
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBData', FILENAME = '%NewPath%\MSDBData.mdf')"
if errorlevel 1 goto AlterErr
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBLog', FILENAME = '%NewLogPath%\MSDBLog.ldf')"
if errorlevel 1 goto AlterErr
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME = '%NewPath%\tempdb.mdf')"
if errorlevel 1 goto AlterErr
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = '%NewLogPath%\templog.ldf')"
if errorlevel 1 goto AlterErr
rem This script file finds the correct spot in the registry for the startup parameters, replaces the path, and returns
rem the old path name back to the calling script.
for /f "delims=;" %%s in ('sqlcmd -E -S%SQLName% -h-1 -i%ScriptPath%MoveSql2005SysDbs.sql') do set OldPath="%%s"
echo Shutting down SQL Server. Answer Y if prompted to shut down dependent services.
net stop %AgentServiceName%
net stop %ServiceName%
echo Moving files...
move %OldPath%\model.mdf %NewPath%
move %OldPath%\modellog.ldf %NewLogPath%
move %OldPath%\MSDBData.mdf %NewPath%
move %OldPath%\MSDBLog.ldf %NewLogPath%
move %OldPath%\tempdb.mdf %NewPath%
move %OldPath%\templog.ldf %NewLogPath%
move %OldPath%\master.mdf %NewPath%
move %OldPath%\mastlog.ldf %NewLogPath%
echo Restarting service with /f and trace flag 3608
net start %ServiceName% /f /T3608
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = 'data', FILENAME = '%NewPath%\mssqlsystemresource.mdf')"
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = 'log', FILENAME = '%NewPath%\mssqlsystemresource.ldf')"
move %OldPath%\mssqlsystemresource.mdf %NewPath%
move %OldPath%\mssqlsystemresource.ldf %NewPath%
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE mssqlsystemresource SET READ_ONLY"
net stop %ServiceName%
echo Restarting service %ServiceName% in normal mode
net start %ServiceName%
net start %AgentServiceName%
echo Verifying new location of system databases...
sqlcmd -E -S%SQLName% -Q"SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files ORDER BY database_id;"
goto end
:AlterErr
echo Error in ALTER DATABASE statement, files not moved.
set errorlevel=2
goto end
:syntax
echo MoveSql2005SysDbs - Moves sql system databases to a different directory
echo MoveSql2005SysDbs [InstanceName] [NewPath][NewLogPath]
echo InstanceName should be MSSQLSERVER if using the default instance
echo NewPath should contain a full path but no trailing backslash
echo NewLogPath defaults to NewPath if not present
:end
[font=Courier New][/font][/font]
*****************************************
Here is the MoveSql2005SysDbs.sql file:
*****************************************
[font=Courier New]
SET NOCOUNT ON
DECLARE @MssqlDotNum varchar(100), @key varchar(255), @ArgName varchar(255), @arg varchar(255), @i int, @OldPath varchar(255)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', '$(InstName)', @MssqlDotNum output
SELECT @i = 0
SELECT @key = 'Software\Microsoft\Microsoft SQL Server\' + @MssqlDotNum + '\MSSQLSERVER\Parameters',
@ArgName = 'SQLArg' + convert(char(1), @i)
WHILE @i <= 2
BEGIN
SELECT @key = 'Software\Microsoft\Microsoft SQL Server\' + @MssqlDotNum + '\MSSQLSERVER\Parameters',
@ArgName = 'SQLArg' + convert(char(1), @i)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @key, @ArgName, @arg output
IF left(@Arg,2) = '-d'
BEGIN
select @OldPath = substring(@Arg, 3, charindex('\master.mdf', @arg) - 3)
select @arg = replace(@Arg, @OldPath, '$(NewPath)')
END
ELSE IF left(@Arg,2) = '-l'
BEGIN
select @OldPath = substring(@Arg, 3, charindex('\mastlog.ldf', @arg) - 3)
select @arg = replace(@Arg, @OldPath, '$(NewLogPath)')
END
IF left(@Arg,2) IN ('-d','-l')
exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', @key, @ArgName, 'REG_SZ', @arg
SELECT @i = @i + 1
END
-- Return the path to the calling .CMD file so it knows the old path. ; is the delimiter
select OldPath = rtrim(@OldPath) + ';'
[/font]
*****************************************
Tom Krueger (9/28/2007)
Wow, nice script. I recently had a hosting company setup a VPS with SQL Server 2005. In there email to me they strongly recommend putting the data files and log files on a separate drive than the C drive, however, they didn't themselves setup the system databases this way. Very frustrating. Anyway, I am looking to use your wonderful script and would like to second the request for the ability to specify a separate location for the ldf files. I thank you so much, in advance for anything you can do.
Take care,
Tom
October 2, 2007 at 10:37 am
Vince,
Thanks a lot for the script using the two different file locations. It works well!
The only thing I noticed is that the "ldf" for the mssqlsystemresource is moved to the same path as the "mdf". In the command file, the "ldf" should probably be set to %NewLogPath% instead of %NewPath%.
Thanks again! This saved a lot of time.
Mike
October 2, 2007 at 11:01 am
Michael,
Thanks, but I did that with the mssqlsystemresource ldf file intensionally. I believe MS expects both the MDF and LDF files for the Resource database to be in the same directory as the master MDF file. I haven't tested that though. I thought it was better to err on the side of caution by placing it there.
Did I understand you correctly that you tested out the new version and it worked OK?
Vince
Viewing 15 posts - 16 through 30 (of 75 total)
You must be logged in to reply to this topic. Login to reply