Restoring Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Restoring Data

Author

Topic

ronji
Starting Member

8 Posts

Posted - 2004-07-25 : 08:10:27

Hi there,
I have been given a SQL backup file from a source code supplier.
I should be able to restore this file into SQL 2000 so I have the correct table and File structure for the DB.
I cannot restore the file?
Can someone please tell me how I can restore a file that wasn't backed up on the same server as it is being restored on.

I sincerely thank you in anticipation...A Very frustrated - Ron

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-25 : 13:36:17

Are you trying to restore over an existing database? Are you trying to restore in Enterprise Manager or in Query Analyzer? If in Query Analyzer, can you show us your script? What error are you getting?

Here is a sample restore script you can run in Query Analyzer:

RESTORE database_name
FROM DISK = '\\server\share\directory\database_name.bak'

If their database was named different than your database, or the files were somewhere different, you will need to use the following options:

First, run this to find out the logical and physical filenames. Write the results down or copy to notepad:

RESTORE FILELISTONLY
FROM DISK = '\\server\share\directory\file.bak'

Then, use the following syntax to restore.

[code]
RESTORE database_name --This will be the database you are restoring over. It can also create a new database.
FROM DISK = '\\server\share\directory\file.bak' --This is the file you are restoring from.
WITH
MOVE 'logical_data_file' TO 'd:\mssql\data\file.mdf', --This will move the data file to the new location.
MOVE 'logical_log_file' TO 'd:\mssql\log\file.ldf' --This will move the log file to the new location.

The MOVE option is moving the logical file names you found with the FILELISTONLY restore to the new files.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

ronji
Starting Member

8 Posts

Posted - 2004-07-25 : 18:01:29

Hi Derek,

Thank you for your assistance. I got thru the first half of the script and when I ran the MOVE I got the following error:

Server: Msg 155, Level 15, State 1, Line 2
'ncawebstore' is not a recognized RESTORE option.

Here is the code:
RESTORE ncawebstore
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL$NCAWWW\Data\CFwebstoreSQL_backup'
WITH
MOVE 'logical_data_file' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$NCAWWW\Data\ncawebstore.mdf'
MOVE 'logical_log_file' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$NCAWWW\log\ncawebstore.ldf'

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-25 : 20:10:27

You were close. You're missing a comma:

RESTORE ncawebstore
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL$NCAWWW\Data\CFwebstoreSQL_backup'
WITH
MOVE 'logical_data_file' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$NCAWWW\Data\ncawebstore.mdf',
MOVE 'logical_log_file' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$NCAWWW\log\ncawebstore.ldf'

Your logicalfiles when you did the RESTORE FILELISTONLY were 'logical_data_file' and logical_log_file?????? That doesn't sound right. You need to replace what I typed there with what's listed from the RESTORE FILELISTONLY.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-07-25 : 21:43:10

RESTORE DATABASE ncawebstore
....

You will also need to find what the logical filenames are.
see restore filelistonly in bol

RESTORE filfelistonly
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL$NCAWWW\Data\CFwebstoreSQL_backup'

This will give a hint as to whether this is a database backup file as well.

==========================================
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.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-26 : 00:54:08

Hey, didn't I say that??? lol

Nigel must be sleepy.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 01:42:41

'C:\Program Files\Microsoft SQL Server\MSSQL$NCAWWW\Data\CFwebstoreSQL_backup'

Is it just me or is this a scary file path ...

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-26 : 01:49:19

It is. You really need to store the backups file somewhere OTHER than the data directory that houses your database files. Also, don't you have an extension on the backup files???? I would think it would be "CFwebstoreSQL_backup.bak" for example.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

ronji
Starting Member

8 Posts

Posted - 2004-07-26 : 18:01:57

THanks guys, I bought this cold fusion estore from cfwebstore.com which is fusebox based. I have been setting it up using an access DB but obviously want it on SQL. CFwebstoreSQL_backup is all that I was given and I was informed I should be able to restore this into SQL??? Yeah sure... I will digest above adn see if I can make it work. I will then move the backup out of the data file. If any one wants to charge me to set it up. It would be an otion I would consider.. Thank you sincerely ALL.... especially Derrick

Kristen
Test

22859 Posts

Posted - 2004-07-27 : 13:51:52

I don't really like the idea of having any of the data or backup files under
C:\Program Files\Microsoft SQL Server\...
but maybe I'm just getting old!

If you've got another drive I'd stick them on their:
x:\MSSQL\DATA
and
x:\MSSQL\BACKUP
is my normal location - must easier to type in that all that "Program Files" stuff!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources