Jump to content


Moving your site database

No replies to this topic

#1 t-zwck



  • Super Moderators
  • 759 posts
  • 9822 thanks

Posted 14 July 2009 - 09:07 AM

This guide will explain how to move your site database to another server.

Im using SCCM 2007 (mixed mode), MSSQL 2005 SP3 and Windows 2003 r2 throughout this guide.
My SCCM 2007 Server is called DSGCM1 and my new SQL server is called DSGSQL1

Before you begin make sure you have a MSSQL 2005 server setup with atleast SP2. (MSSQL Express is not supported)

Before starting the migration you need to check the following:
1. Make sure you are using the same collation settings on booth your MS SQL servers
This is how to check this:
1. Open Microsoft SQL Server Management Studio
2. Connect
3. Right click on your servername choose properties
4. It will look like this:
Attached File  1post-2500-1234340780.jpg   72.86K   41 downloads

If you don´t do this you might end up with allot of status messages saying something like this:
Microsoft SQL Server reported SQL message 468, severity 16: [42000][468][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Finnish_Swedish_CI_AS"”

2. Make sure you have enabled CLR intergration on the SQL server you are planning to move your DB to.
This is how to check this:
1.Open SQL Server Surface Area Configuration. Path:
Attached File  2post-2500-1234346277.jpg   57.78K   45 downloads
2. Press the link "Surface Area Configuration for Features"
Attached File  3post-2500-1234346654.jpg   87.47K   46 downloads
3. Enable CLR intergration
Attached File  4post-2500-1234346488.jpg   48.89K   41 downloads
4. Press Apply and your all done.

If you don´t do this you might end up with status messages like these ones:
Microsoft SQL Server reported SQL message 6263, severity 16: [42000][6263][Microsoft][ODBC SQL Server Driver][SQL Server]Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option. : RoamingBoundaryIPRange_del"
3. Make sure you have atleast Servicepack 2 installed on your new SQL 2005 Server.
This is how to check this:
Run the following query in microsoft SQL Management Studio (press "execute to run the query")
SERVERPROPERTY ('productlevel'), 
Attached File  5post-2500-1234354512.jpg   109.01K   36 downloads

When you have done all of these check you can go ahead and move your DB.

This is how to move your site Database to another server:

Begin with putting the SQL Primary site computer account in the Administrators group on your SQL server (the one you are moving your db to)
Attached File  6post-2500-1234350705.jpg   87.07K   32 downloads

Then backup your site datbase by going into Microsoft SQL Management Studio. Expand the databases node and rightclick on your site database. Choose "tasks" --> "Back Up.."
Attached File  7post-2500-1234350899.jpg   79.55K   33 downloads

Choose to backup to disk and backuptype=Full and then press Add button to choose where to save your backup. This will take some time depending on how big your database is.
Attached File  8post-2500-1234351091.jpg   57.53K   31 downloads

Now copy the database from your old server to your new server.
Attached File  9post-2500-1234351204.jpg   102.3K   31 downloads

On your new SQL server open Microsoft SQL Server Management Studio. Rightclick on the databases node and choose "Restore Database..."
Attached File  10post-2500-1234351302.jpg   66.59K   29 downloads

Write the db name in the "to database:" field. Choose "From Device" and then find your backupfile, mine was located at d:sms_dsg. When file is found make sure your check the restore radio button. Press OK to begin importing the database.
Attached File  11post-2500-1234351718.jpg   82.74K   32 downloads

Wait for this message to appear:
Attached File  12post-2500-1234351760.jpg   79.58K   31 downloads

Now go to your SCCM 2007 server and find the file "preinst.exe". Should be located where you have installed your SCCM.
Attached File  13post-2500-1234351897.jpg   65.95K   30 downloads

Now that you know where your preinst.exe file is located you can run it from the commandprompt with the following switch: /stopsite
In my case the command looked like this: "f:MICROSOFT CONFIGURATION MANAGERbini38600000409preinst.exe" /stopsite
Attached File  14post-2500-1234352198.jpg   32.07K   28 downloads

You will get a few messages like these (wait for them all to finish):
Attached File  15post-2500-1234352356.jpg   158K   30 downloads

On your SCCM site server open "All programs --> Microsoft System Center --> Configuration manager 2007 --> ConfigMgr Setup"
Attached File  16post-2500-1234352785.jpg   78.48K   29 downloads

Click next on the first Wizardpage:
Attached File  17post-2500-1234352826.jpg   66.98K   30 downloads

Choose to "perform site maintenance or reset this Site". Click next.
Attached File  18post-2500-1234352874.jpg   55.37K   29 downloads

Choose "Modify SQL Server Configuration". Click next.
Attached File  19post-2500-1234352972.jpg   41.62K   30 downloads

Change "SQL Server and instance, if applicable:". Wite the name of your new SQL server here. Databasename should still be the same! Click Next
Attached File  20post-2500-1234353117.jpg   46.83K   29 downloads

Will look something liek this:
Attached File  21post-2500-1234353166.jpg   52.43K   29 downloads

When all is completed press next.
Attached File  22post-2500-1234353204.jpg   55.18K   28 downloads

Press Finish or view the log if you want  ;)
Attached File  23post-2500-1234353274.jpg   41.31K   29 downloads

Now restart your server!
Attached File  24post-2500-1234353479.jpg   23.8K   31 downloads

Open ConfigMgr Console and expand "Site Database --> Site management --> Site (in my case DSG - DSG primary Site) --> Site settings --> Site Systems". right click on ConfigMgr site system and choose properties. enter a FQDN for the new server, in my case dsgsql1.dsgdomain.local. Press OK.
Attached File  25post-2500-1234353672.jpg   136.29K   30 downloads

Now reset all your status messages

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users




Test Providers

Site Info

Go to top