To move system databases from one place to another is a complex process that requires proper planning and implementation steps. Step-by-step implementation plane to move system databases from one place to another is given below:
1. Proper Migration Plan is required.
2. If possible, include backup team to take cohesive / snapshot at their end.
3. Drop mail to application team for Pre-activity intimation.
4. Drop mail to monitoring team to include the server in maintenance mode.
5. Run the pre-health check for server as well as databases, services, drive spaces etc.
6. Drop mail to application team to stop related application before proceeding to move system database.
7. Take backup of all system databases if required.
8. Check the current configuration & update SQL Server configuration like below
To check current details like Logical File Name, Path etc etc.
SELECT name AS LogicalFileName, DB_Name(database_id) AS DBName, Physical_name AS ActualFilePath FROM sys.master_files
WHERE database_id < 5
To update configuration, use below queries:
--Model - For Data File
ALTER DATABASE model
MODIFY FILE( NAME = modeldev, FILENAME = 'F:\MSSQL\SystemDatabases\SQLData\model.mdf' )
--Model - For Log File
ALTER DATABASE model
MODIFY FILE( NAME = modellog, FILENAME = 'L:\MSSQL\SystemDatabases\SQLLogs\modellog.ldf')
10. Move the system databases from current location to new location.
11. Start the SQL Server service.
12. Validate using above SELECT query.
13. Run the post activity health check on the server to validate all DB status, SQL services, any error in SQL error log etc.
14. After moving the system DB to new location, check DBs are online & accessible or not. If accessible, delete old database files & backup files to free up space.
15. Drop the mail to application team to validate the application.
16. Drop the mail to monitoring team to remove the server from maintenance mode.
17. Drop the mail to all stakeholder that the task has been completed successfully.
No comments:
Post a Comment