Moving SharePoint 2013 Sites, Central Administration Console from one SQL Server to another SQL server

Please follow below steps in moving the SharePoint database from one server to another.

    1. Stop all the processes that might be accessing your SharePoint databasesFor ex:
      • SharePoint 2010 Administration
      • SharePoint 2010 Timer
      • SharePoint 2010 Tracing
      • SharePoint 2010 User Code Host
      • SharePoint 2010 VSS Writer
      • SharePoint Foundation Search V4
      • World Wide Web Publishing Service
      • SharePoint Server Search 14
      • Web Analytics Data Processing Service
      • Web Analytics Web Service


      runningservices

    2. Back up all your SharePoint databases on SQL Server A (this is in any case if anything
      went wrong).
    3. Deattach all your databases from SQL Server A.


deattache

    1. Move all the .mdf and .ldf file from SQL Server A to SQL Server B.
    2. Place them in the proper DATA folder on SQL Server B.
    3. Make sure the same structure of user accounts with proper privileges that was on
      SQL Server A to be created on SQL Server B.
    4. Then attach the databases on SQL Server B.


attach

    1. Modify the SQL alias on SharePoint that points SQL Server A to SQL Server B.


sqlclientutility

while i was detaching some of the databases was getting the error saying (Cannot detach the database ‘DEMO’ because it is currently in use.) that still some users are connected to the database. To get rid of that problem, executed the following SQL statements:

–Kick all users off of the database NOW
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

–Kick all but after 60 seconds
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS

–restore connection to users
ALTER DATABASE YourDatabase SET MULTI_USER

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: