This is a question I got asked a few times the last few months, so why not write a little blog article about it.

An easy way to move user database files to a different location is by detaching, moving and attaching the user database.

(Of course, while you are doing this, people will not be able to work in the database. Make sure they logout of Dynamics NAV correctly.)

This database move can be done in SQL Server Management Studio (SSMS) so you don't have to type any code:

• Start SQL Server Management Studio
• Expand the server instance, expand Databases
• Right-click the database you want to move, and choose "Properties"
• In the Properties window, choose "Files" and write down the current file paths. Click "Cancel"
• Right-click the database again, and choose "Tasks - Detach..."
• Click "OK" in the next window
• Use Windows Explorer to move the data and log files (.mdf and .ldf) to the new location
• Right-click Databases, and choose "Attach..."
• In the "Attach databases" window, click "Add"
• In the "Locate database files" window, browse to the new location and select the .mdf file. Click "OK"
• In the details pane, verify that the new location is listed for both the .mdf and the .ldf file. Click "OK"
• In SQL Server Management Studio, choose "View - Refresh" and verify that your database is listed again under Databases

Alternatively you can back up the database, within Sql Server, and restore it, specifying a different location for the files on the options table or use the MOVE clause in the RESTORE command in Transact-SQL:

How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

Comments

[prudence]

Thank you so very much for the post,this was very helpful!!!