Error restoring a database

Please that anybody help me.

I have a phisical device called 'respaldo.bak' inside this file there are three backups of three different databases, The first one is database called 'innovasoft' the second one is 'modelo_de_datos' and the last one is 'Inversiones'.

If I perform a restore of the first one there is no problem, but, when i try to restore the second o third database i get the following error:

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'COMPUTO'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

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

El archivo lógico 'INNOVASOFT_Datos' no es parte de la base de datos 'PROBANDO'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.
Fin anómalo de RESTORE DATABASE. (Microsoft SQL Server, Error: 3234)

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

BUTTONS:

OK

I have to say that i want to restore the database with a different name that's why in the error says 'PROBANDO' , i'm really concern about this problem because it means that i have no backups for my data. I used to use this procedure on sql server 2000 every single day without problems, but on sql server 2005 it doesn't work properly.

Please help me.

Thanks in advance.

Make sure you restore with a file destination plus move.

Here is a quick demo showing how it all works.

Code Snippet

--create 3 test dbs
use master
go
create database db1
go
create database db2
go
create database db3
go

--add dummy data to db2
exec db2..sp_executesql N'select db_name() [db] into db'
go

--backup dbs to disk
backup database db1
to disk='c:\dbs.bak'
with init
go
backup database db2
to disk='c:\dbs.bak'
go
backup database db3
to disk='c:\dbs.bak'
go

--restore db2 to new db
restore database db2_2
from disk='c:\dbs.bak'
with file=2,replace,
move 'db2' to 'c:\db2_2.mdf',
move 'db2_log' to 'c:\db2_2.ldf'
go

--check to make sure we actually restore db2
exec db2_2..sp_executesql N'select * from db'
go

--clean up
drop database db2_2, db3, db2, db1
go

|||

Hi, sorry that i answer until today. Well this was what i done

backup database modelo_de_datos

to disk='d:\datos.bak'

with init

go

backup database inversiones

to disk='d:\datos.bak'

go

backup database innovasoft

to disk='d:\datos.bak'

go

--restore inversiones to new db

restore database db2_2

from disk='d:\datos.bak'

with file=2,replace,

move 'inversiones' to 'd:\database\db2_2.mdf',

move 'inversiones_log' to 'd:\database\db2_2.ldf'

go

There was no problem with the backup commands but with the restore part the result was:

Msg 3234, Level 16, State 2, Line 1

El archivo lógico 'inversiones' no es parte de la base de datos 'db2_2'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.

Msg 3013, Level 16, State 1, Line 1

Fin anómalo de RESTORE DATABASE.

As you can see, the problem is exactly the same i gave when doing the task with the Management Studio

Thanks in advance

|||

I think the data & log filenames may not have the standard naming conventions. Test this out.

Code Snippet

create database db4
on
(
name = db4_data,
filename = 'c:\db4_data.mdf',
size = 10,
maxsize = 40,
filegrowth = 5
)
log on
(
name = db4log_log,
filename = 'c:\db4_log.ldf',
size = 10,
maxsize = 40,
filegrowth = 5
)

backup database db4
to disk='c:\dbs1.bak'
with init
go

RESTORE FILELISTONLY FROM DISK = 'c:\dbs1.bak'
WITH FILE=1;

restore database db4_2
from disk='c:\dbs1.bak'
with file=1,replace,
move 'db2' to 'c:\db4_2.mdf',
move 'db2_log' to 'c:\db4_2.ldf'
go

It throws similar error what you are getting now. Above post from oj, works correctly, all you need to do get the filenames from the backup with the below code and substitute in the backup command.

Code Snippet

RESTORE FILELISTONLY FROM DISK = 'c:\dbs.bak'
WITH FILE=2;

|||

You need to get the correct logical names for the invensiones database.

Do:

Code Snippet

restore filelistonly from disk='d:\datos.bak' with file=2

Once you have the correct logical names you then can move the data files to new location.

|||

Thank you guys for your useful help.

One more question. This works excellent using transact sql is it possible to do the same using Management Studio. Because the problem began when i tried to run the restore under Management Studio.,

Than you.,

|||

Sure. You would want to make sure the logical names are valid. In fact, you should see the same t-sql statement if you launch profiler and watch it.