backup restore from 2005 to 2005 fails

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

SQL Server 2005 Forums

SQL Server Administration (2005)

backup restore from 2005 to 2005 fails

Author

Topic

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-07-02 : 05:34:09

i got sql server 2005 express on my pc and it works flawlessly

Now i make every day backups by hand and save it to directory on my computer.
Now my company made a test server with sql server 2005 express ( and management tools )
and i want to restore the database to the backup file i got from my pc....

Ive read alot of things about no backwards compatibility with 2008 to 2005, but this isnt the case cuz
its both 2005... anyway, this is the error i get when i click options, in the restore window


TITLE: Microsoft SQL Server Management Studio Express
------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

De mediafamilie op apparaat C:\Documents and Settings\Administrator\Bureaublad\db backups\020710aa.bak is onjuist samengesteld. SQL Server kan deze mediafamilie niet verwerken.
RESTORE FILELIST wordt op abnormale wijze beëindigd. (Microsoft SQL Server, Error: 3241)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2055&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

i must say, i only installed the sql server 2005 ( first i did the MSXML update like ms site stated, then the framework 2.0)
further i made a database, nothing else...

thx in advance guys

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-02 : 05:51:32

I have not so much experience with backups and restores but I think there are two ways to solve your problem:

Way 1: All backup files of a mediafamily should be in the folder from where you are restoring.

Way 2: create a new backup and be sure to create a new media so that there are no dependencies to other backups.

No, you're never too old to Yak'n'Roll if you're too young to die.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-02 : 06:50:45

If the database you're trying to restore uses table partitioning, you won't be able to restore it to a SQL Express edition. It is only supported on the Enterprise Edition.

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-07-02 : 08:56:36

i think it has something to do with the updates, is this possible ?

im updating the server right now

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-07-02 : 09:00:05

still the same error

webfred, i did the second thingie, but really dont know what u mean with way1.
i browse to my .bak file on my desktop, do i need to put it into the backup folder in :
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup

?

PS/ sorry for duplicate :(

NeilG
Aged Yak Warrior

530 Posts

Posted - 2010-07-02 : 09:14:30

No you don't need to put the backup file in the default folder, the file just has to be present in the path you give to SQL Server.

What version is the SQL Server instance that the backup file is taken from?

-----------------------------------------------
Learning something new on SQL Server everyday.

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-07-02 : 09:17:52

ive just discovered where i can see all the current versions of the server

PC where i took the backup
Microsoft SQL Server Management Studio Express 9.00.2047.00
Microsoft Data Access Components (MDAC) 6.1.7600.16385 (win7_rtm.090713-1255)
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 8.0.7600.16385
Microsoft .NET Framework 2.0.50727.4927
Operating System 6.1.7600

PC where backup needs to be placed:

Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3082
Operating System 5.2.3790

Im still updating, i hope thats all i need to do ... :o

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-07-02 : 09:47:01

no, just the same error....

im a bit stumped, its both sql server 2005, only the MSXML does seem something that should be the same ? i really dont know...

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-07-02 : 09:50:20

ow yes , when i start config manager

this is stated on pc from where i made the backup:
DEVELOPMENT01\SQLEXPRESS (SQL Server 9.0.4053 - COMPANY\tom)

other one:
IIS-SERVER01\SHAREPOINT (SQL Server 8.0.2055 - SOMETHING\Administrator)

8.0 vs 9.0 ?
that seems to be the cause, not ?

if i only knew were to find the version, i got on my pc with win7, to install on the windows 2003 serv

Or do i better just download 2008 express and install that, 2005 to 2008 doesnt seem to be a problem because
i got 2008 on my personal laptop at home, and the backups restore flawless there...

some1 who would grant me some advice? :)

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-07-02 : 10:00:26

You appear to be backing up a SQL Server 2005 database (9.0), and restoring it on a SQL Server 2000 instance (8.0). That is not possible.

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images directly from SQL Server, SQLite and Firebird databases.
SQL Data Sets - share and distribute SQL Server and Oracle data sets securely and easily

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-07-02 : 10:02:05

but they are both the same package, "sql server 2005 express", how is its possible ? are there versions of sqlserver 2005 with 8.0 or 9.0
what do i do here ? ..

reinstalling isnt a problem

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-02 : 10:09:26

Only the Management Studio components are the same, the database engine versions are different. You need to restore to a SQL 2005 or higher database engine.

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-07-02 : 10:18:20

i need to restore the sql server to a sql 2005 or higher, right ?

not the backup itself ? bit confused

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-02 : 10:23:50

No.
What robvolk means was:
You have taken a backup from SQL Server 2005 and this can not be restored to SQL Server 2000. Only to 2005 or higher.
So you need to install SQL Server 2005 (or higher) on your destination machine.

No, you're never too old to Yak'n'Roll if you're too young to die.

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-07-02 : 10:36:51

aha, i know :)

but i just figured out that i had only installed management studio , i assumed, the express would be integrated in that package.
but apparently its not:

on this site:
http://www.microsoft.com/Sqlserver/2005/en/us/express-down.aspx

first paragraph with title:
SQL Server 2005 Express Edition SP3

second linkie, but i need the one on top of that one too ? :]

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-07-06 : 07:02:44

yes no ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources