January 16, 2014 at 5:12 am
If backup schedule scnario as below
Monday- 10 PM full backup
Tuesday-6AM Differential backup
Tuesday-1 Pm differential backup
And every one our transactional backup is scheduled
If my DB is crashed @ 2:55 PM on tuesday, how can i recover the database.
Please let me know the entire query.
Thanks
Shashikala
January 16, 2014 at 5:21 am
Restore the last full backup (see books online - the SQL help file - for the RESTORE syntax)
Restore the 1pm differential
Restore all the log backups since that diff. You didn't say what the schedule is, so can't say how many you will need to restore
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2014 at 5:27 am
Transactional backup as below
every one our let say,11PM,12AM,1AM,2AM.............1PM,2PM.
If Db crashed @ 2:55PM, how can i recover the DB.
January 16, 2014 at 5:32 am
Restore the last full backup
Restore the 1pm differential
Restore all the log backups since that diff.
Whether or not you can recover that 55 minutes since the last log backup depends on the type of failure. Look in books Online for the discussion of 'Tail-log backup'
That risk is why the log backup interval should be less than the allowable data loss for the system.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2014 at 1:10 pm
GilaMonster (1/16/2014)
That risk is why the log backup interval should be less than the allowable data loss for the system.
Best quote I've seen in awhile and very true.
January 29, 2014 at 9:35 pm
Some times it allows you to take tail log backup (with continue on error) and restore it , you may get all the data.
Every time you cannot depend on luck as gail said reduce the timing frequency of log backup. normally 15 minutes.
Regards
Durai Nagarajan
January 29, 2014 at 10:55 pm
durai nagarajan (1/29/2014)
Some times it allows you to take tail log backup (with continue on error) and restore it , you may get all the data.
WITH NO_TRUNCATE
and that's called a tail-log backup
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2014 at 11:50 am
Do you guys use any automated script to restore the t-logs?
If there are hundreds of t-logs to restore, it will be time consuming right?
February 5, 2014 at 4:09 pm
If you took the backups using native SQL, and they are stored in the same location as the time of backup, then I think you can use the GUI to restore and it will show you all the t-log backups, already selected, ready for the restore.
It might be a good idea to go through the procedure with a test database so you are familiar with the steps,
February 5, 2014 at 4:54 pm
Steve Jones - SSC Editor (1/28/2014)
GilaMonster (1/16/2014)
That risk is why the log backup interval should be less than the allowable data loss for the system.Best quote I've seen in awhile and very true.
Agreed
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2014 at 4:55 pm
homebrew01 (2/5/2014)
If you took the backups using native SQL, and they are stored in the same location as the time of backup, then I think you can use the GUI to restore and it will show you all the t-log backups, already selected, ready for the restore.It might be a good idea to go through the procedure with a test database so you are familiar with the steps,
Yes, that is a nice feature added to 2012 and 2014.
And re-emphasize the testing of the restore process.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2014 at 5:04 pm
Earlier versions have it too.
February 5, 2014 at 5:18 pm
That feature disappeared with SQL 2005. You can get something like it if you have all of your backups in the same backup file or in the same backup device. But that is different than how 2012 now works where it reads the backup history.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply