x

Migrating data from SQL Server 2005 to SQL Server 2008

I need to move data from a database in SQL Server 2005 to a database in SQL Server 2008. How do i achieve this? Please help.

more ▼

asked Jun 02, 2010 at 01:30 AM in Default

avatar image

Sushant
11 5 5 7

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

There are a number of options, but it all depends on you requirements.

If you are wanting this to be a regular things, then it maybe worth investigating doing this in SSIS, as advanced tools are available to Extract, Transform and Load data.

more ▼

answered Jun 02, 2010 at 02:27 AM

avatar image

sp_lock
10.8k 27 37 37

(comments are locked)
10|1200 characters needed characters left

by far the simplest is the use of SELECT INTO as Jonlee suggests. To do this you will need to create a linked server object on the 2008 instance referring to the 2005 instance. you then simply write a select statement from the 2008 database such as

SELECT a.fname, a.sname INTO 2008DB.dbo.authors 
from 2005linkedservername.2005DB.dbo.authors as a

This will select fname and sname columns for all the rows in the 2005 database and create a table in the 2008 database with that data in it.

If the table already exists in the 2008 database then use

INSERT INTO 2008DB.dbo.authors  
SELECT a.fname, a.sname  
from 2005linkedservername.2005DB.dbo.authors as a 

It depends on whether you have a need to repeat this process, something more configurable like SSIS qould be the best solution to an ongoing requirement. Again, something Jonlee mentions.

more ▼

answered Jun 02, 2010 at 04:41 AM

avatar image

Fatherjack ♦♦
43.8k 79 99 118

(comments are locked)
10|1200 characters needed characters left

If you're attempting to upgrade the database from 2005 to 2008, simply take a backup of the 2005 database and then restore it on 2008 system. Upgrade the compatibility level of the database and manually update the statistics and you'll be good to go.

If you're just moving bits and pieces of the database or the data, the other guys have it well covered.

more ▼

answered Jun 02, 2010 at 09:30 AM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2170
x10

asked: Jun 02, 2010 at 01:30 AM

Seen: 823 times

Last Updated: Jun 02, 2010 at 04:45 AM

Copyright 2017 Redgate Software. Privacy Policy