When creating a web application in SharePoint 2013 it will communicate with SQL Server to create the supporting content database. Depending on what steps are used to create the Web Apps it sometimes creates the SharePoint database with a not-so-‘friendly’ database name and it may contain a ginormous GUID, (which I pronounce Goo-id) as part of the database name. When working with these databases it can become challenging to specify the name of the database within PowerShell or SQL Server so you’ll want to rename the database to a more user-friendly name. You may also decide you need to move the database to a different location on your hard drives, but you don’t want to recreate the Web App.
This blog article is going to walk you through the steps you can take to move or rename these databases.
There are six general steps required to either move or rename SharePoint Web App content databases:
1. Take the database offline and remove it from within SharePoint.
2. Backup the Database (Optional step, but I strongly recommend it, in case there are problems).
3. Detach the database from within SQL Server.
4. Rename the database files (.ldf, and .mdf) associated with the database and rename the database to match the database file names. Or if you are moving the database files to a new location, copy the database files to the new location.
5. In SQL Server attach the moved and/or renamed database back to SQL Server.
6. Add the renamed or moved database back to Web Application.
In SharePoint:
1. Central Administration->Application Management->Manage Content Databases.
2. Choose the correct Web Application using the Web Application dropdown box on the right side of screen.
3. Click the name of the database that you want to move or rename.
4. Click from Ready to Offline in the Database Status dropdown.
5. Click checkbox next to Remove content database (This is not going to delete the DB in SQL).
6. Click Ok on the warning popup box indicating sites will no longer be available.
7. Click Ok.
In SQL Server:
1. Locate and right-click on the current database name in SQL Server Management Studio.
2. Click Tasks, then click Back Up… to open the Back Up Database configuration dialog box.
3. Verify Backup Type is Full, and that ‘database‘ is selected within database component area. Make a note of the location of where the backup file will be created, then Click Ok to complete the backup of content database.
4. Right-click the current database name in SQL Server Management Studio and click Properties, then click Files, then locate and note the Path of each database file located in the details pane of the Database Properties window. (you will be renaming and/or moving these files later)
5. Right-click the current database name in SQL Server Management Studio and click Tasks, then Detach…
6. In the Detach Database dialog box, select the Drop Connections checkbox and click Ok.
Note: The current database name will no longer be available in SQL Server Management Studio.
In Windows Explorer:
1. Open Windows Explorer and locate the path of the database files you noted in the earlier step.
2. Locate the files you noted earlier and right-click the files and rename to reflect the new database name. (It is best practice to keep the default file extensions of these files) If you are moving the database files, copy the database files to the new location.
3. Close Windows Explorer and return to SQL Server Management Studio.
In SQL Server:
1. In the left pane of the SQL Server Management Studio, right-click Databases and click Attach…
2. Click the Add button and locate the directory that contains the .mdf file you previously located in Windows Explorer and click on it to highlight it. You will see the .ldf file was also added to the files associated with this database.
3. Click the filename in the Attach As area within the Databases to attach section and specify the new database name to be seen in SQL Server and SharePoint
4. In the details pane of the Attach Database dialog box, under the Current File Path section, locate the new path, and click on the new file names of both the .mdf and .ldf files. Then click Ok.
In SharePoint:
1. Central Administration->Application Management->Manage Content Databases:
2. Choose the correct Web Application using the Web Application dropdown box on the right side of screen
3. Click Add a Content Database
4. Type in the new database name in the Database Name box and click Ok
Congratulations, you have now successfully renamed and/or moved the SharePoint content database and its associated files.
Oh don’t forget to delete the backup you created in the earlier steps and say bye to those GUIDS!