Home / server / Questions / 801092
Accepted
Carlo
Asked: 2016-09-06 01:14:20 +0800 CST

Can I restore SQL databases from full Windows server backup if needed

  • 772

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

windows sql-server backup windows-server-backup database-restore

1 Answers

  1. Best Answer

    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.

    • 2