October 4, 2014 at 6:21 am
Hello all
Thank you for such a great forums on web.
I'm not a DBA specialist and my knowledge about SQL server is low so this simple issue is getting challenging for me.
We have an old SQL Server express 2005 SP2 on WINDOWS 2003 platform.
We want to copy all of the databases to our new server which is 2008R2 and it is suggested to have SQL Server SP4 to have compatibility on 2008R2
My question is this:
How to move/copy all of the credentials and databases and the whole data from OLD server to new server?
We can have 4-5 hours of downtime and i have only 100 databases.
I want to use the easiest way and the must simple procedure to do this job.
** A friend of mine told me SQL Server management studio 2012 has a built in COPY DATABASE wizard which easily copy data from source to destination server. But it needs SQL Agent be installed on the source server. Since the source server is EXPRESS edition so we don't have any AGENT there.
Do you have any easy to apply solution? I will be thankful if you guide me in details since i'm not a DBA specialist and my main work area is on other web services expect MSSQL.
I have also installed SP2 on the new 2008R2 server and i didn't find any issue with that.
Thank you for your time on my case.
Best Regards
October 8, 2014 at 1:47 pm
Hi,
The easiest way to do this type of migration is Backup old DB, Copy Backup to new server, Restore on new server.
It is a simple process but there are many ways to make mistakes and when you have 100 db's that gives you 100X opportunities to stumble:w00t:
First for the backups. How to Automate the Backup of a SQL Server 2005 Express Data Store is about SQL 2005 Express on Citrix but the process is the same. Once you do it a couple of times you can script and automate the process pretty easily.
File copy is File copy, or perhaps use a SAN or Network Share.
Restore on SQL 2008R2 is pretty straight forward. Step through the restore wizard and then generate a restore script (option at top of the wizard). You can parameterize the generated script to automate restores.
You don't have to do all at once (unless you application requires all 100 dbs). You can test the process by moving a couple of dbs to "pre-production". I would run through the whole process at least 3 or 4 times.
Using this approach you can minimize overall downtime (again if your application supports it). Any given db is only down from the time of it's last backup on the SQL 2005 side to the time it is restored on SQL 2008R2.
I have done several migrations like this and I urge you to do the test cycle. The upgrade from 5 to 8 is mostly seamless but there are potential issues depending on how the database is designed and implemented.
The credentials are a whole 'nother kettle. It depends on whether the apps use SQL Logins or Windows Security. Security Groups or individual logins.
While the process is "simple" you might consider consulting with a good dba for the project. You should be able to find someone through a local SQL Server User Group
Good luck.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply