APPLICATIONS, SOFTWARE AND TECHNOLOGY

note: use sp_helpdb 'DATABASE name' to check the location of the database before you start

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 type below command

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