| Author |
Topic |
|
laddu
Constraint Violating Yak Guru
332 Posts |
 Posted - 2009-11-10 : 19:58:21
|
| In what circumtances can we recover the database to point of time failure?I mean can we take the tail backup in every case(eg:-database is offline) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2009-11-10 : 22:03:32
|
| Thanks for you reply Tkizer.Can we take tail t-log using below command, if DB is offline alsobackup log databasenameto disk='somepath\file'with no_truncate;goORbackup log databasenameto disk='somepath\file'with continue_after_error;go |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2009-11-10 : 22:30:16
|
| That means we will be lose the active transations(transations from last tlog to failure). What are the other ways to achieve this other than logshipping? if i need point in time recovery |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-11-10 : 22:51:30
|
| If the database can be safely detached, you could copy the data and log file(s) to another server and attach them. Assuming no file corruption you'd preserve all the transactions in the log. It's not a reliable technique though, just an option in case of emergency.You could use database mirroring to provide another copy of the database, as well as replication. There are 3rd party software packages that provide (near) continuous replication of SQL Server data to another source, using various methods (log reader, disk reader, network packet duplication) as well as SAN hardware/software combinations that do block-level disk copying. None of these are technically backup solutions, but they could be used in concert with database backup techniques to reduce the loss window if your database goes offline. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-11-10 : 23:37:16
|
| We use database mirroring and clustering for high availability. We backup our tlogs every 15 minutes.Log shipping just provides another server to perform the restores. It does not allow you to backup the active transactions on the principle server in the case where a database is not available.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2009-11-12 : 10:22:30
|
quote:
Originally posted by tkizer
If the database is not available, then no.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
I found below answer in forums that it is possible to take tail backup even db is offlineProviding the log is available, yes. If the log is damaged/missing then no. If it's just the mdf/ndf files that are damaged/missing/corrupt then yes. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-11-13 : 04:43:14
|
quote:
Originally posted by tkizer
It is not possible to take a transaction log backup of a database that is offline. Try it yourself, you'll get this error:
Agreed. I seem to have been quoted out of context in the OP's last message.--Gail ShawSQL Server MVP |
|
|
|
|
Tail backup