Backup SQL Express - Restore Sql 2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Backup SQL Express - Restore Sql 2008

Author

Topic

cboshdave
Starting Member

2 Posts

Posted - 2014-04-10 : 20:19:30

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

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-04-11 : 07:41:46

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.

cboshdave
Starting Member

2 Posts

Posted - 2014-04-11 : 10:25:26

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??

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-04-11 : 12:12:08

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources