Home » System Databases

System Databases

sql server DBA 999

Moving system databases in SQL Server 2008/2005

Moving system databases in SQL Server 2008, 2005

To move all the system and user databases from “C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA” folder to “C:System databases” location.

We can use below query to check current location of system databases:

Select name, physical_name from sys.master_files


1.Moving Tempdb database:

We need to use the Alter database command to move tempdb database files from one location to another as shown in the below screenshot.
How to move system databases sql server 3

Use master
go
Alter database tempdb modify file (name=tempdev,filename='C:System databasestempdb.mdf')
go
Alter database tempdb modify file (name=templog,filename='C:System databasestempdb.ldf')
go

Once we execute the above command the o/p will be something like,

The file “tempdev” has been modified in the system catalog. The new path will
Read the rest