Restore SQL Server 2003 Database in SQL Server 2005 1

[SimonEireconsulting]

Hi,

Anyone know how I can move a database from SQL Server 2003 to another server with SQL Server 2005 - when I try and do a restore with the option Force Overwrite (as I did from one server to another using the SQL Server 2003 on bother servers and it worked) I get the message:

The backup set holds a backup of a database other than the existing 'XXX' database.

where XXX is tha name of my database

 

[simian101]

Sounds like you need to delete the existing database with the same name or give the new database anouther name.

Simi

 

[kermitforney]

I think you need to create a new database on the 2005 server with the name of the backup your trying to restore. Create the database by name only and keep it empty then restore. Try that.

 

[SimonEireconsulting]

kermitforney,

this is exactly what I am trying to do
When I have done this before on two server running SQL Server 2003 it worked (I have done this many times), but I get the error described above when going from 2003 to 2005

 

[Crowley16]

firstly, sql server 2003? doesn't exist...

secondly, it seems like you have a backup which contains multiple media sets.

you can either restore just the media set related to your database or take a clan backup and restore that.

there's no need for the database to exist on 2005 first.

--------------------
Procrastinate Now!

 

[SimonEireconsulting]

SQL Server that comes with Small Business 2003 - probably just SQL Server 2000

the bakup doesn't contain multiple media sets - it is a standard backup of a pre 2005 db.

In 2000, I used to create a new database with the same name as the old database and then restore from bakup file with the option "overwrite existing db"

This doesn't work when I try it in 2005

 

[mrdenny]

Don't bother creating a new database with the same name. Just type the name of the database in the drop down list. This should allow the database to restore.

Small Business Server 2003 comes with SQL Server 2000 Standard.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[PRPhx]

If all else fails, you can try to detach the database, copy the db file from the old server and then re-attach it under SQL Server 2005.

Here is a link to MS that describes how to move user db's, etc.:

http://support.microsoft.com/kb/314546

Good luck! I'll be going thru the same thing in a week or so. Not really looking forward to it....My DBA says it should be fairly easy and straight forward.

 

[SimonEireconsulting]

mrdenny, PRPhx,

taking a backup of the old database and restoring to 2005, without first creating a db in 2005 with the same name but just typing in the name in the dropdown list worked.

Thanks mrdenny

 

[mrdenny]

No problem.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog