October 2, 2007 at 12:04 pm
Vince,
Yes, I tested out the new script that you posted, the one that copies the data files to one location and the log files to another, and it worked fine for me.
Thanks!
Mike
October 3, 2007 at 2:32 am
I agree with Vince, when I first attempted to split the mssqlsystemresource.ldf to a different drive I experienced all sorts of problems (including problems applying hotfix KB934458). Save yourself a lot of grief by keeping the resource files in the same location as the master db.
imho.
Cheers,
Mark
October 3, 2007 at 9:57 am
How do you call the CMD file? Do you use quotes or delimiters?
MoveSql2005SysDbs.CMD "MSSQLSERVER", "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\D1", "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\D2"
October 3, 2007 at 10:16 am
Quotes are only needed when a parameter contains a space. No delimiter is needed. That is standard command line parameter passing. So in your example if you leave off the commas you'd be OK.
webooth (10/3/2007)
How do you call the CMD file? Do you use quotes or delimiters?MoveSql2005SysDbs.CMD "MSSQLSERVER", "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\D1", "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\D2"
October 3, 2007 at 12:45 pm
Thank you.
October 24, 2007 at 6:26 am
I did a fresh install on w2k3r2 with all options selected (analysis server, ssis,...etc) and then ran this script and my sql server would not restart...
error 3417 could not start sql server...
had to reload os..:(
???
October 24, 2007 at 6:57 am
Brandt,
Ouch, sorry to hear that! If I had to guess, I'd say it might be due to other services like the Full-Text search and possibly analysis services not shutting down when the script tries to take the SQL server service down.
I'm sure you don't want to go through that experience again, but if you want to run the script again first shut down all SQL services except the actual server. Also, if you want to capture the output to a file then it might help me to debug.
Vince
October 25, 2007 at 12:12 pm
I'm missing something here. Here's the output. Can you point out what I'm missing?
C:\Documents and Settings\Administrator\Desktop\movedb>MoveSql2005SysDbs.cmd "mssqlserver" "g:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" "f:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data"
Files\Microsoft was unexpected at this time.
Thanks,
Denny
October 25, 2007 at 1:14 pm
djohnson,
Thanks, I think you've uncovered a bug. Lines 5 and 9 need to have %2 and %3 in double quotes to handle the spaces you want to include in your path.
As to why you want a path with spaces, I can't say. Getting rid of spaces in paths is a major reason I wrote this utility in the first place, which explains why I never saw the bug...
An additional workaround is to use the Dos 8.3 names. E.g.
[font="Courier New"]MoveSql2005SysDbs.cmd mssqlserver g:\PROGRA~1\MICROS~1\MSSQL.1\MSSQL\Data f:\PROGRA~1\MICROS~1\MSSQL.1\MSSQL\Data[/font]
However, I will try to fix and post a new version to the site. Thanks again!
Vince
djohnson (10/25/2007)
I'm missing something here. Here's the output. Can you point out what I'm missing?C:\Documents and Settings\Administrator\Desktop\movedb>MoveSql2005SysDbs.cmd "mssqlserver" "g:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" "f:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data"
Files\Microsoft was unexpected at this time.
Thanks,
Denny
December 5, 2007 at 2:09 pm
I have recently taken over a client that is moving to a new Application running on SQL 2005. The previous tech left everything in the default C: but we have limited space. The Application has been installed and it created a new database. I would like to use this script to move the system DB's and the Application DB.
Can I add the following where APPDB is the application created database in the respective locations:
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'APPDB', FILENAME = '%NewPath%\APPDB.mdf')"
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE APPDB MODIFY FILE (NAME = 'APPDB', FILENAME = '%NewPath%\APPDB.ldf')"
And
move %OldPath%\APPDB.mdf %NewPath%
move %OldPath%\APPDB.ldf %NewPath%
This is a low risk as it is a new server and application (could be rebuilt at a high $ cost to customer), but it could be useful knowledge as I run into these type of situations in my line of work.
December 5, 2007 at 2:38 pm
You could, but its a whole lot easier to move a non-system DB. Just Detach it, move the files, then attach at the new location. You can detach/attach through the gui.
December 5, 2007 at 3:57 pm
That was much easier. The script needed some of the massaging from previous posts, but everything is running smoothly. I missed the easy custom move in the GUI!!
December 7, 2007 at 12:24 pm
Has anyone use the script on a two node cluster environment? I am building a new SQL2k5 cluster and would like to use the script to move the system db to a shared drive. Any suggestion would be appreciated. Thanks!.
December 7, 2007 at 12:36 pm
1st post here, woo hoo!
SQL Books Online (Sep 2007)
Quick 5 step quick solution. Moving the file is probably the longest step
1. Run the following statement.
ALTER DATABASE database_name SET OFFLINE
2. Move the file or files to the new location.
3. For each file moved, run the following statement.
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' )
4. Run the following statement.
ALTER DATABASE database_name SET ONLINE
5. Verify the file change by running the following query.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N' ');
June 25, 2008 at 9:33 am
Hey Mark, Any update on your script to install 8 instances of SQL 2005 and sp2? What about the maintenance plans?
Vince great scripts
Viewing 15 posts - 31 through 45 (of 75 total)
You must be logged in to reply to this topic. Login to reply