Restore DB from Windows Backup

Question

Hi,

I Run Full Windows backup of my Windows Server 2016 (backing up my full local C: drive) every day at 2:00 am.

I want to setup the Backup for my small DB in addition to that. This DB on SQL Server 2016 Pro.

When I opening Restore Database Wizard in Management Studio it looks like all systems and My_database has taken already (like extracted from full backup) and it offering me to restore my DBs from Windows Server backup (on external My Book USB drive).

My question: will I be able to use Windows Backup to restore my DBs via Restore Wizard from Management Studio?

Thank you in advance,

Alex

Answer 1

No, you will not be able to use windows backup to restore your databases via the restore wizard in management studio.

You will need to do a SQL server backup and have your windows backup software back that up. Then you can restore this SQL Server backup file from the windows backup, and then restore the sql server backup file.

Answer 2

Hello Alex,

Additional, in common SQL Server database file are permanently in use by SQL Server and so can not be backup with Windows backup function.

You have to performe native SQL Server backups to disk, then you can backup that SQL backup files in your Windows backup schedule, later restore from Windows backup and restore it with native SQL restore feature.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

Sorry for delay with response – a lot of problems today.

May I ask you, if that is a way, why some people mirroring that DB into the Share, or even do backup to the shared drive? Is that means they do not have Windows Backup?

Answer 4

Sorry for delay with response – a lot of problems today.

May I ask you, if that is a way, why some people mirroring that DB into the Share, or even do backup to the shared drive? Is that means they do not have Windows Backup?

Answer 5

Generally, you should use the built-in BACKUP command in SQL Server to backup your databases, if the are of any critical nature. Alternatively, use a third-party program which is dedicated to backup SQL Server databases. (And they really use the BACKUP command anwyay.)

I don't know about Windows Backup, but you can backup your databases with a general backup software that uses the Volume Shadowcopy Service. I don't know exactly how it works, but it freezes I/O on the databases while taking the backup. In fact, this is how I back up my databases at home, but I should quickly note that all I have at home is a collection of junk and lab databases. I would never rely on that for production databases.

You can check in the SQL Server errorlog whether Windows backup uses VSS; you will see messages about I/O freeze if it does.