June 26, 2007 at 11:51 am
Just a tidbit of info... The path to the registry entry mentioned above is for "default" instances. For named instances use:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\Parameters]
Stop SQL Server,
Move master(mdf+ldf),
Change the above path,
Start sqlserver with switches,
detach other dbs,
move other dbs,
attach other dbs.
June 26, 2007 at 11:53 am
Sorry... Previous posting eliminated entries in the path...
Between Microsoft SQL Server\ and MSSQLServer is the named instance.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance name\MSSQLServer\Parameters]
February 28, 2025 at 5:33 am
Find below steps to move system databases to another drive.
Auto generated script for Move System Databases from one drive to another Drive:
Syntax:
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' );
Generate script to move Database:
SELECT 'ALTER DATABASE '+DB_name(database_id)+' MODIFY FILE (NAME = [' + f.name + '],'
+ ' FILENAME = ''C:\SQLDBA\Backup\MoveDB\SystemDB\' + f.name
+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
+ ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'DB_Name');-- Update Database name instead of DB_Name which you want move
Model DB:
1. Take the Model DB backup
Backup database model to disk='C:\SQLDBA\Backup\Practice\model.bak'
2. Generate script from below.
ALTER DATABASE model MODIFY FILE (NAME = [modeldev], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\model.mdf');
ALTER DATABASE model MODIFY FILE (NAME = [modellog], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\modellog.ldf');
3. Run generated script
4. Stop the SQL services
5. Move Model DB to Target location
6. Start SQL services
7. Validate whether moved model DB to target location
Sp_helpdb Model
Temp DB:
1. Temp DB backup will not possible-Ignore this step
2. Run below script.
ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [templog], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\templog.ldf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb_mssql_2.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb_mssql_3.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb_mssql_4.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp5], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb_mssql_5.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp6], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb_mssql_6.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp7], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb_mssql_7.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp8], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb_mssql_8.mdf');
3. Restart SQL services
4. Validate whether moved temp DB to target location
Sp_helpdb tempdb
MSDB :
1. Take the MSDB DB backup
Backup database MSDB to disk='C:\SQLDBA\Backup\Practice\MSDB.bak'
2. Generate script from below.
sp_helpdb msdb
ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBData, FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\MSDBData.mdf');
ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBLog, FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\MSDBLog.ldf');
3. Run generated script
4. Stop the SQL services
5. Move MSDB DB to Target location
6. Start SQL services
7. Validate whether moved model DB to target location
Sp_helpdb MSDB
Master DB:
1. Take the master DB backup
Backup database master to disk='C:\SQLDBA\Backup\Practice\Master.bak'
2. Open SQL server configuration manager->SQL Server Services-> Right pan-> Right click on SQL server-> Properties-> Startup parameters-> update target location and click apply
Update the path for -d and -l
-d means master data file
-l means log file
-e means error log
-dC:\SQLDBA\Backup\MoveDB\SystemDB\master.mdf
-lC:\SQLDBA\Backup\MoveDB\SystemDB\mastlog.ldf
3. Stop SQL services
4. Move Master DB to Target location
6. Start SQL services
7. Validate whether moved master DB to target location
Sp_helpdb master
February 28, 2025 at 5:35 am
Find below steps to move system databases to another drive.
Auto generated script for Move System Databases from one drive to another Drive:
Syntax:
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' );
Generate script to move Database:
SELECT 'ALTER DATABASE '+DB_name(database_id)+' MODIFY FILE (NAME = [' + f.name + '],'
+ ' FILENAME = ''C:\SQLDBA\Backup\MoveDB\SystemDB\' + f.name
+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
+ ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'DB_Name');-- Update Database name instead of DB_Name which you want move
Model DB:
1. Take the Model DB backup
Backup database model to disk='C:\SQLDBA\Backup\Practice\model.bak'
2. Generate script from below.
ALTER DATABASE model MODIFY FILE (NAME = [modeldev], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\model.mdf');
ALTER DATABASE model MODIFY FILE (NAME = [modellog], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\modellog.ldf');
3. Run generated script
4. Stop the SQL services
5. Move Model DB to Target location
6. Start SQL services
7. Validate whether moved model DB to target location
Sp_helpdb Model
Temp DB:
1. Temp DB backup will not possible-Ignore this step
2. Run below script.
ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [templog], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\templog.ldf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb_mssql_2.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb_mssql_3.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb_mssql_4.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp5], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb_mssql_5.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp6], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb_mssql_6.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp7], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb_mssql_7.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp8], FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\tempdb_mssql_8.mdf');
3. Restart SQL services
4. Validate whether moved temp DB to target location
Sp_helpdb tempdb
MSDB :
1. Take the MSDB DB backup
Backup database MSDB to disk='C:\SQLDBA\Backup\Practice\MSDB.bak'
2. Generate script from below.
sp_helpdb msdb
ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBData, FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\MSDBData.mdf');
ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBLog, FILENAME = 'C:\SQLDBA\Backup\MoveDB\SystemDB\MSDBLog.ldf');
3. Run generated script
4. Stop the SQL services
5. Move MSDB DB to Target location
6. Start SQL services
7. Validate whether moved model DB to target location
Sp_helpdb MSDB
Master DB:
1. Take the master DB backup
Backup database master to disk='C:\SQLDBA\Backup\Practice\Master.bak'
2. Open SQL server configuration manager->SQL Server Services-> Right pan-> Right click on SQL server-> Properties-> Startup parameters-> update target location and click apply
Update the path for -d and -l
-d means master data file
-l means log file
-e means error log
-dC:\SQLDBA\Backup\MoveDB\SystemDB\master.mdf
-lC:\SQLDBA\Backup\MoveDB\SystemDB\mastlog.ldf
3. Stop SQL services
4. Move Master DB to Target location
6. Start SQL services
7. Validate whether moved master DB to target location
Sp_helpdb master
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy