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