Solved

Backup and restore from 2008 - 2005 not working

Posted on 2008-07-20
9
1,869 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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
View SQL 2005 Job package 16 76
SSRS 2013 - Overlapping reports 2 38
SQL Server 2012 r2 - Sum totals 2 31
PolyServe for SQL server 13 41
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

713 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