Saturday, June 23, 2018

Move SQL Server database files

Create tempdb partitions and move to new location location.

Know existing location:
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');

Move to new location:

ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = "C:\SQLDB\tempdb.mdf")
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = "C:\SQLDB\templog.ldf")
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev1, FILENAME = "C:\SQLDB\tempdb1.mdf")
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog1, FILENAME = "C:\SQLDB\templog1.ldf")

Note:
Also verify that SQL service account has full access to new location.

To move msdb, need to follow steps below after executing above queries.

1. Go to SQL Server configuration manager.
2. Update path on parameters.


No comments:

Post a Comment

Reindexing Dynamis AX database

Below script will create job to re-index and update statistics of dynamics ax database. In script below fill factor is used as 30%, revie...