I need to move the SQL data and log files to another location. How can I do this?

There are some situations where we need to move the SQL Server data and log files to a new directory or even to another disk. This procedure can be done safely, both in system and user databases. The only exception is the system database “Resource”, which is located in “<drive>:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance name>\MSSQL\Binn\” and cannot be moved . To move the other databases, follow the steps below:

MOVING SYSTEM DATABASES

1. For each file to be moved, except for the files from databases “master” and “Resource”, run the following statement:

ALTER DATABASE <database name> MODIFY FILE (NAME = <logical name>, FILENAME = '<new path>\<file name>')

2. Stop the instance of SQL Server.

3. Move the files to the new location.

4. Restart the instance of SQL Server.

5. 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>');

6. If the “msdb” database is moved and the instance of SQL Server is configured for “Database Mail”, complete these additional steps:

  a. Verify that Service Broker is enabled for the “msdb” database by running the following query:

SELECT is_broker_enabled

FROM sys.databases

WHERE name = N'msdb';

  b. Verify that Database Mail is working by sending a test mail.

MOVING THE “MASTER” DATABASE

To move the “master” database, follow these steps:

1. From the “Start” menu, point to “All Programs”, point to “Microsoft SQL Server”, point to “Configuration Tools”, and then click “SQL Server Configuration Manager”.

2. In the “SQL Server Services” node, right-click the instance of SQL Server (for example, “SQL Server (MSSQLSERVER)”) and choose “Properties”.

3. In the service properties dialog box, click the “Startup Parameters” tab.

4. In the “Existing parameters” box, select the “–d” parameter to move the “master” data file. Click “Update” to save the change. In the “Specify a startup parameter” box, change the parameter to the new path of the “master” database.

5. In the “Existing parameters” box, select the “-l” parameter to move the “master” log file. Click “Update” to save the change. In the “Specify a startup parameter” box, change the parameter to the new path of the “master” database.

The parameter value for the data file should follow the “-d” parameter and the value for the log file should follow the “-l” parameter. The following example shows the parameter values for the default location of the “master” data file.

-dC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\master.mdf

-lC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

If the planned relocation for the “master” data file is “E:\SQLData”, the parameter values would be changed as follows:

-dE:\SQLData\master.mdf

-lE:\SQLData\mastlog.ldf

6. Stop the instance of SQL Server by right-clicking on the instance name and choosing “Stop”.

7. Move the “master.mdf” and “mastlog.ldf” files to the new location.

8. Restart the instance of SQL Server.

9. Verify the new settings by running the following query:

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID('master');

GO

FOLLOW-UP: AFTER MOVING ALL SYSTEM DATABASES

If all of the system databases were moved to a new drive, volume or to another server with a different drive letter, make the following updates:

• Change the “SQL Server Agent” log path. If you do not update this path, “SQL Server Agent” will fail to start.
• Change the database default location. Creating a new database may fail if the drive letter and path specified as the default location do not exist.

To change the “SQL Server Agent” Log Path

1. From “SQL Server Management Studio”, in “Object Explorer”, expand “SQL Server Agent”.

2. Right-click “Error Logs” and click “Configure”.

3. In the “Configure SQL Server Agent Error Logs” dialog box, specify the new location of the “SQLAGENT.OUT” file. The default location is “C:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance name>\MSSQL\Log\”:

To change the database default location

1. From “SQL Server Management Studio”, in “Object Explorer”, right-click the SQL Server server and click “Properties”.

2. In the “Server Properties” dialog box, select “Database Settings”.

3. Under “Database Default Locations”, browse to the new location for both the data and log files:

4. Stop and start the SQL Server service to complete the change.

MOVING USER DATABASES

In SQL Server, we 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 of 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.

CONSIDERATIONS

