SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Backup SQL Express - Restore Sql 2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cboshdave
Starting Member

USA
2 Posts

Posted - 04/10/2014 :  20:19:30  Show Profile  Reply with Quote
Yeah... definitely new here. I am trying to move from SQL Express to SQL 2008. Different Machines. I was finally able to connect to my SQL Express using SQL Mgt Studio. I am connecting to Express with SQL Mgt Studio 2008 and running a backup. Then copying the backup to server B and trying to restore it.

I keep getting - The media family on device 'C:\Backup.BAK' is incorrectly formed. SQL Server cannot process this media family.

From my research, I thought it was a newer version trying to restore to an older version and, in fact, I was accidentally backing up with 2012 and restoring with 2008. I remedied that and am just working with 2008 now. But I am still getting the same error.

Another thing I noted is that when I copy the Backup.BAK file, it has 413,442 kb at the source and was less at the destination. So, I zipped it and copied. Then, it matched. Still get the same error. I am running out of thoughts.

Suggestions?? Possible??

By the way... running simple scripts like the following for backup and restore.
BACKUP DATABASE dbname
TO DISK = 'C:\Backup.BAK'
GO

RESTORE DATABASE dbname
FROM DISK = 'C:\Backup.BAK'
WITH RECOVERY
GO

Edited by - cboshdave on 04/10/2014 20:22:42

Ifor
Aged Yak Warrior

626 Posts

Posted - 04/11/2014 :  07:41:46  Show Profile  Reply with Quote
It sounds as if your version of SQL Express is 2012. You can check this by connecting to SQL Express and running:

SELECT @@VERSION

Even though you connect to it with Management Studio 2008 and/or have the db in 2008 compatibility (compatibility mode 100) it will still be backed up in 2012 format. ie SQL 2008 will not be able to recognize this format.

The easiest way to proceed will be to do something like:

- script the db from SQL Express. (In MS highlight YourDB, right click, Tasks|Generate Scripts.)
- save the results to a file.
- edit the file and move any foreign key constraints to another file
- on SQL2008, create a new db
- run the main script against the new db.
- run the import wizard. (In MS highlight YourDB, right click, Tasks|Import data. The source will be SQL Express.)
- apply any foreign key constraints.


Go to Top of Page

cboshdave
Starting Member

USA
2 Posts

Posted - 04/11/2014 :  10:25:26  Show Profile  Reply with Quote
Thanks! You are exactly correct. I understand. So, my next hurdle is that when I try to Script the db, I am only getting the db created. No tables get scripted. I can script each table (lots). But that does not seem right??
Go to Top of Page

Ifor
Aged Yak Warrior

626 Posts

Posted - 04/11/2014 :  12:12:08  Show Profile  Reply with Quote
Scripting the db will just give you the CREATE DATABASE bit which I suspect is next to useless.

Try connecting to SQL Express with the full version of SQL Management Studio 2008:

Select databases
Highlight YourDB
Right Click
select tasks
select generate scripts

A wizard will then guide you, you want everything except data.

I have never tried this with a previous version of Management Studio so it may not work.

Edited by - Ifor on 04/11/2014 12:12:56
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000