vurmode.blogg.se

The following examples use the Windows command-line prompt and sqlcmd Utility. This procedure applies to all system databases except the master and Resource databases. If a file must be moved because of a hardware failure, follow these steps to relocate the file to a new location. Verify that Database Mail is working by sending a test mail. SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE Ĭonfirm that the value of is_broker_enabled is now 1. For more information, see ALTER DATABASE.

If the Service Broker is not enabled for msdb, it must be re-enabled for Database Mail to function. Verify that Service Broker is enabled for the msdb database by running the following query. If the msdb database is moved and Database Mail is configured, complete the following additional steps. Since in Step 5 you copied the database files instead of moving them, now you can safely delete the unused database files from their previous location.įollow-up: After moving the msdb system database SELECT name, physical_name AS CurrentLocation, state_desc The system databases should report the new physical file locations. Verify the file change by running the following query. For more information, see Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service. Restart the instance of SQL Server or the server. Note that this is not a necessary step for the tempdb system database, those files will be created in the new location automatically. For more information, see Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service.Ĭopy the database file or files to the new location. Stop the instance of SQL Server to perform maintenance. Until the service is restarted, the database continues to use the data and log files in the existing location.

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' ) If the Database Engine service account cannot control the files in their new location, the SQL Server instance will not start.įor each database file to be moved, run the following statement. For more information, see Configure Windows Service Accounts and Permissions. Verify that the service account for the SQL Server Database Engine has full permissions to the new location of the files. Record the existing location of the database files you intend to move, by reviewing the sys.master_files catalog view. See later in this article for steps to move the master database.