Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
Course Of The Month
3 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Move SQL System Databases to a new SAN location on a Cluster Server
Posted on 2015-01-13
We are moving our current SQL Cluster Server storage to a new SAN.
After running T-SQL
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
The path parameters in Sql Configuration must be changed
-dC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER
-lC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER
On a Cluster Sever I see that the SQL Server and Agent are stopped and a Deamon Launcher is running.
What is the correct procedure to change the path parameters in Sql Configuration on a Cluster Server?
Change the path settings in the SQL Server properties / Startup Parameters and then restart the Deamon Launcher?
After running T-SQL
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
The path parameters in Sql Configuration must be changed
-dC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER
-lC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER
On a Cluster Sever I see that the SQL Server and Agent are stopped and a Deamon Launcher is running.
What is the correct procedure to change the path parameters in Sql Configuration on a Cluster Server?
Change the path settings in the SQL Server properties / Startup Parameters and then restart the Deamon Launcher?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
2
2- +1
10 Comments
Active today
The path parameters in Sql Configuration must be changedBeing a cluster don't forget to change it in all nodes.
Active 2 days ago
You'll have to physically move the database MDF/NDF/LDF files at the OS level, too - just doing an "ALTER DATABASE" command won't be sufficient to migrate these databases to your new storage location. When we've had to do this before, we've always done a detach/copy/reattach process to get them moved.
You'll have to do the ALTER DATABASE for your TempDB, since it's re-created from scratch every time the server is started up and will be moved the next time the instance comes online. To move your master and other system databases, you'll need to modify the startup parameters of your MSSQL service on each cluster node to ensure it knows where the database files are located
As an alternate to this whole confusion, and depending on your tolerance for downtime, could you accommodate something like this (much simpler approach)?
1. Mount new storage to an accessible location on the active node
2. Shut down MSSQL database instance
3. Copy all contents of old storage mount to new storage mount (using something that persists ACL and journaling, like XCOPY)
4. Change mount point of old storage to move it out of the way (give it a new drive letter)
5. Remount new storage mount into location of old storage mount
6. Bring MSSQL database instance back online
SQL Server is none-the-wiser - aside from the outage you took to copy the files, the database server has no idea you swapped storage and no additional commands are required to redirect anything. We've also done this before and it works great.
If you're moving one database at a time for downtime reasons, then the other methods would be necessary, but if you're swapping out the disk under the whole database instance, this is the way easier way to go.
You'll have to do the ALTER DATABASE for your TempDB, since it's re-created from scratch every time the server is started up and will be moved the next time the instance comes online. To move your master and other system databases, you'll need to modify the startup parameters of your MSSQL service on each cluster node to ensure it knows where the database files are located
As an alternate to this whole confusion, and depending on your tolerance for downtime, could you accommodate something like this (much simpler approach)?
1. Mount new storage to an accessible location on the active node
2. Shut down MSSQL database instance
3. Copy all contents of old storage mount to new storage mount (using something that persists ACL and journaling, like XCOPY)
4. Change mount point of old storage to move it out of the way (give it a new drive letter)
5. Remount new storage mount into location of old storage mount
6. Bring MSSQL database instance back online
SQL Server is none-the-wiser - aside from the outage you took to copy the files, the database server has no idea you swapped storage and no additional commands are required to redirect anything. We've also done this before and it works great.
If you're moving one database at a time for downtime reasons, then the other methods would be necessary, but if you're swapping out the disk under the whole database instance, this is the way easier way to go.
Thanks for your replies.
I like the swapping storage drives approach, but downtime must be kept as short as possible.
Can you please check the following steps are correct? It's a two node active passive cluster.
- Stop SQL Server Agent (instance) on active Cluster Server.
- Change the path parameters in Sql Configuration
These must be changed on both active and passive Cluster servers?
-dC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER
-lC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER
- Run T-Sql Alter database script for tempdb, msdb, model and the master.
This must be run on both active and passive Cluster servers?
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
- Stop SQL Server (instance)
- Move the databases master/masterlog model/modellog MSDBData/MSDBLog to the new location.
No need to move the tempdb/templog, they will be recreated on startup.
- Start SQL Server (instance) and SQL Server Agent (instance).
- Next step is to detach and attach all user databases one after the other.
- Finally Quorum and MSDTC.
These can be recreated with SQL server and Agent running?
Any tips?
I like the swapping storage drives approach, but downtime must be kept as short as possible.
Can you please check the following steps are correct? It's a two node active passive cluster.
- Stop SQL Server Agent (instance) on active Cluster Server.
- Change the path parameters in Sql Configuration
These must be changed on both active and passive Cluster servers?
-dC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER
-lC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER
- Run T-Sql Alter database script for tempdb, msdb, model and the master.
This must be run on both active and passive Cluster servers?
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
- Stop SQL Server (instance)
- Move the databases master/masterlog model/modellog MSDBData/MSDBLog to the new location.
No need to move the tempdb/templog, they will be recreated on startup.
- Start SQL Server (instance) and SQL Server Agent (instance).
- Next step is to detach and attach all user databases one after the other.
- Finally Quorum and MSDTC.
These can be recreated with SQL server and Agent running?
Any tips?
Active today
Instead of "Stop SQL Server Agent (instance) on active Cluster Server" should be "Stop SQL Server (instance) on active Cluster Server" but before that you need to do the "Run T-Sql Alter database script for tempdb, msdb, model and the master".
This must be run on both active and passive Cluster servers? This need to be executed while SQL Server is running so you just need to connect to the SQL Server using SSMS and the run the following commands:
For the master database the procedure is different, so now is the step to change the startup parameters in both nodes:
-dNewPath\master.mdf
-lNewPath\mastlog.ldf
Now is time for physically move the files to the new location:
- Move the databases master/masterlog model/modellog MSDBData/MSDBLog to the new location.
- No need to move the tempdb/templog, they will be recreated on startup.
After that you can:
- Start SQL Server (instance) and SQL Server Agent (instance).
This must be run on both active and passive Cluster servers? This need to be executed while SQL Server is running so you just need to connect to the SQL Server using SSMS and the run the following commands:
-- Move TempDB
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'NewPath\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'NewPath\templog.ldf')
GO
-- Move Model
ALTER DATABASE model
MODIFY FILE ( NAME = modeldev , FILENAME = 'NewPath\model.mdf' )
GO
ALTER DATABASE model
MODIFY FILE ( NAME = modellog , FILENAME = 'NewPath\modellog.ldf' )
GO
-- Move MSDB
ALTER DATABASE msdb
MODIFY FILE ( NAME = MSDBdata , FILENAME = 'NewPath\MSDBData.mdf' )
GO
ALTER DATABASE msdb
MODIFY FILE ( NAME = MSDBlog , FILENAME = 'NewPath\MSDBlog.ldf' )
GO
For the master database the procedure is different, so now is the step to change the startup parameters in both nodes:
-dNewPath\master.mdf
-lNewPath\mastlog.ldf
Now is time for physically move the files to the new location:
- Move the databases master/masterlog model/modellog MSDBData/MSDBLog to the new location.
- No need to move the tempdb/templog, they will be recreated on startup.
After that you can:
- Start SQL Server (instance) and SQL Server Agent (instance).
Thanks for all the info. I started today with the TEST instance, but there is some bad news here. The instance does not want to come back online after moving the System Databases.
I stopped De SQL Server Agent (TEST) and ran the following scripts:
ALTER DATABASE model MODIFY FILE (NAME = modeldev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE model MODIFY FILE (NAME = modellog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE tempdb MODIFY FILE (NAME = templog , FILENAME = 'N:\MSSQL10.TEST\MSSQL\Dat
Result:
The file "modeldev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "modellog" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "MSDBData" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "MSDBLog" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
Stopped the SQL Server (TEST) and changed the Startup Parameters:
Original Startup Parameters:
-dY:\MSSQL10.TEST\MSSQL\DA
New Startup Parameters:
-dK:\MSSQL10.TEST\MSSQL\DA
After bringing the TEST instance back Online it didn't want to start anymore. It looks like a rights issue.
Errorlog:
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file K:\MSSQL10.TEST\MSSQL\DATA
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "K:\MSSQL10.TEST\MSSQL\DAT
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file K:\MSSQL10.TEST\MSSQL\DATA
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "K:\MSSQL10.TEST\MSSQL\DAT
Error: 5105, Severity: 16, State: 1.
A file activation error occurred. The physical file name 'K:\MSSQL10.TEST\MSSQL\DAT
Error: 945, Severity: 14, State: 2.
Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server.
Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
The folder rights of original and new location are the same, except for a Username MSSQL@TEST.
I can't find this user MSSQL@TEST anywhere in AD. I added the SQL service account, but still the TEST instance doesn't want come back online.
What can be the problem here?
I stopped De SQL Server Agent (TEST) and ran the following scripts:
ALTER DATABASE model MODIFY FILE (NAME = modeldev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE model MODIFY FILE (NAME = modellog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE tempdb MODIFY FILE (NAME = templog , FILENAME = 'N:\MSSQL10.TEST\MSSQL\Dat
Result:
The file "modeldev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "modellog" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "MSDBData" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "MSDBLog" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
Stopped the SQL Server (TEST) and changed the Startup Parameters:
Original Startup Parameters:
-dY:\MSSQL10.TEST\MSSQL\DA
New Startup Parameters:
-dK:\MSSQL10.TEST\MSSQL\DA
After bringing the TEST instance back Online it didn't want to start anymore. It looks like a rights issue.
Errorlog:
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file K:\MSSQL10.TEST\MSSQL\DATA
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "K:\MSSQL10.TEST\MSSQL\DAT
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file K:\MSSQL10.TEST\MSSQL\DATA
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "K:\MSSQL10.TEST\MSSQL\DAT
Error: 5105, Severity: 16, State: 1.
A file activation error occurred. The physical file name 'K:\MSSQL10.TEST\MSSQL\DAT
Error: 945, Severity: 14, State: 2.
Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server.
Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
The folder rights of original and new location are the same, except for a Username MSSQL@TEST.
I can't find this user MSSQL@TEST anywhere in AD. I added the SQL service account, but still the TEST instance doesn't want come back online.
What can be the problem here?
Hi,
You put wrong filenames:
Start instance in master-only mode, connect with sqlcmd and use correct commands, e.g.
Commands to start in master only from command prompt:
*Default instance
*Named instace
Cheers,
Daniel
You put wrong filenames:
ALTER DATABASE model MODIFY FILE (NAME = modeldev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')--it's a folder
ALTER DATABASE model MODIFY FILE (NAME = modellog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')--it's a folder
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')--it's a folder
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')--it's a folder
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')--it's a folder
ALTER DATABASE tempdb MODIFY FILE (NAME = templog , FILENAME = 'N:\MSSQL10.TEST\MSSQL\Data')--it's a folder
Start instance in master-only mode, connect with sqlcmd and use correct commands, e.g.
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\MSDBData.mdf')
Commands to start in master only from command prompt:
*Default instance
NET START MSSQLSERVER /f /T3608
*Named instace
NET START MSSQL$instancename /f /T3608
Cheers,
Daniel
Thanks Daniel,
A big mistake was made, messed it up here. It should be:
There are 3 instances on the server:
SQL01
SQL04\Production
SQL05\TEST
Is the following command correct for startup in master-only mode for the test instance SQL05\TEST?
Can SSMS T-SQL query be used for this instance in startup master-only mode?
If sqlcmd must be used is the following command correct, when I put the ALTER DATABASE... lines into D:\Script\TEST.sql?
A big mistake was made, messed it up here. It should be:
ALTER DATABASE model MODIFY FILE (NAME = modeldev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\model.mdf')
ALTER DATABASE model MODIFY FILE (NAME = modellog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\modellog.ldf')
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\MSDBData.mdf')
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\MSDBLog.ldf')
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = templog , FILENAME = 'N:\MSSQL10.TEST\MSSQL\Data\templog.ldf')
There are 3 instances on the server:
SQL01
SQL04\Production
SQL05\TEST
Is the following command correct for startup in master-only mode for the test instance SQL05\TEST?
NET START mssql$SQL05\TEST /f /T3608
or
NET START mssql$TEST /f /T3608
or
NET START "SQL05\TEST" /f /T3608
Can SSMS T-SQL query be used for this instance in startup master-only mode?
If sqlcmd must be used is the following command correct, when I put the ALTER DATABASE... lines into D:\Script\TEST.sql?
sqlcmd -S "SQL05\TEST" -i "D:\Script\TEST.sql" -o "D:\Script\Output.txt"
Hi,
Is the following command correct for startup in master-only mode for the test instance SQL05\TEST?
>> NET START mssql$TEST /f /T3608
Can SSMS T-SQL query be used for this instance in startup master-only mode?
>> Unfortunately not
If sqlcmd must be used is the following command correct, when I put the ALTER DATABASE... lines into D:\Script\TEST.sql?
>> Try this one:
Is the following command correct for startup in master-only mode for the test instance SQL05\TEST?
>> NET START mssql$TEST /f /T3608
Can SSMS T-SQL query be used for this instance in startup master-only mode?
>> Unfortunately not
If sqlcmd must be used is the following command correct, when I put the ALTER DATABASE... lines into D:\Script\TEST.sql?
>> Try this one:
sqlcmd -S "SQL05\TEST" -d master -i "D:\Script\TEST.sql" -o "D:\Script\Output.txt"
I've requested that this question be closed as follows:
Accepted answer: 0 points for NicoNL's comment #a40553189
Assisted answer: 125 points for Vitor Montalvão's comment #a40548877
Assisted answer: 125 points for ryanmccauley's comment #a40549528
Assisted answer: 250 points for Daniel_PL's comment #a40551957
for the following reason:
SQL is mirated to new SAN
Accepted answer: 0 points for NicoNL's comment #a40553189
Assisted answer: 125 points for Vitor Montalvão's comment #a40548877
Assisted answer: 125 points for ryanmccauley's comment #a40549528
Assisted answer: 250 points for Daniel_PL's comment #a40551957
for the following reason:
SQL is mirated to new SAN
Featured Post
Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder. The folder can be on the local hard drive or on a network share. This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
Suggested Courses
Course of the Month3 days, 5 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
696 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.