Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Restore Database From Overwritten Old Backups Expand / Collapse
Author
Message
Posted Thursday, October 10, 2013 12:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:46 PM
Points: 321, Visits: 1,279
Hi Every one..

Like to take advise on this.

Have one backup file which overwritting everyday full backup on that file Suppose ADW.Bak overwritting Full Backup every day .I want to restore Database with 5 day old backup file how i can do that?

When i am trying to restore it showing me most current backup file only?



Aim to inspire rather than to teach.
SQL Server DBA
Post #1503753
Posted Thursday, October 10, 2013 1:01 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
Was the backup overwriting the older backups in the file every night or appending the backup to the file?
Restore headeronly should show you what backups are in the file.

If there's only one backup in the file, then you can only restore from that. If backups are being overwritten then the older backups are gone.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1503754
Posted Thursday, October 10, 2013 1:40 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:46 PM
Points: 321, Visits: 1,279
Its Overwritting with INIT and NOFORMAT Option.

Restore Headeronly showing most current backup file which todays date..


Aim to inspire rather than to teach.
SQL Server DBA
Post #1503769
Posted Thursday, October 10, 2013 1:42 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:46 PM
Points: 321, Visits: 1,279
this is the command

BACKUP

DATABASE XXX.bak TO DISK = N'F:\Backup\XXX.bak' WITH NOFORMAT, INIT, NAME = N'XXX_backup_2013_10_10_153518_8403533', SKIP, REWIND, NOUNLOAD, STATS = 10
GO




Aim to inspire rather than to teach.
SQL Server DBA
Post #1503771
Posted Thursday, October 10, 2013 2:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
logicinside22 (10/10/2013)
Its Overwritting with INIT and NOFORMAT Option.

Restore Headeronly showing most current backup file which todays date..


That's because INIT deletes old backups from the file before starting the new backup. There's no other backups than the latest in that file, so that's all you're going to be able to restore.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1503790
Posted Thursday, October 10, 2013 2:18 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:46 PM
Points: 321, Visits: 1,279
Thanks a lot Gail . your information always best .

Aim to inspire rather than to teach.
SQL Server DBA
Post #1503797
Posted Friday, October 11, 2013 2:06 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 481, Visits: 1,778
You don't have e.g. an offsite backup that takes a copy of your backup file every night, do you? If so you could possibly restore that.
Post #1503908
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse