Moving system databases to a another folder

  • 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.

  • 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]

  • 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

  • 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