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

Happening

Upgrade from AX 2012 to Latest Dynamics 365 Finance and Operation

Below are the steps defined by sequence. 1. Create new Upgrade project in Dynamics LCS. 2. Create VSTS Project and connect it with L...

Trending now