Steps to move SQL Server Database Files?

Sometimes DBA have a requirement to move/relocate Database files from one path to another path either on the same server on another server. In this blogpost you'll see and learn how to perform DB files relocation and this can be easily achieved using the standard SQL functions to detach and attach databases.

Follow below steps:

1. Run 'sp_helpfile' to determine which Files to Move

If you are unsure of the files that are used by a database then you can use a built-in SQL Server stored procedure to generate a file list. This will include all of the data and log files for the database, these being the files that you want to relocate. To determine the file list, execute the following query within SQL Analyser, SQL Server Management Studio or another query tool, ensuring that you are linked to the correct database:

syntax: sp_helpfile

2. Detach your Database

SQL Server database files are always in use whilst the service is running. To detach the database from the service, ensure that no users are connected to the database and then execute the following command. Replace the 'testdb' name with the name of the database to disconnect. After this step, you will be able to move the files to their new location using Windows Explorer or any another appropriate file manager tool.

Synatx : sp_detach_db 'TestDB'

NOTE: Please connect to another database before detaching the database for which data and log files need to be moved. Try to run as below command: use master go sp_detach_db 'TestDB' go

3. Move your database's DATA and LOG files.

Now move DATA (mdf) and LOG(ldf) files of your database from source path to you desired or planned target path. For example:

Source Path - C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL

Target Path - D:\SQLServer\

4. Reattach your Database

Once the database files have been moved, the database can be reattached to SQL Server. To attach the database, the sp_attach_db command is used. This command requires parameters. The first parameter names the database; the remaining parameters list the database and log files to attach. Once executed, the database is again available for use.

Syntax : sp_attach_db 'TestDB', 'D:\SQLServer\UserDBData\TestDB.mdf', 'D:\SQLServer\UserDBLog\TestDB_log.ldf'

That's all in this post. If you liked this blog and interested in knowing more about SQL Server, Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com .