SQL SERVER DBA, Linux and Azure: How to move system databases from one location to another location in SQL server

How to move system databases from one location to another location in SQL server


Move tempdb:

1) first we have to know the location of tempdb data file and log file location by using below query
use tempdb
go
exec sp_helpfile
go

2) run the below query to save the sql server new location of tempdb data file and log file path

alter database tempdb modify file (name=tempdev, filename='E:\sysdbs\mdf\tempdb.mdf')
go
alter database tempdb modify file (name=templog, filename='E:\sysdbs\ldf\templog.ldf')
go

after execute the above query it shows message like the sql server save your catalog but you must restart the sql server

3) stop the sql server

4) start the sql server

5)verify the path for tempdb data file and log file by using below query
exec sp_helpfile
go

6) whenever restart the sql server new tempdb will be created so automatically the new tmpdb will created in new location  goto old location and delete the old tempdb files


move model :

1) first we have to know the location of tempdb data file and log file location by using below query
use model
go
exec sp_helpfile
go

2)run the below query to save the sql server new location of model data file and log file path

alter database model modify file (name=modeldev, filename='E:\sysdbs\mdf\model.mdf')
go
alter database model modify file (name=modellog, filename='E:\sysdbs\ldf\modellog.ldf')
go

after execute the above query it shows message like the sql server save your catalog but you must restart the sql server

3) stop the sql server

4)move the  model databse data file and log file from old location to new location

5) start the sql server

6)verify the path for model data file and log file by using below query
exec sp_helpfile
go


move msdb : 

1) first we have to know the location of msdb data file and log file location by using below query
use msdb
go
exec sp_helpfile
go

2)run the below query to save the sql server new location of msdb data file and log file path

alter database msdb modify file (name=msdbdata, filename='E:\sysdbs\mdf\msdbdata.mdf')
go
alter database msdb modify file (name=msdblog, filename='E:\sysdbs\ldf\msdblog.ldf')
go

after execute the above query it shows message like the sql server save your catalog but you must restart the sql server

3) stop the sql server

4)move the  msdb data file and log file from old location to new location

5) start the sql server

6)verify the path for model data file and log file by using below query
exec sp_helpfile
go

move master :

1) copy the startup paramaeter(sql server configuration manager---sql server services---sql server(mssqlserver)---properties---advanced---startup parameter)

-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

2) stop the sql server

3) move the master data file and log file from old location to new location

4)paste the startup parameter as a new location of master data fil;e and log file

(sql server configuration manager---sql server services---sql server(mssqlserver)---properties---advanced---startup parameter)

-dE:\sysdbs\mdf\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lE:\sysdbs\ldf\mastlog.ldf

apply ok

5)it shows one message like system save the ur request but you must restart the sql server to apply these rules

6) start the sql server

7)if run sql server there is no issues if sql server will not  work check the startup parameters(startup parameters are very senstive it doesnt have single extra collan or space etc)

8)check the new path of master database data file and log file by using below query

exec sp_helpfile



No comments:

Post a Comment