Use the ALTER DATABASE command to migrate DATABASE in SQL Server

Technically, you can use the ALTER DATABASE syntax to move any system or user database files that define, initialize, except for the Resource database. To move these data files, you need:

1. Specify the name and path of the file.
2. 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, if you want 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!