none
How do I move database (tables and content) from SQL Server 2008 to SQL Server 2012 Express

    Question

  • Hey all.

    I'm trying to move my database from one machine to another so I can test connecting to it via my application.  Unfortunately, since the version I installed on the other machine is an Express edition, I'm not finding an easy way to do this.  I figured there was a way to 'Generate Scripts', but the best that can do is re-create the tables on the destination computer (by physically copying the .sql file to the destination, then executing it.)   That's fine, but I'd really like to move my test data over as well.  

    Is there a way to make this happen?

    Edit: The database, with the test data, is only about 3mb, so size constraints shouldn't be an issue.
    Monday, June 11, 2012 5:16 PM

Answers

  • Hi Rockstarter,

    >>Not sure the versions of the Sql Servers
    Please check your version of SQL Server on source and destination machine using following command in SQL Server Management Studio.

    select @@version

    The outputs of the command are like below:

    -SQL Server 2008 R2: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)   Apr 22 2011 19:23:43   Copyright (c) Microsoft Corporation  Data Center Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
    -SQL Server 2012 Express: Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) Feb 10 2012 19:13:17 Copyright © Microsoft Corporation Express Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

    >>"The media family on device 'Path' is incorrectly formatted. Server can not process this family."

    Please check if backup file of database is good, also ensure backup file doesn’t get corrupted. For more details, you can refer to here.

    There is another way to move data, we recommend that you can use Import/Export data wizard in SQL Server for moving data.For more details, you can refer to here.


    Best Regards, Ray Chen


    Wednesday, June 13, 2012 7:51 AM
  • Shulei,

    Thanks for the tip on finding the version.  It looks like we already have Sql Server 2005 installed on that machine and that's been the Database engine I've been connecting to all along, even tough I had recently installed the 2012 Express edition on it as well.

    Now that that's been identified I think I'm going to test just creating a new database on the 2005 Database Engine machine, and accessing it from this development computer via Management Studio, and then see if I can access the new database via code.  If I can, I'll just focus on creating the new database structure and all and just keeping it always on the server instead of locally for off-line development.

    Wednesday, June 13, 2012 3:31 PM

All replies

  • Update:

    I tried doing a Backup/Restore.

    Backup: I went to my SQL Server 2008 and did a backup of the database.  I right-clicked the Database, selected Tasks, and opened Backup.  Then, I decided to do a Full backup of my Database.mdf (the .mdf is an artifact from when I originally created the database in Visual Studio, then migrated it to Sql Server 2008).  The Backup Set was automatically populated, as was the backup destination.  For options, I said I wanted it to overwrite all existing backup sets.  The .bak was copied to the the other computer.

    Restore: In Sql Server 2012 Express, I clicked on 'Databases' in my Object Explorer and selected 'Restore Database'... the window that opened looked different than the one I'm used to seeing in 2008.  First, I was asked for the Source for Restore, so I selected 'From Device' and pointed to the .bak file.  This is where things started acting wonky.  No Backup Set's show up in the listing.  I went back into the 'Specify Backup' window and clicked on the file I'm trying to backup from, and I clicked the contents button.

    "The media family on device 'Path' is incorrectly formatted.  Server can not process this family."

    Confused, I went back to Sql Server 2008 and went to the restore database process.  Sure enough, when I select the file I'm able to select the backup set.

    If anyone has any idea as to what I'm doing wrong please share your thoughts.  I'll continue to update this thread with and progress or findings I should happen upon.

    Monday, June 11, 2012 6:06 PM
  • Are you using the same version between your source and destination? For example, if your source is SQL Server 2008 R2 with service pack 1 (build 10.50.2500,) the destination has to be either the same version or higher. Otherwise, you won`t be able to restore your backups

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn

    Monday, June 11, 2012 9:23 PM
  • The transition of the database file went like this.

    1) Created a local database in Visual Studio 2010 (.mdf) and put it in my App_Data folder.
    2) Eventually moved the database to a local version of Sql Server 2008.
    3) Just tried moving it to Sql Server 2012 Express, which is installed on a separate machine.

    Not sure the versions of the Sql Servers but each time (except from VS to Sql Server), there is a step up in release year.

    Tuesday, June 12, 2012 12:19 AM
  • Hi Rockstarter,

    >>Not sure the versions of the Sql Servers
    Please check your version of SQL Server on source and destination machine using following command in SQL Server Management Studio.

    select @@version

    The outputs of the command are like below:

    -SQL Server 2008 R2: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)   Apr 22 2011 19:23:43   Copyright (c) Microsoft Corporation  Data Center Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
    -SQL Server 2012 Express: Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) Feb 10 2012 19:13:17 Copyright © Microsoft Corporation Express Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

    >>"The media family on device 'Path' is incorrectly formatted. Server can not process this family."

    Please check if backup file of database is good, also ensure backup file doesn’t get corrupted. For more details, you can refer to here.

    There is another way to move data, we recommend that you can use Import/Export data wizard in SQL Server for moving data.For more details, you can refer to here.


    Best Regards, Ray Chen


    Wednesday, June 13, 2012 7:51 AM
  • Shulei,

    Thanks for the tip on finding the version.  It looks like we already have Sql Server 2005 installed on that machine and that's been the Database engine I've been connecting to all along, even tough I had recently installed the 2012 Express edition on it as well.

    Now that that's been identified I think I'm going to test just creating a new database on the 2005 Database Engine machine, and accessing it from this development computer via Management Studio, and then see if I can access the new database via code.  If I can, I'll just focus on creating the new database structure and all and just keeping it always on the server instead of locally for off-line development.

    Wednesday, June 13, 2012 3:31 PM