SELECT name, physical_name AS CurrentLocation, state_desc Verify the file change by running the following query. For example, run NET START MSSQLSERVER in the command-line prompt. For example, run NET STOP MSSQLSERVER in the command-line prompt.Ĭopy the file or files to the new location. Once the sqlcmd session is open, run the following statement once for each file to be moved: ALTER DATABASE database_name MODIFY FILE( NAME = logical_name, FILENAME = 'new_path\os_file_name' )Įxit the sqlcmd utility or SQL Server Management Studio. For more information about using the sqlcmd utility, see Use the sqlcmd Utility. To connect to a named instance on the local server, with Active Directory integration authentication: sqlcmd -S localhost\instancenameįor more information on sqlcmd syntax, see sqlcmd utility.įor each file to be moved, use sqlcmd commands or SQL Server Management Studio to run the following statement. For example, when executing sqlcmd locally on the same server as the default (MSSQLSERVER) instance, and to connect with Active Directory integration authentication, run the following command: sqlcmd Promptly after service startup with trace flag 3608 and /f, start a sqlcmd connection to the server, to claim the single connection that is available. The commands fail when the parameters are not specified as shown.įor the default (MSSQLSERVER) instance, run the following command: NET START MSSQLSERVER /f /T3608įor a named instance, run the following command: NET START MSSQL$instancename /f /T3608įor more information, see Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service. The parameters specified in these commands are case sensitive. For more information, see Startup Parameters and TF3608. Using startup parameter 3608 prevents SQL Server from automatically starting and recovering any database except the master database. Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. Stop the instance of SQL Server if it is started. If the database cannot be started, if it is in suspect mode or in an unrecovered state, only members of the sysadmin fixed role can move the file.
#Mailsteward pro move sql windows
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 The system databases should report the new physical file locations. 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.
#Mailsteward pro move sql full
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.
0 kommentar(er)
