May 31, 2005 at 9:54 am
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.
May 31, 2005 at 10:03 am
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
May 31, 2005 at 10:12 am
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.
May 31, 2005 at 11:08 am
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
June 1, 2005 at 2:18 am
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.
June 1, 2005 at 2:39 am
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!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply