November 19, 2008 at 12:53 pm
Hi,
We have a backup device called ABC.bak.
Full Backup every day 11:00pm, every 6 hrs differential and every hr log backup goes the same backup device abc.bak.Its size is 4gb.
So now I want restore this database to other instance.I copied this ABC.bak and trying to restore.
1. I choose restore from backup device->Select devices->Disk(Chose restore devices page)->Add->filename and backup device.I choose filename and selected ABC.bak and restore is fine.
2.But how can we know all the differential and log backups are restored from ABC.BAk
3. Whether do we need choose Backup device instead of filename.
4. If want to restore only full backup from the backup device ABC.BAK how to do that?
5.If I want restore only full and differential and one log back restore to get point in time data from ABC.BAK device how to do that?
I know that the full,diff and log backups are going to backup device ABC.bak but confused in restore method? plz provide me the script to restore from backup device
November 19, 2008 at 1:20 pm
From BOL:
Restore a full database and a differential backup
This example restores a full database backup followed by a differential backup. In addition, this example shows restoring the second backup set on the media. The differential backup was appended to the backup device that contains the full database backup.
Copy Code
RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH NORECOVERY
RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH FILE = 2
The keys are WITH FILE = 2 (specifies file 2 in the device) and WITH NORECOVERY (which needs to be specified if you want to continue to restore differential and log backups). Each backup is appended to your device so your FULL backup is file 1, your first log backup is 2, etc...
You can run RESTORE FILELISTONLY FROM ABC to see the files that are in the device. If you are not issuing a WITH INIT or WITH FORMAT at some point when you do a backup you have all the backups ever sent to that device and you probably want the last set.
What you need is the last full backup, the last differential, and then any log files after the last differential.
I typically use one device/file for full backups, another for differential, and another for logs. That way I know what I need to restore easier.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 20, 2008 at 12:40 pm
thank You
we have full backup at 11:00pm and diff at every 6 hrs and log every 15 mins. All backups are going one backup device ABC.bak.
If a database 'ABC' crashed at 9:25am how to restore from the backup device ABC.bak to the point in time 9:25am using t-sql script as well as through enterprise manager.
could you plz explain me this.I really appreciate your help.
November 20, 2008 at 1:42 pm
Basic restore would be like this:
-- this lists the files in the device so you can find the right file numbers
Restore filelistonly from abc
-- this is the restore of the last full
Restore Database ABC from abc with norecovery, file = 1
-- now restore the closest differential
Restore Database ABC from abc with norecovery, file = 25 -- I think this would be the 5 am differential
-- now restore the log files
Restore Log ABC from abc with norecovery, file = 26
... repeat until you get to the 9:30 log backup, incrementing the file number
-- now restore the last log file to the correct time
Restore Log ABC From abc with recovory, file = ?, 'Nov 20, 2008 9:25 AM'
For using EM look it up in BOL.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 20, 2008 at 4:19 pm
thank you Corbett...
I executed the command restore filelistonly from backupdevice, Iam getting the below results
LogicalName PhisicalName Type FilegroupName size maxsize
ABC_Master_dat D:\DATA\ABC.mdf D Primary 4253548544 35184372080640
ABC_Master_log D:\Log\ABC.ldf L Null 5834407936 35184372080640It is not giving any file numbers.So how can we know the file numbers that need to be restored.Iam sure that all the backups(Full,diff,log) are going to backup device ABC. But in the above results have NO file numbers. Is that any thing wrong with my database.Its a production box, now I need to restore the database.
Lets say full backup is restored with file =1 and how we need look for the next differentilal backup file number and the next log backup flle number that need to mentioned in the restore command.
Restore ABC from Disk ='c:\ABC>bak'
with file=? with norecovery,stats 10.
plz help me.....
November 20, 2008 at 4:52 pm
How did you find these file numbers:
-- now restore the closest differential
Restore Database ABC from abc with norecovery, file = 25 -- I think this would be the 5 am differential
-- now restore the log files
Restore Log ABC from abc with norecovery, file = 26
November 20, 2008 at 5:09 pm
I just guessed on File Numbers. I gave you the wrong command earlier. You want the RESTORE HEADERONLY which has the File Number in the POSITION column. You can look this up in BOL.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 21, 2008 at 1:02 pm
Thank you..
I understood as below,plz correct me if Iam wrong.
1.when ever we want restore a database backup from a backup device(which consists full,diff and log backups) we need to go for Restore headeronly to see the file numbers in 'Position' column. Then use those file numbers in the restore command loke
restore database ABC from disk='E:\backup\ABC.bak'
with file =(the number we get from the position column),norecovery
and the subsequent diff and log backups can be restored by giving the proper file numbers
Is that correct?
November 21, 2008 at 1:25 pm
Yes, this is correct. But is also why I always have separate files for Full, Differential, and Log Backups. You could also have different files for the log files between full/differential backups so you don't have as many files within the backup file to look through.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 21, 2008 at 1:37 pm
Thank you..
is there any sample restore script to restore from backup device(which consists full,diff and log backups),that automatically takes the file numbers and restores the database.
OR do we need to do one by one manually,I mean restore full, then look for the file number using restore headeronly and restore the diffrential and look for the file number again and restore the subsequent log backups.
how it works actually?
plz advice me...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply