Backup and restore from 2008 - 2005 not working
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
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
chapmandew
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.
ASKER
so there is no way to swap data from a 2008 to 2005 machine at all?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.sche
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.sche
ASKER
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
completed with errors
have you viewed the messages tab for those errors ? can you post the full message block - right click and save to file ?
ASKER
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$TE
however an adp database wont connect to it.
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$TE
however an adp database wont connect to it.
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...
the connection issue is really a different question, and you will probably want to post it in the Access / ADP zones...
ASKER
will do that thanks.