Urgent - Restore to a new name

Question

Post reply

Urgent - Restore to a new name

Paula-196779

SSCrazy

Points: 2575
More actions
May 31, 2005 at 9:54 am

#113922

Hi all,
I need to restore a database from a few days ago to a new database (i.e. a copy). I have looked in BOL and found instructions for this but my backups are not stored on a device, they are on the d drive. When I look at the restore wizard it gives me a list of all the backups with a date next to them, but when I look on the d drive, it seems there is only one (the latest one). so I have a number of questions;
1. If I can find the right backup file, how do I restore this file to a new database?
2. Can I restore to a new database using the all tasks - restore wizard?
3. If SQL Server gives me a list of backups does that mean they must exist somewhere?
4. Should I do this through T_SQL or the GUI?
Many thanks,
Paula.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

hoo-t SSChampion Points: 12713 More actions · Answer 1

My recommendation would be to use tSQL in Query Analyzer. Locate your backup file and then use the following syntax -

This statement will give you the logical filenames for the database along with their original location on the disks:

RESTORE FILELISTONLY

FROM DISK = 'X:\SQL_Backups\PUBS_special.bkp'

You'll need the Logical Names, and you'll need to be sure that you do NOT specify the same PhysicalNames.

Here's the statement for the restore (plug in your logical names, change the physical names, and change the database name to whatever you want it to be):

RESTORE DATABASE PUBS_new

FROM DISK = 'X:\SQL_Backups\PUBS_special.bkp'

WITH RECOVERY,

MOVE 'PUBS_Data' TO 'f:\SQL_Data\PUBS_new_Data.mdf',

MOVE 'PUBS_Log' TO 'L:\SQL_Logs\PUBS_new_Log.ldf',

STATS = 10

-- Steve

Paula-196779 SSCrazy Points: 2575 More actions · Answer 2

Thankyou. I still don't know how to specify last Thursday's backup though. The only one I can find is called blah.BAK but when I go to all tasks - restore, it gives me a list with dates next to them. Then on the options tab you can change the Restore As field, but I'm worried it won't work and it will restore over the original. If I could find a file called blah260505.BAK or something similar then I'd do exactly as you suggest but I can't. It must exist otherwise it wouldn't appear in the list, but it's not where it says it is.

Thankyou anyway.

hoo-t SSChampion Points: 12713 More actions · Answer 3

When you select "restore database", the filenames of the backups should be listed in the selection box. Otherwise, to ensure that you are not overwriting when using the wizard, change the "Restore as database" database name on the General tab, then change the "Restore as" filenames on the Options tab. This will create a new database with new files, and won't overwrite anything. Just be sure that you make changes in BOTH places, the database name AND the file names!

Steve

Paula-196779 SSCrazy Points: 2575 More actions · Answer 4

I tried to do this and definitely changed both places but I got a message saying 'you are trying to overwrite an existing database'. I really need to do this asap but I just can't work out how, I'm sure it's really easy but if I do it using t-sql how do I specify the particular backup from the date I want?

Please help, I'm desperate!

Thanks,

Paula.

Paula-196779 SSCrazy Points: 2575 More actions · Answer 5

Ok, I worked out what I did. I had the backup that I wanted selected but also had another one ticked lower down and so it was trying to back up both. Once I unchecked the second backup it ran fine.

Thanks for everyone's help!