Friday, March 6, 2015

Change System Database default path in SQL ?

Steps Followed to Move System DBs in SQL Server
1. Stop Services
2. Copy system databases (master, model, msdb) data files and log files and paste in new destination
3. Change in Startup Parameter for moving master Databas
--Old Path in Startup Parameter
-dG:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eG:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lG:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

New Path in Startup Parameter (do not copy paste)
-dG:\master.mdf;-lG:\mastlog.ldf
4. Start services and Verify
5. Change destination path and Run below script to move msdb and model databases
Use master
Go
Alter Database Model
Modify File (Name = 'modeldev', FileName = 'G:\model.mdf')
Go
Use master
GO
Alter Database Model
Modify File (Name = 'modellog', FileName = 'G:\modellog.ldf')
Go
Use master
Go
Alter Database MSDB
Modify File (Name = 'MSDBData', FileName = 'G:\MSDBData.mdf')
Go
Use master
GO
Alter Database MSDB
Modify File (Name = 'MSDBlog', FileName = 'G:\MSDBlog.ldf')
Go
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA\templog.ldf');
GO
6.Stop Services
7. Remove old mdf and ldf files for system databases
8. Start services and Verify

---------------Change Default Backup Path--------
1. Run > REGEDIT
2. HKEY_LOCAL_MACHINE  >  SOFTWARE  > Microsoft
> Microsoft SQL Server  > MSSQL10_50.MSSQLSERVER(For Default Instance)
> MSSQLServer > BackupDirectory> Modify > OK > Verify From SQL SERVER
 

1 comment: