Steps to Move
System DBs after installing SQL Server
1.Stop SQL Services
2. Copy system databases(master, model, msdb)data files and log files
From source and paste in new destination
3.Change in Startup Parameter for moving master Database
Example of Old Path in 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
New Path in Startup Parameter
-dG:\ System data and logs\master.mdf;-eG:\ System data and logs\Log\ERRORLOG;-lG:\ System data and logs\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:\ System data and logs
\model.mdf')
Go
Use master
GO
Alter Database Model
Modify File (Name = 'modellog', FileName = 'G:\ System data and logs
\modellog.ldf')
Go
Use master
Go
Alter Database MSDB
Modify File (Name = 'MSDBData', FileName = 'G:\ System data and logs
\MSDBData.mdf')
Go
Use master
GO
Alter Database MSDB
Modify File (Name = 'MSDBlog', FileName = 'G:\ System data and logs
\MSDBlog.ldf')
Go
6.Stop Services
7. Remove old mdf and ldf files for system databases
8. Start services and Verify
1.Stop SQL Services
2. Copy system databases(master, model, msdb)data files and log files
From source and paste in new destination
3.Change in Startup Parameter for moving master Database
Example of Old Path in 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
New Path in Startup Parameter
-dG:\ System data and logs\master.mdf;-eG:\ System data and logs\Log\ERRORLOG;-lG:\ System data and logs\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:\ System data and logs
\model.mdf')
Go
Use master
GO
Alter Database Model
Modify File (Name = 'modellog', FileName = 'G:\ System data and logs
\modellog.ldf')
Go
Use master
Go
Alter Database MSDB
Modify File (Name = 'MSDBData', FileName = 'G:\ System data and logs
\MSDBData.mdf')
Go
Use master
GO
Alter Database MSDB
Modify File (Name = 'MSDBlog', FileName = 'G:\ System data and logs
\MSDBlog.ldf')
Go
6.Stop Services
7. Remove old mdf and ldf files for system databases
8. Start services and Verify
No comments:
Post a Comment