Ócáid
Mar 31, 11 PM - Apr 2, 11 PM
An ócáid foghlama SQL, Fabric and Power BI is mó. Márta 31 – 2 Aibreán. Bain úsáid as cód FABINSIDER chun $ 400 a shábháil.
Cláraigh inniuNí thacaítear leis an mbrabhsálaí seo a thuilleadh.
Uasghrádú go Microsoft Edge chun leas a bhaint as na gnéithe is déanaí, nuashonruithe slándála, agus tacaíocht theicniúil.
Applies to:
SQL Server
In SQL Server, 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 SQL Server. To move a database to another instance of SQL Server or to another server, use backup and restore or detach and attach operations.
Nóta
This article covers moving user database files. For moving system database files, see Move system databases.
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 SQL Server Database Engine change the way that the Database Engine stores information in the database files. These features are restricted to specific editions of SQL Server. A database that contains these features can't be moved to an edition of SQL Server 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.
Nóta
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.
To move a data or log file as part of a planned relocation, follow these steps:
For each file to be moved, run the following statement.
ALTER DATABASE database_name
MODIFY FILE (NAME = logical_name, FILENAME = 'new_path\os_file_name');
Run the following statement to bring the database offline.
ALTER DATABASE 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:
ALTER DATABASE database_name
SET OFFLINE
WITH ROLLBACK IMMEDIATE;
Move the file or files to the new location.
Run the following statement.
ALTER DATABASE database_name
SET ONLINE;
Verify the file change by running the following query.
SELECT name,
physical_name AS CurrentLocation,
state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');
To relocate a file as part of a scheduled disk maintenance process, follow these steps:
For each file to be moved, run the following statement.
ALTER DATABASE database_name
MODIFY FILE (NAME = logical_name, FILENAME = 'new_path\os_file_name');
To perform maintenance, stop the instance of SQL Server or shut down the system. For more information, see Start, stop, pause, resume, and restart SQL Server services.
Move the file or files to the new location.
Restart the instance of SQL Server or the server. For more information, see Start, stop, pause, resume, and restart SQL Server services
Verify the file change by running the following query.
SELECT name,
physical_name AS CurrentLocation,
state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');
If a file must be moved because of a hardware failure, use the following steps to relocate the file to a new location.
Tábhachtach
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.
Stop the instance of SQL Server if it was already started.
Start the instance of SQL Server in master
-only recovery mode by entering one of the following commands at the command prompt.
For the default (MSSQLSERVER) instance, run the following command.
NET START MSSQLSERVER /f /T3608
For a named instance, run the following command.
NET START MSSQL$instancename /f /T3608
For more information, see Start, stop, pause, resume, and restart SQL Server services. For information about Linux, see Start, stop, and restart SQL Server services on Linux.
For each file to be moved, use sqlcmd commands or SQL Server Management Studio to run the following statement.
ALTER DATABASE 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.
Exit the sqlcmd utility or SQL Server Management Studio.
Stop the instance of SQL Server.
Move the file or files to the new location.
Start the instance of SQL Server. For example, run: NET START MSSQLSERVER
.
Verify the file change by running the following query.
SELECT name,
physical_name AS CurrentLocation,
state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');
The following example moves the AdventureWorks2022
log file to a new location as part of a planned relocation.
Make sure you are in the context of the master
database.
USE master;
GO
Return the logical file name.
SELECT name,
physical_name AS CurrentLocation,
state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'AdventureWorks2022')
AND type_desc = N'LOG';
GO
Set the database offline.
ALTER DATABASE AdventureWorks2022
SET OFFLINE;
GO
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.
ALTER DATABASE AdventureWorks2022
MODIFY FILE (NAME = AdventureWorks2022_Log, FILENAME = 'C:\NewLoc\AdventureWorks2022_Log.ldf');
GO
ALTER DATABASE AdventureWorks2022
SET ONLINE;
GO
Verify the new location.
SELECT name,
physical_name AS CurrentLocation,
state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'AdventureWorks2022')
AND type_desc = N'LOG';
Ócáid
Mar 31, 11 PM - Apr 2, 11 PM
An ócáid foghlama SQL, Fabric and Power BI is mó. Márta 31 – 2 Aibreán. Bain úsáid as cód FABINSIDER chun $ 400 a shábháil.
Cláraigh inniuOiliúint
Modúl
Migrate SQL Server workloads to Azure SQL Database - Training
Learn the essentials of Azure SQL Database deployment and migration. Explore its benefits, exclusive features, and migration options while optimizing performance and application connections for a smooth transition to the cloud.
Deimhniú
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.
Doiciméadúchán
Move database files - SQL Server
Learn how to move system and user databases by specifying the new file location in the FILENAME clause of the ALTER DATABASE statement.
Move system databases - SQL Server
Learn how to move system databases in SQL Server.
Move Database using detach & attach (Transact-SQL) - SQL Server
Move a database using detach and attach (Transact-SQL)