Urgent! how to import data from .bak file into SQL Server2000

[yehong]

I have a backup file (db.bak). I am trying to use Import/Export wizard and first screen asks me for Data Source. What should be the correct Data Source name here for a .bak file? Also ,will this wizard import the database schema and data also?

Thanks

 

[ecobb]

You don't import from a backup, you have to restore from it.

Hope This Helps!

Ecobb
- I hate computers!

 

[yehong]

Thanks for your response.
I did not make that backup. That backup has been done on another server and I just need to create same database on my local server. How can wizard will restore if it did not backup anything? Thanks

 

[ecobb]

Sorry my last post was so short, my boss walked in

You can still restore from it, even though it's from another server. In Enterprise Manager, right click on any database, go to "All Tasks" and select "Restore Database". When the Restore Wizard comes up, at the top you have the option to "Restore As Database". Rename this to be whatever you want the new database to be called. IF YOU DO NOT RENAME THIS FILE YOU WILL RESTORE OVER AN EXISTING DATABASE. Now, when you restore from your backup, SQL Server will create a new database and restore your backup to it. To select the backup file (.bak) to restore from, choose the "From Device" option, and click "Select Devices". In the new window that opens, click "Add" and this will allow you to navigate to/ search for the .bak file you want to restore from.

Hope This Helps!

Ecobb
- I hate computers!

 

[yehong]

Thanks for this input. It gave me following errors.
"Device activation error. The physical file name'C:\...mdf'
may be incorrect. Use MOVE WITH to identify a valid location for the file."
I then created same path that was required and it worked.
I think restore was looking for same file path that was used while exporting the database.

I had tried using these commands in the Query Analyzer also:
restore database newdbname from disk = 'd:\mssql7\backup\A1203.bak'
with file=1, replace,
move 'A1203.mdf' to 'd:\mssql7\data\A1203.mdf',
move 'A1203_log.ldf' to 'd:\mssql7\data\A1203.ldf'

This command returned following errors
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'A1203.mdf' is not part of database 'newdbname'. Use RESTORE FILELISTONLY to list the logical file names.

I just wanted to share with you this info and would love to know if I could have modified this command to take care of those errors so that I would not have to create same path on the server.

 

[nigelrivett]

In enterprise manager you can specify the paths to create the mdfs and ldfs in the second tab - just overwrite what is already there.

The error you are getting from query analyser is that A1203.mdf is not a logical name for one of the files. The logical names can be found from enterprise manager or by

restore filelistonly from disk = 'd:\mssql7\backup\A1203.bak'


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.