Post reply

SQL 2008 R2 to SQL 2005

  • January 24, 2011 at 9:15 am

    #233858

    Hi,

    I am new to sql server 2008R2.

    We have a reporting server on which we have 2 instances, one is sql server 2005 sp3 enterprise edition and other is sql server 2008R2 express edition with advanced services.

    I have a database on the sql 2008 instance which i want to copy it to sql 2005 instance.

    How can i acheive this?

    Thanks.

    Regards,

    Sushant

    Regards
    Sushant Kumar

  • January 24, 2011 at 9:35 pm

    #1277596

    One option is to script out your database from your SQL 2008 server and create it on the SQL 2005 server and move the data. You will only be able to move objects that are compatible with SQL 2005 since you are moving it backward. For example if you have user defined table types those were new to SQL 2008 and can not be created on SQL 2005.

    Backup and restore will not work in this situation since it's an older version.

    I'm also assuming you have a user created database you are wanting to move? If your talking about a system database or reporting services it's a different story.

  • January 25, 2011 at 6:28 am

    #1277785

    To move the data, you can use the Import/Export wizard in SSMS, or you can manually create the package in SSIS.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 25, 2011 at 7:00 am

    #1277816

    I tried to copy data thru import/export data in SSMS on sql 2005 instance but got this error:-

    Execute the transfer with the TransferProvider. (Error)

    Messages

    * ERROR : errorCode=0 description=Transferring columns of data type "date" is not supported. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)

    How should i proceed.

    Regards,

    Sushant

    Regards
    Sushant Kumar

  • January 25, 2011 at 7:05 am

    #1277819

    it looks like you have a column with the datatype DATE in SQL 2008, this is not in SQL 2005 so you will need to change the dataype of the column (probably to datetime or smalldatetime) before you transfer the table across

  • January 25, 2011 at 7:46 am

    #1277850

    see the attachment, I am getting tht error, if i try to change the table column datatype to datetime in sql2008R2.

    🙁

    Regards
    Sushant Kumar

  • January 25, 2011 at 7:49 am

    #1277853

    ALTER TABLE <TableName> ALTER COLUMN <ColumnName> DATETIME

    Don't use the GUI, it recreates tables and takes far longer and far more resources than necessary (hence that warning)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 25, 2011 at 8:06 am

    #1277867

    Thanks GAIL , it helped.

    Regards,

    Sushant

    Regards
    Sushant Kumar

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply