Solved

Backup and restore from 2008 - 2005 not working

Posted on 2008-07-20
9
1,898 Views
Last Modified: 2011-09-20
i have an sql 2008 database that i am trying to backup then restore to a 205 database on another server.

I can back them up properly but when i try to restore them on another server i get an error message that says  

the media on device ...  is incorrectly formed

with the error message 3241

I can restore the backup on the same server without any errors (the one running sql 208)  i thought the problem might be that i was moving the backup file, so i shared the main folder where i backup from and tried doing that, still no luck.

I am using sql server management studio

If any one could offer any help it would be appreciated
0
Comment
Question by:CaptainGiblets
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22045975
YOu can't restore 2008 to a 2005 machine...you can't go backwards like that...only forwards.  You can restore 2000 to 2005, or 2005 to 2008.
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 22046039
so there is no way to swap data from a 2008 to 2005 machine at all?
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22046103
Sure...you can use SSIS to transfer your tables/sprocs, etc.  What you want to do is go to the db you want to transfer from in 2008, right click the db and select Export.  From there is a wizard that will guide you through the process.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 51

Expert Comment

by:Mark Wills
ID: 22046314
hey chapmandew - that almost sounds like using SQl Server Management studio :)

and as always, chapmandew is correct, 2008 backups are incompatable with 2005 - the headers are different inside the backup file.

not sure if the wizard does both table structures and data as well - back in 2000 you could export tables as objects - data and structure combined. Not sure it still exists in 2008.

if they are both visible to each other, you could use the script of the database to create the database in 2005 and then use a linked server from 2005 pointing to the 2008 database and table by table perform a bulk insert, or simply use SQL inserts, to load the database.

when doing that, you will want to SET IDENTITY_INSERT to ON so you can populate any identity rows as well. then it would become a fairly simple procedure to loop through each table and populate in the form insert mytable select * from linkedserver.database.schema.mytable .


0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 22046354
i generated a script and made it script for 2005, when i tried to run it it didnt do anything just said

completed with errors
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22046372
have you viewed the messages tab for those errors ? can you post the full message block - right click and save to file ?

0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 22046581
its ok i managed to get the eport to work, had to manually re create all my views etc though but should be ok.

I just now cant get adp to connect to the server

the current connection string i use to connect in sql server manager is

\\servername\pipe\MSSQL$TERMINALDB\sql\query

however an adp database wont connect to it.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22046593
that is a named instance of sql server... there will be some kind of authentication issue - have you allowed mixed mode authentication etc, have you checked your user credentials ? the user secutiry matrix in the new server / new database might have to be re-established.

the connection issue is really a different question, and you will probably want to post it in the Access / ADP zones...
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 22046604
will do that thanks.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question