Search This Blog

Thursday, August 23, 2012

How to Move a SharePoint 2010 Content Database to a Different Drive or Specified folder?

The SQL server is running out of drive space. The solution is to move the ContentDB
There are two ways of going about this. 
One is to move to a new server instance.


The second solution is to move the database file to a new drive

To detach and attach the database in the same instance, we don’t need to tell SharePoint to do anything differently. With that said there are a couple of tricks you can use to minimize downtime.


Start by moving only one ContentDB at a time. This will allow the other sites located in other ContentDBs to stay online. Also, setting the ContentDB to read-only and doing a copy backup users can still access the sites and data, but can’t write. This should help to insure data consistency during the move. Just be sure to remove the read-only attribute of the “copied” ContentDB before attaching. 

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.

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\os_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\os_file_name' );

2. Stop the instance of SQL Server or shut down the system to perform maintenance. For more information, seeStopping Services.

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

4. Restart the instance of SQL Server or the server. For more information, see Starting and Restarting Services.

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.

Note:
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$instancename /f /T3608
For more information, see How to: Start an Instance of SQL Server (net Commands).

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\os_file_name' );

For more information about how to use the sqlcmd utility, see Using the sqlcmd Utility.

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

5 comments:

  1. Are you sure you want to ALTER the SharePoint Database??
    Please, think about the support...
    I think it will be better to developp a tools to do same thing but using the SharePoint API.
    Microsoft will not support any direct modifications in SharePoint Databases.
    If your SQL is full... Build a new SQL instance on a different Server and Backup/Restore the collection.
    You can Move a big collection to a new or different Content DB will Powershell but it only if both of this DBs are in the same SQL Instance.
    If you need disk space... Add some additionnal disk ;)

    ReplyDelete
  2. This works very well and offer a free trial, I am not sure if you have to buy it to get your files, but even still, it is worth it if you can get your "important" data. You risk losing your data for good if you try too many things. I use this program regularly for clients.

    http://www.recoverydeletedfiles.com/sharepoint-server-data-recovery.html

    ReplyDelete
  3. If you want to repair corrupt or damaged SharePoint database due to drive failures, saved backup turned bad, drive failures and any other reason, then I would like to suggest you to try http://www.en.sharepointserverrecovery.net to repair .MDF database and recover its objects using Raw and Live SQL Instance mode.

    ReplyDelete
  4. Repair all type corrupt SharePoint data with the help of this third party tool Kernel for SharePoint Server recovery which quickly recover all MDF file items from corrupt database. Download it from: http://www.repairsharepoint.com

    ReplyDelete
  5. When we can not access our Microsoft Sharepoint Database there are many causes behind this. May be whole database get corrupted or May be MDF File get corrupted or May be Sharepoint documents get corrupted. Then use SharePoint Database Repair recover all corrupt, damaged SharePoint Server MDF database and recover its objects using Raw and Live SQL Instance mode.


    ReplyDelete