Use the ALTER Database Command to Migrate Database in SQL Server

This article explores use the alter database command to migrate database in SQL server with straightforward explanations and useful context. It also highlights common questions and important details to consider.

• Specify the name and path of the file.
• Full path of new data file.

Note that if you do it this way, you can only move one file at a time. ALTER DATABASE's general structure includes:

1. Find the database name and log file with the command

USE master SELECT name, physical_name FROM sys. master_files WHERE database_id = DB_ID ("Personnel");

2. Set to OFFLINE mode

ALTER DATABASE Personnel SET offline GO

3. Move the file to a new location using the command

ALTER DATABASE Personnel MODIFY FILE (NAME = Personnel_Data, FILENAME = "C: DataPersonnel_Data. mdf") GO

4. Repeat the above commands with other data and log files.

5. After finishing, set ONLINE mode with the command

ALTER DATABASE Personnel SET online GO

And finally, check the database changes:

USE master SELECT name, physical_name FROM sys. master_files WHERE database_id = DB_ID ("Personnel");

Or besides, when needed to move data by full-text path, just declare the new path instead of the new path + the file name as usual. The full steps are as follows:

1. Set OFFLINE mode for database by command

ALTER DATABASE database_name SET offline GO

2. Move each file one by one

ALTER DATABASE database_name MODIFY FILE (NAME = logical_name, FILENAME = "new_path". GO

3. Do the same with other catalog files.

4. Set the database to ONLINE with the command

ALTER DATABASE database_name SET online GO

Some notes to know when using the ALTER DATABASE command, please refer here. Good luck!