SYSTEMS DATABASE RELOCATION

note: use sp_helpdb 'DATABASE name' to check the location of the database before you start and do backup all the DATABASES first.


STEP 1:master

1) stop SQL SERVER
2) copy master to the new location
3) check the registry parameter :

open regedit(modify registry)
-->local machine -->software -->microsoft -->mssqlserver -->MSSQLSERVER -->parameter
change below parameter

-dD:\Microsoft SQL Server\MSSQL\data\master.mdf (-d should remain)

-lD:\Microsoft SQL Server\MSSQL\data\mastlog.ldf (-l should remain)
4) start sql server


STEP 2:tempdb

1) query analyzer

use master

go

Alter database tempdb modify file (name = tempdev, filename = 'h:\tempdb.mdf')

go

Alter database tempdb modify file (name = templog, filename = 'h:\templog.ldf')

go

Results after command completed:

"File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.File 'templog' modified in sysaltfiles. "

You may delete the old file after restarting SQL Server.
2) restart sql server (do sp_helpdb to check the changes)
3) delete the old physical files.


STEP 3:msdb and model

1) if the db is hardened :open regedit(modify registry)
-->local machine -->software -->microsoft -->mssqlserver -->mssqlserver -->parameter-->rightclick, add new string value name SQLArg3 and value data -T3608
2) restart sql
3) query analyzer (pls close enterprise manager)
note: make sure the physical file exists at the new location.
example:

sp_detach_db xxx, 'true'goEXEC sp_attach_db 'xxx','c:\xxx.mdf','c:\xxx.ldf'
sp_detach_db yyy, 'true'goEXEC sp_attach_db 'yyy','c:\yyy.mdf','c:\yyy.ldf'
(do sp_helpdb to check the changes)
4) delete newly created string value name SQLArg3 and value data -T3608
5) restart sql server