Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2011-08-24 : 13:00:19
|
| If we don't have a recent SQL Server backup (.bak) but we have a backup of the Database file (.mdf), can we restore it (I guess by attaching)?If so, do we even need to do full Backups (ignoring the point-in-time possibilities with repect to the Transaction Logs)? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-08-24 : 13:29:38
|
| If the database was shut down cleanly, then yes, you should be able to attach it.No, you still need to make full database backups, since if the MDF file becomes corrupted or lost, you can still restore the database. You also should not ignore log backups, as they can provide a recovery option in case of unintended data issues (i.e., drop table, truncate table) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-24 : 13:42:21
|
| "do we even need to do full Backups"Backup can be done with the database running. Detach requires that the SQL Service is stopped, or the database is detached, so that's not an online task.There are circumstances (corruptions) where a database will detach and then no reattach, in those circumstances you will have wished that you were doing Backups instead!Backups only include the used parts of the database file. A copy of MDF file will include all the unused space too, and thus be larger.Attaching an MDF file to a server that already has a database of the same name is a bit fraught. Easier restoring a backup file to a new-name database. (e.g. if you wanted to restore last week's "backup" to a temporary database name in order to investigate something)Personally I wouldn't consider replacing Backups with MDF-file-copies ... but that may just be because I'm an old codger! and I also would not consider NOT having TLog backups (for OLTP databases). They have saved my bacon on numerous occasions ... |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-08-25 : 08:50:58
|
| Thanks.This is just for scenarios were there is no Database Management (before everyone says no DBA!!! this is the reality in most small non-IT companies). Such as a small program with 1 or 2 users and maybe they enter a couple orders a day or something: - If there's a hardware failure during the day- They are not doing SQL Server Database Backups- They are during nightly full Disk backups- And they don't expect to have an up to the minute restore but they do expect to have a restore from previous day (i.e. most recent OS backup)Sounds like in this type of scenario, it would be OK. |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2011-08-25 : 09:07:11
|
| If that was the case then I'd suspect the database would be very small and would not take a great deal of disk space or time to take sql backups.Therefore I'd take SQL Backups, to rely on restoring the OS in a DR situation is possible, but would be slow and not really something i'd recommend. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-25 : 09:13:49
|
| If I was selling a Mom & Pop small user applicaiton, which happened to be using SQL Server, I would provide them with Tlog backups.I reckon the support cost would be less, and the "quality" of support higher, if a "I did something stupid" could be resolved from TLog backups. Also, when you get a database corruption most times it can be fixed by restore last clean FULL backup, and then all TLogs to the present time - i.e. no lost of data."They are during nightly full Disk backups"Note that SQL Service must be stopped for that to work (i.e. copying the MDF file) |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-08-25 : 09:40:18
|
quote:
Originally posted by Kristen
Note that SQL Service must be stopped for that to work (i.e. copying the MDF file)
So if the file is backed up and the service is still running - would the only issue be that the file is missing modified pages which haven't been written to disk? And if so, I thought SQL Server frequently saves dirty pages to disk even in low activity. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-08-25 : 09:47:22
|
| It does, during a checkpoint, but unless you manually force a checkpoint you can't guarantee the data will be there.It's still better to make regular backups, even if no one expects to really need them. (that's when you need them the most) You cannot fully rely on OS file backups to suffice in case of emergency. |
|
|
|
|
|
|
|
|
Difference between OS backup and SQL backup