Online Backup and Offline Backup

Question

Hi All,

Can any one explain detail what is difference between online and offline backups?

---

Raveendra

Answer 1

Hello Raveendra,

In common you use only online backups for SQL Server; in Oracle there is also a offline strategy, but not (really) for SQL Server.

For a offline backup you would shutdown the SQL Server service and backup the MDF/NDF/LDF file with a file backup. But this is unsafe and it requires a downtime for SQL Server = no 24/7 service, so you should always use online backups.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

what ever Olaf said that is correct...

http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/c665854e-30b9-46b9-a586-ad908affdb46/

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

Hello Raveendra,

In common you use only online backups for SQL Server; in Oracle there is also a offline strategy, but not (really) for SQL Server.

For a offline backup you would shutdown the SQL Server service and backup the MDF/NDF/LDF file with a file backup. But this is unsafe and it requires a downtime for SQL Server = no 24/7 service, so you should always use online backups.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Hi Olaf

Could  you clarify what you mean by unsafe ? A recovery would be run when attaching the files. There is a downtime indeed, but it is still safe with regard to transactionnal consistency. 

---

David B.

Answer 4

Unsafe is due to Many ways-

as we know that Incase if need to have sql offline backup the it requires to completely stop the SQl server services and move the files where ever you want by using your xp_cmd command, incase of the server is very critical then why you will go for shutdown the SQL server servivces instead of that you can go through the online backup where users can perform their activity with out an interrupt of SQL server or DBs down......

also Imagine that during the offline backup--the files moves from one drive to another during that if the file get corrupted or locked then there might be an chance of DB 's goes ot suspect mode then think about you do not have the backup in place bcz you are following the offline backup.

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 5

SQL Server does not use offline backups but if you are using SQL Server 2012 you can use AlwaysOn and offload the backup process to a replica.  You can find more information on backups from active secondaries here: http://msdn.microsoft.com/en-us/library/hh245119.aspx

---

Jonathan Gardner PMP
Website: http://jonathanagardner.com
Twitter: jgardner04

Answer 6

There is no offline backup for SQL Server.Could you confirm the reason to look for offline backup ? There might be better solutions which we can recommend after understanding your requirements.

Thank you,

 

---

Anup | Database Consultant

Blog: www.sqlsailor.com Twitter: Follow me !

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 7

As the name suggests, online backup means the backup taken when your server is online means live & offline backup means taking backup when your server is down.

But the sql server only supports the online backup. 

We cant take offline backup in sql server & its unsafe also as you need to take your server down which can cause many problems.

Hope you will find it needful :)

Regards,

Vimarsh 

Answer 8

I normally copy the mdf & ndf files of all the system databases pre and post deployments for Service Packs & Hotfix. Just an approach, for a quick fall back, if required.

---

SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )