4

I have scheduled backup and every day copies of:

  • bare metal recovery
  • system state
  • system reserved
  • local disk C:\

Backup is stored on iscsi disk. On local disk C are databases mdf with logs. In theory can I restore databases from full windows server backup if there is something wrong with SQL server generated backup of databases like mydatabase.bak ?

Thanks

Carlo
  • 45
  • 1
  • 5

1 Answers1

2

It depends on if your SAN backups quiesce the data and log files (mdf and ldf files).

A SAN backup solution that does support SQL Server will hook into the SQL Server VDI interface and quiesce the database prior to taking the backup. If you were to run such a backup and then look in the SQL error log, there will be messages stating that the IO was frozen on the database.

...

If you are relying on SAN backup, you’re still going to have to check for database consistency, either by running DBCC checks on the live database, or by restoring a database from a SAN backup and running the checks on that. Otherwise, you may just be backing up a corrupted database.

If they do, then in theory, yes you could restore your databases from these files alone.
However it is not only frowned upon, but downright considered a bad idea by most database administraotrs..

As far as I'm concerned, a file system backup of mdf/ndf/ldf files is NOT a backup you can rely on. If you have any hope of recovery, you need to use proper, native SQL Server backups. Anything less exposes you to a lot of risk, could be detrimental to your career, and may even jeopardize the viability of your entire company.

If you're only using this as a fall back in case your actual native backups fail.
I don't think anyone will fault you for that.
But again, I would never consider it a good backup solution in its own right.

Reaces
  • 5,547
  • 4
  • 36
  • 46
  • In addition to what Reaces has said here @carlo - have you actually tried doing a restore of databases recovered this way onto another server? Having this as your fallback position is one thing but proper SQL-aware backup routines (such as SQL itself backing up to a .bak file) are going to be much more reliable than any other method. I would count myself as very lucky if a restore like this worked properly. – Rob Moir Sep 05 '16 at 10:28
  • Thanks for answer and comments. Yes, I have proper(I hope) native SQL backups and this is only as fallback. I didnt try to restore databases that way. I needed to ask first to know is it worth it. – Carlo Sep 05 '16 at 10:37
  • @RobM Not sure if you commented before I added that part, but I did add a quote recommending he restores a database form the SAN backup and run DBCC checks on it :) – Reaces Sep 05 '16 at 10:47
  • @Carlo Well, if this is just your fallback in case your native backups are working, I don't see much wrong with it. Just make sure you test it first. And of course... regularly test your native backups! – Reaces Sep 05 '16 at 10:48