Create a new database from a .bak backup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GM - 08 Mar 2008 11:58 GMT

Hi, i have a problem:

I have a .bak file from a database backup, but i don't have the database
anymore.

How can i create the database from the backup file?

If i try to restore the database with management studio, i should already
have a database to put the data in.. but i don't have it anymore.

If i create a new database, and try to put the backed up data into it, i got
an error:
Msg 3154, Level 16, State 4, Line 1

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

Is there a way to re-create the database from the backup?(it's a full
backup)

I'm working with MSSQL Server 2005 and Management Studio.

Thank you for any advice!

Reply to this Message

John Bell - 08 Mar 2008 13:27 GMT

Hi

You don't need to start with a database when restoring. Click on the
database leaf in the tree and then enter your own database name in the
database name box, then go on and choose the file to restore from using the
restore from device option.

If you know that there is only one backup in the file and the database
doesn't exist you could use something like the following from a query window
if you change the file path/name and database name:

RESTORE DATABASE NewDatabase
FROM DISK = 'D:\Backups\DBBACKUP.bak'
GO

John

> Hi, i have a problem:
>
[quoted text clipped - 20 lines]
>
> Thank you for any advice!

Reply to this Message

GM - 08 Mar 2008 15:03 GMT

Hi, thank you for the answer!

the .bak file contains only one backup
If i use the restore option, and type a new database name, when i execute i
get te following error:

Msg 5133, Level 16, State 1, Line 1

Directory lookup for the file "D:\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\XXX_Data.mdf" failed with the operating system
error 3(The system cannot find the path specified.).

I've already tryed
RESTORE DATABASE NewDatabase
FROM DISK = 'D:\Backups\DBBACKUP.bak'

(with the right location/dbname)

but if the database does not exists, i got an error:

Msg 5133, Level 16, State 1, Line 1

Directory lookup for the file "D:\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\XXX_Data.mdf" failed with the operating system
error 3(The system cannot find the path specified.).

Msg 3156, Level 16, State 3, Line 1

File 'XXX_Data' cannot be restored to 'D:\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\XXX_Data.mdf'. Use WITH MOVE to identify a valid
location for the file.

Msg 5133, Level 16, State 1, Line 1

Directory lookup for the file "D:\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\XXX_Log.ldf" failed with the operating system
error 3(The system cannot find the path specified.).

Msg 3156, Level 16, State 3, Line 1

File 'XXX_Log' cannot be restored to 'D:\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\XXX_Log.ldf'. Use WITH MOVE to identify a valid
location for the file.

Msg 3119, Level 16, State 1, Line 1

I've tryed to use "WITH MOVE " tu point to another folder, but since i don't
have the mdf file, it's pointless...

If i create a new database with the same name of the database i want to
restor,e before trying to restore, i got the error i reported in the
previous post..

Any suggestion?

> Hi
>
[quoted text clipped - 36 lines]
>>
>> Thank you for any advice!

Reply to this Message

Greg D. Moore (Strider) - 08 Mar 2008 15:15 GMT

> Hi, thank you for the answer!
>
[quoted text clipped - 44 lines]
> I've tryed to use "WITH MOVE " tu point to another folder, but since i
> don't have the mdf file, it's pointless...

Umm, that's not pointless at all. That's the exact whay you do this.

RESTORE DATABASE NewDatabase
from disk='D:\Backups\DBBACKUP.bak'
with move 'XXX_Data' to 'C:\newlocation_fordata\XXX_LOG.MDF',
move 'XXX_log' to 'C:\newlocation_forlogs\XXX_LOG.LDF'

> If i create a new database with the same name of the database i want to
> restor,e before trying to restore, i got the error i reported in the
> previous post..

Reply to this Message

Chris.Cheney - 08 Mar 2008 15:15 GMT

RESTORE DATABASE [NewDatabaseName]
FROM DISK='BackupNameAndPath'
WITH MOVE 'OldDatabaseName' TO 'NewDatabaseNameAndPath.mdf',
MOVE 'OldDatabaseName_log' TO 'NewDatabaseNameAndPath.ldf'

NB the MOVEd strings are the logical database names. Use

RESTORE HEADERONLY FROM DISK = 'BackupNameAndPath'

to get a listing of the contents of the backup file to determine the
old database name.

Reply to this Message

Brett I. Holcomb - 08 Mar 2008 15:19 GMT

It's telling you the directory does not exist so create it first. You
can use the studio inteface of the SQL as given by others.

>Hi, thank you for the answer!
>
[quoted text clipped - 74 lines]
>>>
>>> Thank you for any advice!

Reply to this Message

GM - 08 Mar 2008 17:14 GMT

I forgot to say that i had to restore the database to a server different
from the one where i did the backup..

Anyway, with your help, i managed to recreate the database using:

RESTORE DATABASE XXX

FROM DISK = 'E:\bck\XXX.bak'

WITH

MOVE 'XXX_data' TO 'f:\sqldata\XXX_data.mdf'

, MOVE 'XXX_log' TO 'f:\sqldata\XXX_log.ldf'

, REPLACE;

thanks to all for your helpful suggestions!

Reply to this Message

Brett I. Holcomb - 08 Mar 2008 15:14 GMT

You don't need a database to restore in Management Studio. I do this
quite often when I move databases. I right click on "Databases" and
there is a restore database option. I choose from device and select
the bck file and then check restore. I give it a name and then under
options make any changes on the path that I want. Make sure the
directory the mdf and ldf files are going into actually exists or it
will complain.

>Hi, i have a problem:
>
[quoted text clipped - 19 lines]
>
>Thank you for any advice!

Reply to this Message