For more information, see Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service. Stop the instance of SQL Server or shut down the system to perform maintenance. ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' ) To relocate a file as part of a scheduled disk maintenance process, follow these steps:įor each file to be moved, run the following statement. Relocation for Scheduled Disk Maintenance SELECT name, physical_name AS CurrentLocation, state_desc Verify the file change by running the following query. Move the file or files to the new location.
#YUMMYSOUP DATABASE FILE OFFLINE#
For example, to automatically rollback and disconnect all other connections to the database, use: ALTER DATABASE database_name SET OFFLINE WITH ROLLBACK IMMEDIATE To override this behavior, use the WITH clause. If another connection is open to the database, the ALTER DATABASE statement will be blocked until all connections are closed. This action requires exclusive access to the database. ALTER DATABASE database_name SET OFFLINE Run the following statement to bring the database offline. To move a data or log file as part of a planned relocation, follow these steps:įor each file to be moved, run the following statement. For more information, see Configure File System Permissions for Database Engine Access. Make sure the service account for the SQL Server Database Services service has permissions to the new file location in the file system. The full-text catalogs now move automatically when you move a database. Starting with SQL Server 2008 R2 (10.50.x), full-text catalogs are integrated into the database rather than being stored in the file system. To obtain the name, query the name column in the sys.master_files catalog view. The procedures in this article require the logical name of the database files. Use the sys.dm_db_persisted_sku_features dynamic management view to list all edition-specific features that are enabled in the current database. A database that contains these features cannot be moved to an edition of SQL Server that does not support them. These features are restricted to specific editions of SQL Server. Some features of the SQL Server Database Engine change the way that the Database Engine stores information in the database files. For more information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server). When you move a database onto another server instance, to provide a consistent experience to users and applications, you might have to re-create some or all the metadata for the database. For moving system database files, see Move System Databases. This article covers moving user database files.
0 kommentar(er)
