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