Backup and Restore

27 Jun 2006 3:42 PM

Gary L

I am attempting to restore a database to a different server. When I run
restore either from the GUI or a script I receive the following error:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database
other than the existing 'dbname' database. (Microsoft.SqlServer.Smo)

Funny thing about this is we were using SQL Server 2000. I grab this DB all
the time and restore it to another server. Worked great for years. We
recently "upgraded" to SQL Server 2005 and restoring the DBs from the 2000
server to 2005 worked fine. Going 2005 to 2005 fails.

27 Jun 2006 6:14 PM

Tibor Karaszi

There's already a database with the name of the database you try to restore. But the file layout
isn't the same as the one for the backup. Either delete the existing database before the restore, or
use the REPLACE option of the restore command. The REPLACE option is probably called something like
"force restore over existing database".

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quoteHide quote

"Gary L" <Ga***@discussions.microsoft.com> wrote in message
news:27034902-5AA3-4012-BAFF-7294732B7D86@microsoft.com...
>I am attempting to restore a database to a different server. When I run
> restore either from the GUI or a script I receive the following error:
>
> System.Data.SqlClient.SqlError: The backup set holds a backup of a database
> other than the existing 'dbname' database. (Microsoft.SqlServer.Smo)
>
> Funny thing about this is we were using SQL Server 2000. I grab this DB all
> the time and restore it to another server. Worked great for years. We
> recently "upgraded" to SQL Server 2005 and restoring the DBs from the 2000
> server to 2005 worked fine. Going 2005 to 2005 fails.

27 Jun 2006 10:12 PM

Gary L

Actually the new database didn't exist at the time, so I tried first with a
script, then created a blank database and tried it using the GUI. Neither
worked.

As it turns out the original database had another file associated with it, a
..ndf file. It appears the only way to restore it was via the script and
including the .ndf filename using "WITH MOVE". This method worked.

I'm assuming that for future restores I should not run into this problem
since the files are already in place.

Show quoteHide quote

"Tibor Karaszi" wrote:

> There's already a database with the name of the database you try to restore. But the file layout
> isn't the same as the one for the backup. Either delete the existing database before the restore, or
> use the REPLACE option of the restore command. The REPLACE option is probably called something like
> "force restore over existing database".
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Gary L" <Ga***@discussions.microsoft.com> wrote in message
> news:27034902-5AA3-4012-BAFF-7294732B7D86@microsoft.com...
> >I am attempting to restore a database to a different server. When I run
> > restore either from the GUI or a script I receive the following error:
> >
> > System.Data.SqlClient.SqlError: The backup set holds a backup of a database
> > other than the existing 'dbname' database. (Microsoft.SqlServer.Smo)
> >
> > Funny thing about this is we were using SQL Server 2000. I grab this DB all
> > the time and restore it to another server. Worked great for years. We
> > recently "upgraded" to SQL Server 2005 and restoring the DBs from the 2000
> > server to 2005 worked fine. Going 2005 to 2005 fails.
>
>

28 Jun 2006 7:33 AM

Tibor Karaszi

Restore will create the database for you if it doesn't exist. But you might have to use MOVE if for
example a file for the source database is in a folder that doesn't exist on the destination server.

If the database exists, then SQL Server will verify that the file layout for the existing database
matches the one in the backup has. If it doesn't, you either have to delete the database first, or
use the REPLACE option (which will do it for you).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quoteHide quote

"Gary L" <Ga***@discussions.microsoft.com> wrote in message
news:F8CE1FC8-E598-4D2E-A231-61F2DD2E1DE5@microsoft.com...
> Actually the new database didn't exist at the time, so I tried first with a
> script, then created a blank database and tried it using the GUI. Neither
> worked.
>
> As it turns out the original database had another file associated with it, a
> .ndf file. It appears the only way to restore it was via the script and
> including the .ndf filename using "WITH MOVE". This method worked.
>
> I'm assuming that for future restores I should not run into this problem
> since the files are already in place.
>
> "Tibor Karaszi" wrote:
>
>> There's already a database with the name of the database you try to restore. But the file layout
>> isn't the same as the one for the backup. Either delete the existing database before the restore,
>> or
>> use the REPLACE option of the restore command. The REPLACE option is probably called something
>> like
>> "force restore over existing database".
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>>
>> "Gary L" <Ga***@discussions.microsoft.com> wrote in message
>> news:27034902-5AA3-4012-BAFF-7294732B7D86@microsoft.com...
>> >I am attempting to restore a database to a different server. When I run
>> > restore either from the GUI or a script I receive the following error:
>> >
>> > System.Data.SqlClient.SqlError: The backup set holds a backup of a database
>> > other than the existing 'dbname' database. (Microsoft.SqlServer.Smo)
>> >
>> > Funny thing about this is we were using SQL Server 2000. I grab this DB all
>> > the time and restore it to another server. Worked great for years. We
>> > recently "upgraded" to SQL Server 2005 and restoring the DBs from the 2000
>> > server to 2005 worked fine. Going 2005 to 2005 fails.
>>
>>