Skip to content

Latest commit

 

History

History
234 lines (169 loc) · 10.1 KB

move-user-databases.md

File metadata and controls

234 lines (169 loc) · 10.1 KB
titledescriptionauthorms.authorms.reviewerms.datems.servicems.topichelpviewer_keywords
Move user databases
Learn how to move user databases in SQL Server.
WilliamDAssafMSFT
wiassaf
randolphwest
09/19/2024
sql
how-to
disaster recovery [SQL Server], moving database files
database files [SQL Server], moving
data files [SQL Server], moving
editions [SQL Server], moving databases between
moving full-text catalogs
scheduled disk maintenance [SQL Server]
moving databases
full-text catalogs [SQL Server], moving
moving database files
moving user databases
relocating database files
planned database relocations [SQL Server]
databases [SQL Server], moving

Move user databases

[!INCLUDE SQL Server]

In [!INCLUDE ssNoVersion], you can move the data, log, and full-text catalog files of a user database to a new location by specifying the new file location in the FILENAME clause of the ALTER DATABASE statement. This method applies to moving database files within the same instance [!INCLUDE ssNoVersion]. To move a database to another instance of [!INCLUDE ssNoVersion] or to another server, use backup and restore or detach and attach operations.

Note

This article covers moving user database files. For moving system database files, see Move system databases.

Considerations

When you move a database onto another server instance, to provide a consistent experience to users and applications, you might have to recreate some or all the metadata for the database. For more information, see Manage Metadata When Making a Database Available on Another Server.

Some features of the [!INCLUDE ssDEnoversion] change the way that the [!INCLUDE ssDE] stores information in the database files. These features are restricted to specific editions of [!INCLUDE ssNoVersion]. A database that contains these features can't be moved to an edition of [!INCLUDE ssNoVersion] that doesn't support them. Use the sys.dm_db_persisted_sku_features dynamic management view to list all edition-specific features that are enabled in the current database.

The procedures in this article require the logical name of the database files. To obtain the name, query the name column in the sys.master_files catalog view.

Full-text catalogs are integrated into the database rather than being stored in the file system. The full-text catalogs move automatically when you move a database.

Note

Make sure the service account for the Configure Windows service accounts and permissions has permissions to the new file location in the file system. For more information, see Configure file system permissions for Database Engine access.

Planned relocation procedure

To move a data or log file as part of a planned relocation, follow these steps:

  1. For each file to be moved, run the following statement.

    ALTERDATABASE database_name MODIFY FILE (NAME = logical_name, FILENAME ='new_path\os_file_name');

  2. Run the following statement to bring the database offline.

    ALTERDATABASE database_name SET OFFLINE;

    This action requires exclusive access to the database. If another connection is open to the database, the ALTER DATABASE statement is blocked until all connections are closed. To override this behavior, use the WITH <termination> clause. For example, to automatically roll back and disconnect all other connections to the database, use:

    ALTERDATABASE database_name SET OFFLINE WITH ROLLBACK IMMEDIATE;

  3. Move the file or files to the new location.

  4. Run the following statement.

    ALTERDATABASE database_name SET ONLINE;

  5. Verify the file change by running the following query.

    SELECT name, physical_name AS CurrentLocation, state_desc FROMsys.master_filesWHERE database_id = DB_ID(N'<database_name>');

Relocation for scheduled disk maintenance

To relocate a file as part of a scheduled disk maintenance process, follow these steps:

  1. For each file to be moved, run the following statement.

    ALTERDATABASE database_name MODIFY FILE (NAME = logical_name, FILENAME ='new_path\os_file_name');

  2. To perform maintenance, stop the instance of [!INCLUDE ssNoVersion] or shut down the system. For more information, see Start, stop, pause, resume, and restart SQL Server services.

  3. Move the file or files to the new location.

  4. Restart the instance of [!INCLUDE ssNoVersion] or the server. For more information, see Start, stop, pause, resume, and restart SQL Server services

  5. Verify the file change by running the following query.

    SELECT name, physical_name AS CurrentLocation, state_desc FROMsys.master_filesWHERE database_id = DB_ID(N'<database_name>');

Failure recovery procedure

If a file must be moved because of a hardware failure, use the following steps to relocate the file to a new location.

Important

If the database can't be started, that is it's in suspect mode or in an unrecovered state, only members of the sysadmin fixed role can move the file.

  1. Stop the instance of [!INCLUDE ssNoVersion] if it was already started.

  2. Start the instance of [!INCLUDE ssNoVersion] in master-only recovery mode by entering one of the following commands at the command prompt.

  3. For each file to be moved, use sqlcmd commands or [!INCLUDE ssManStudioFull] to run the following statement.

    ALTERDATABASE database_name MODIFY FILE (NAME = logical_name, FILENAME ='new_path\os_file_name');

    For more information about how to use the sqlcmd utility, see sqlcmd - use the utility.

  4. Exit the sqlcmd utility or [!INCLUDE ssManStudioFull].

  5. Stop the instance of [!INCLUDE ssNoVersion].

  6. Move the file or files to the new location.

  7. Start the instance of [!INCLUDE ssNoVersion]. For example, run: NET START MSSQLSERVER.

  8. Verify the file change by running the following query.

    SELECT name, physical_name AS CurrentLocation, state_desc FROMsys.master_filesWHERE database_id = DB_ID(N'<database_name>');

Examples

The following example moves the [!INCLUDE ssSampleDBobject] log file to a new location as part of a planned relocation.

  1. Make sure you are in the context of the master database.

    USE master; GO

  2. Return the logical file name.

    SELECT name, physical_name AS CurrentLocation, state_desc FROMsys.master_filesWHERE database_id = DB_ID(N'AdventureWorks2022') AND type_desc = N'LOG'; GO

  3. Set the database offline.

    ALTERDATABASE AdventureWorks2022 SET OFFLINE; GO

  4. Physically move the file to a new location. In the following statement, modify the path specified in FILENAME to the new location of the file on your server.

    ALTERDATABASE AdventureWorks2022 MODIFY FILE (NAME = AdventureWorks2022_Log, FILENAME ='C:\NewLoc\AdventureWorks2022_Log.ldf'); GO ALTERDATABASE AdventureWorks2022 SET ONLINE; GO

  5. Verify the new location.

    SELECT name, physical_name AS CurrentLocation, state_desc FROMsys.master_filesWHERE database_id = DB_ID(N'AdventureWorks2022') AND type_desc = N'LOG';

Related content

close