Use ALTER DATABASE to Move Databases

Use ALTER DATABASE to Move Databases

Follow our Daily tips facebook.com/technettips
•twitter.com/technettips
• Blogs.technet.com/tnmag
• TechNet Tips library You can use the ALTER DATABASE statement to move any system or user-defined database files except for Resource database fi Les. To move files, you specify the cur¬rent logical name of the file and the new file path, which includes the new file Name.y ou can move only one file at a time of this manner.

To move data or log files to a new location, follow these steps:

1. Get The logical name of the data and log files associated with the database by typing the following: /c1>

2.Take the database your want to work with offline by typing these commands:

ALTER DATABASE personnel SET offline GO

3.Move one file at a time to the new location by typing the following:

4.Repeat The previous step to move other data and log files.

5. Put The database online by typing the following commands:

You can verify the change or changes by typing this:

Use master SELECT name, physical_name from sys.master_files WHERE database_id = db_id ("Personnel");

You can also move Full-text catalogs by their logical name. But note if you are specify the new catalog location and you are specify only New_path rather than new_path/file_name. To move a full-text the catalog file to a new location, follow these steps:

1. take the database your want to work with offline by typing the following:

2. Move one file at a time to the new location by typing these commands:

3. Repeat the previous step to move other Full-text catalog files as necessary.

4. Put the database online by typing the following:

ALTER DATABASE database_name SET online GO

On using ALTER DATABASE, see the TipChange Settings with ALTER DATABASE in SQL Server.


From the Microsoft Press book Microsoft SQL Server Administrator ' s Pocket consultant, Second Edition by William R. Stanek.

Use ALTER DATABASE to Move Databases