0

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.

  • SQL Agent is not required to use the Copy Database wizard. – user507 Oct 6 '14 at 13:04
  • Why are you moving to a new server with SQL Server 2005??? You should strongly consider moving to SQL Server 2012, at least. SQL Server 2005 is no longer supported by Microsoft. Also, for clarity, is the new server Windows 2008R2 or SQL Server 2008R2? – Max Vernon Oct 6 '14 at 13:27
1

Since you have downtime, best is to use this powershell script- with backup restore option

This script will take care of moving databases, logins, jobs,etc on the new server. make sure to use backup restore switch as opposed to detach/attach.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.