Restore Help Needed

[dburniston]

I've received a backup file from another company that I need to restore to my server. I believe I have to use the Restore With Move.

I need to use a filename instead of a backup device, and I get a syntax error when I try. The database doesn't currently exist on my system.

RESTORE DATABASE Prep FROM 'D:\Data\Prep' WITH MOVE 'Prep' TO d:\Prep.mdf'

The backup file Prep doesn't appear to have a file extension, even from the directory list in command prompt and only shows as Prep. The syntax error states the file definition is a problem.

Can anyone help me get the data restored?

Thanks,

...Dave

 

[MeanGreen]

Is this a backup of the database or a detachment from the database. Is this SQL 6.5, 7.0, or 2K? Is there really no extension on the file sent, or was it removed to get past antivirus software (so you will want to rename it with the proper extension)? You could try the sp_attach_single_file_db command like this:

EXEC sp_attach_single_file_db @dbname = 'Prep',
@physname = 'D:\Data\prep.mdf'


Hope this helps.

 

[jester777]

The syntax of your restore statement is wrong try

RESTORE DATABASE Prep FROM DISK='D:\Data\Prep' WITH MOVE 'Prep' TO 'd:\Prep.mdf'

 

[dburniston]

Ok,

I have MSSQL Server2000. I'm restoring a SQL Server 7 database. When I do a restore filelistonly, I get the following information:

2 files
Logical names
FkaPrep_dat
FkaPrep_log

Physical name
E:\MSSQL7\DATA\FkaPrep.mdf
E:\MSSQL7\DATA\FkaPrep.ldf

Using the following restore command:

restore database fkaprep from disk = 'd:\fkaprep' with move
'fkaprep_dat' to 'd:\fkaprep.mdf'

I don't get the error for the mdf file anymore, but I get an error that the ldf file needs the WITH MOVE.

I've tried using the File = 1 option to just get the mdf file, but it didn't make a difference.

How do I get it to restore both files with a move?

Thanks,

...Dave

 

[MeanGreen]

Have you tried just doing a sp_attach_db. Since the extentions are MDF and LDF, I am guessing that the sp_attach_db might work. Try this:

EXEC sp_attach_db @dbname = N'FKAPrep',
@filename1 = N'E:\MSSQL7\DATA\FkaPrep.mdf',
@filename2 = N'E:\MSSQL7\DATA\FkaPrep.ldf'

Hope this helps.

 

[jester777]

I think using the FILE=1 parameter the way you want is only available if you are doing a partial restore (ie specify the PARTIAL keyword). If you don't specify PARTIAL then FILE=1 means restore the first backup set on the backup device you are using.
The best solution here is to specify MOVE options for both the data file and the log file. When the restore is complete you can do anything you want with the transaction log.