Sql-server – moving all databases from SQL 2005 EXPRESS SP2 to SQL2005 Enterprise SP4

sql serversql-server-2005

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 Answer

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.

Related Solutions

Sql-server – Failed Upgrade from SQL Server 2005 sp2 to SQL Server 2005 sp4

Almost every time I've had an SP installation issue on a stand-alone server, it was because the resource database was moved or it was not in the same directory as the master database.

Many articles I'm finding correlate your error to this exact conclusion including this one.

I would start there and if you find it to be the case, move the system databases back to where the resource database is.

If that doesn't work, try re-applying SP2. If all of the components weren't patched during the SP2 upgrade, you could experience this issue.

Lastly, verify service account permissions and permissions of the account you are running the SP4 with.

Sql-server – Migrate database from SQL Server 2005 to SQL Server 2008 R2

In order.

Some easy options: You could look at the start->run services.msc and see which services are there; you could run powershell and look at run gsv *SQL*; or you could look at the Start menu->Microsoft SQL Server 2008 R2 and see what's there.
If you're a database newbie, I would take a look at the excellent database maintenance scripts from Ola Hallengren. These cover backups, index maintenance and database integrity checks.
I would migrate the database by taking a backup of the old database and restoring that to the new server - this will bring all objects, tables, data, stored procedures etc. However, you'll then have to migrate any appropriate scheduled tasks and user IDs. There are some SQL Server Integration Services tasks that'll help with jobs and logins (indeed, they'll also help with the data migration...).

Lastly, welcome to the job. I would recommend that you have a trawl through Red Gate's bookstore - there are a lot of free PDF versions of their books. Have a skim of SQL Server Backup and Restore, Troubleshooting SQL Server for the Accidental DBA.

Good luck!

edit. A pair of thoughts, relating to your migration from 2005 to 2008 R2 specifically.

You should be OK doing this by using database backup and restore - good news.
However, be aware that it is entirely possible that some features your app relies on may be deprecated / discontinued. Once you get up & running on SQL Server 2008 R2, you can keep an eye out for these features using the sys.dm_os_performance_counters DMV. There are various blog posts out there, but the gist is:
```
SELECT * FROM sys.dm_os_performance_counters
WHERE Object_name like '%:Deprecated Features'
AND cntr_value > 0
```

(unfortunately, tracking Deprecated Features wasn't part of the SQL 2005 release...)

Related Question