When we move a database onto another server instance, to provide a consistent experience to users and applications, we might have to re-create some or all the metadata for the database. For more information, see “Manage Metadata When Making a Database Available on Another Server Instance (SQL Server)” (https://msdn.microsoft.com/en-us/library/ms187580.aspx).

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 cannot be moved to an edition of SQL Server that does not 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 topic require the logical name of the database files. To obtain the name, query the “name” column in the “sys.master_files” catalog view:

Starting with SQL Server 2008 R2, “full-text” catalogs are integrated into the database rather than being stored in the file system. The “full-text” catalogs now move automatically when you move a database.

PLANNED RELOCATION PROCEDURE

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

1. Run the following statement:

ALTER DATABASE <database name> SET OFFLINE;

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

3. For each file moved, run the following statement:

ALTER DATABASE <database name> MODIFY FILE (NAME = <logical name>, FILENAME = '<new path>\<file name>');

4. Run the following statement:

ALTER DATABASE <database name> SET ONLINE;

5. 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>');

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:

ALTER DATABASE <database name> MODIFY FILE (NAME = <logical name>, FILENAME = '<new path>\<file name>');

2. Stop the instance of SQL Server or shut down the system to perform maintenance. For more information, see “Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service” (https://msdn.microsoft.com/en-us/library/hh403394.aspx).

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

4. Restart the instance of SQL Server or the server. For more information, see “Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service” (https://msdn.microsoft.com/en-us/library/hh403394.aspx).

5. 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>');

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.

However, if the database cannot be started, that is it is in suspect mode or in an unrecovered state, only members of the “sysadmin” fixed role can move the file.

1. Stop the instance of SQL Server if it is started.

2. 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$<instance name> /f /T3608

For more information, see “Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service” (https://msdn.microsoft.com/en-us/library/hh403394.aspx).

3. 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>\<file name>');

For more information about how to use the “sqlcmd” utility, see “Use the sqlcmd Utility” (https://msdn.microsoft.com/en-us/library/ms180944.aspx).

4. Exit the “sqlcmd” utility or SQL Server Management Studio.

5. Stop the instance of SQL Server.

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

7. Start the instance of SQL Server. For example, run: “NET START MSSQLSERVER”.

8. 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>');

For more information, see the articles below:

• Move System Databases: https://msdn.microsoft.com/en-us/library/ms345408.aspx
• Move User Databases: https://msdn.microsoft.com/en-us/library/ms345483.aspx

About the Error 35250

Error 35250 is a common failure can occur when attempting to create an availability group using either method. The error is as follows and is caused because the target primary and secondary replicas are unable to communicate:

Msg 35250, Level 16, State 7, Line 1

The connection to the primary replica is not active.  The command cannot be processed.

If creating the availability group using the New Availability Group wizard, the 'Joining <availability database> to availability group <availability group> at <replica>' will fail. Clicking the Error link, message 35250 is reported:

 

If you used transact-sql to create the availability group instead of the New Availability Group wizard, the ALTER DATABASE...SET HADR AVAILABILITY GROUP... command fails with message 35250:

 

Following is a list of common causes for this failure and steps to diagnose each.

CAUSE 1: INBOUND PORT 5022 TRAFFIC IS BLOCKED

IMPORTANT: By default, AlwaysOn configures the database mirroring endpoints to use port 5022, and the following document will use 5022 when discussing the database mirroring endpoint in use for availability groups. However, this may not be the configured port for the database mirroring endpoints in your environment.  Query sys.tcp_endpoints on each replica to confirm which port is being used:

select name, type, port from sys.tcp_endpoints where type_desc='database_mirroring'

go

By default inbound traffic is blocked in Windows firewall.

 

 

Port 5022 is used by the primary and secondary replicas for synchronization and communication purposes. Traffic must be allowed inbound on this port. Testing has shown that if port 5022 inbound traffic is blocked at the primary, at the secondary or at both, you will be unable to create the availability group and message 35250 will be reported.

For additional settings of SQL Server in Windows Firewall, please follow the article “Configure a Windows Firewall for Database Engine Access” (https://technet.microsoft.com/en-us/library/ms175043(v=sql.110).aspx).

CAUSE 2: ENDPOINT IS NOT CREATED OR STARTED

Ensure the mirroring endpoints are created and started on the primary and the secondary replicas.

 

 To detect if the endpoints are created, query for database_mirroring endpoints at the primary and the secondary.

:Connect SQLNODE1

select name, state_desc, port from sys.tcp_endpoints where type_desc='DATABASE_MIRRORING'

go

:Connect SQLNODE2

select name, state_desc, port from sys.tcp_endpoints where type_desc='DATABASE_MIRRORING'

go

If you find an endpoint does not exist, create it:

:Connect SQLNODE1

create endpoint [Hadr_endpoint]

 state=started

as tcp (listener_port = 5022, listener_ip = all)

for database_mirroring (role = all, authentication = windows negotiate, encryption = required algorithm aes)

go

To detect if the endpoints are started, query them at the primary and the secondary.

:Connect SQLNODE1

select name, state_desc, port FROM sys.tcp_endpoints where name='hadr_endpoint'

go

:Connect SQLNODE2

select name, state_desc, port from sys.tcp_endpoints where name='hadr_endpoint'

go

If you find an endpoint is not running, start it:

:Connect SQLNODE1

alter endpoint [Hadr_endpoint] state = started

IMPORTANT: There is a known issue in which sys.tcp_endpoints.state_desc may incorrectly report the endpoint as STARTED when it is not started. It is a good idea to execute the ALTER ENDPOINT command for the endpoint at each replica, despite the report given by sys.tcp_endpoints, queried above.

CAUSE 3: ENDPOINT PERMISSIONS

If database mirroring endpoints are configured to use Windows authentication, ensure that the SQL Server instances hosting your availability replicas run with a SQL Server startup account are domain accounts.

The New Availability Group wizard catches this misconfiguration, and reports it. Note that the Endpoints tab is displayed and the SQL Server Service Account reports that one of the replica's startup account is configured for LocalSystem.

If you proceed by clicking Yes and complete the wizard, it will fail during the 'Joining <availability database> to availability group <availability group> at <replica>.'

If you are attempting to create an availability group using transact-sql, this configuration problem will not be detected and command ALTER DATABASE...SET HADR AVAILABILITY GROUP...  will fail with 35250.

CAUSE 4: ADDITIONAL ENDPOINT PERMISSIONS

If the instances of SQL Server run as the Network Service account, the login of the each host computer account (DomainName\ComputerName$) must be created in master on each of the remote server instances and that login must be granted CONNECT permissions on the endpoint. This is because a server instance running under the Network Service account authenticates using the domain account of the host computer.

Here are steps to identify the endpoint and how to give CONNECT permissions to a computer account.

To identify the endpoint:

USE master;

GO

SELECT name, state_desc, port from sys.tcp_endpoints where type_desc='DATABASE_MIRRORING'

GO

To give CONNECT permissions on the endpoint for SQL Servers. Execute below for each server on each server:

USE master;

GO

CREATE LOGIN [Contoso\SQL1$] FROM WINDOWS;

GO

GRANT CONNECT on ENDPOINT::Hadr_endpoint TO [Contoso\SQL1$];

GO

CAUSE 5: SQL SERVER IS NOT LISTENING ON PORT 5022

If SQL Server is unable to listen on port 5022, the New Availability Group wizard and the transact-sql command ALTER DATABASE...SET HADR AVAILABILITY GROUP...  will fail with 35250.

To determine if SQL Server is listening on port 5022, review the SQL Server error log. You should find the following message(s) in the SQL Server error log:

2013-12-09 08:52:25.47 spid23s     Server is listening on [ 'any' <ipv6> 5022].

2013-12-09 08:52:25.47 spid23s     Server is listening on [ 'any' <ipv4> 5022].

SQL Server may not be able to listen on port 5022 if another application is already listening on the port. If you find that SQL Server is not listening on port 5022 because it is already being used, run 'netstat -a' to determine what application is using the port.

ADDITIONAL DIAGNOSTICS - QUERY SYS.HADR_AVAILABILITY_REPLICA_STATES ON THE SECONDARY

If the above settings check out, query the sys.dm_hadr_availability_replica_states for the last_connect_error_number which may help you diagnose the join issue. Depending on which replica was having difficulty communicating, you should query both the primary and secondary for the local replica:

select r.replica_server_name, r.endpoint_url,

rs.connected_state_desc, rs.last_connect_error_description,

rs.last_connect_error_number, rs.last_connect_error_timestamp

from sys.dm_hadr_availability_replica_states rs join sys.availability_replicas r

on rs.replica_id=r.replica_id

where rs.is_local=1

For example, if the secondary was unable to communicate with the DNS server or if a replica's endpoint_url was configured incorrectly when creating the availability group, you may get the following results:

 

SOURCE:

• Create Availability Group Fails With Error 35250 'Failed to join the database': http://blogs.msdn.com/b/alwaysonpro/archive/2013/12/09/trouble-shoot-error.aspx
• SQL AlwaysOn: Failed to join the database to the Availability Group (Error 35250): http://blogs.msdn.com/b/svarukala/archive/2014/03/31/sql-alwayson-failed-to-join-the-database-to-the-availability-group-error-35250.aspx

I lost my administrative privileges in SQL Server. What about now?

As a result of some failure or operational error, sometimes we may encounter a user who lost administrative privileges in SQL Server. To make matters worse, often this was the only user with administrative privileges on the database. And now, how to solve it?

Fortunately, there is a way to reactivate the administrator privilege to this user, since this user is also a Windows administrator user. Confirmed this hypothesis, let’s go to the steps to re-enable the administrator privileges for this user in SQL Server:

1. Log on to Windows with the user who lost access to SQL Server. Keep in mind that this user must have administrator privileges on Windows.


2. Restart the service in SQL Server by adding the “-mSQLCMD” parameter, using the SQL Server Configuration Manager. This parameter allows access to SQL Server only through the SQLCMD:
   
   
   
   
   
3. Connect to SQL Server through the SQLCMD:
   
   


4. Add the user login to the role “sysadmin”, through the following command:
   
   
   ALTER SERVER ROLE [sysadmin] ADD MEMBER [<domain>\<login>];
   GO
   
   
5. Remove the parameter “-mSQLCMD” of the SQL Server service startup.


6. Restart the SQL Server service.

For more information, see the articles below:

• Connect to SQL Server When System Administrators Are Locked Out: https://msdn.microsoft.com/en-us/library/dd207004.aspx
• ALTER SERVER ROLE (Transact-SQL): https://msdn.microsoft.com/en-us/library/ee677634.aspx