windows server backup. how to restore msssql database

Posted on

A server stack is the collection of software that forms the operational infrastructure on a given machine. In a computing context, a stack is an ordered pile. A server stack is one type of solution stack — an ordered selection of software that makes it possible to complete a particular task. Like in this post about windows server backup. how to restore msssql database was one problem in server stack that need for a solution. Below are some tips in manage your windows server when you find problem about windows, sql-server, backup, windows-server-2012-r2, database-restore.

it’s theoretical question. If i have a window server 2012 r2 which have mssql server installed. I add role windows server backup.

So can restore the database which was backuped by windows server backup. How? just attach mdf and ldf files from backup?

P.S. I know that MSSQL databases must be backuped up by there internal utilites and the Windows server backup is not a good for this. But i saw in mssql logs that windows server backup uses vss for sql too and it may be consistent.


Review the following answer:

How can I backup an SQL Server database using Windows Server Backup?

If you wish to backup your SQL server, it is recommended that you use commercial software (for example, Redgate or Idera) or – if you wish to stay free-ish – use Ola’s scripts:

What George said. I just wanted to add that:

Theoretically, you can indeed restore SQL Server from Windows Server Backup. (You would detach the old database, rename the old files, put the .mdf and .ldf back in the right places and use the attach feature.) It’s not generally recommended, however (unless you’ve had some horrible disaster that took out your actual SQL backups). This is because uncommitted transactions might not be on disk yet, and also because log backups do some additional housekeeping for databases in Full Recovery.

How Bad [TM] this is depends on your database. If your database is basically the brains of a piece of commercial software that you never interact with on a SQL level, this is considerably Less Bad than if your database is used for banking or financial data with a high transaction volume, for example.

Personally, even when using SQL Express or Windows Internal Database I back up with actual SQL backups rather than relying on Windows Server Backup. (I use task scheduler to kick off Ola Hallengren’s scripts with SQLCMD.)

(In my opinion, there’s nothing wrong with the built-in SQL Server backup commands. Many people find commercial GUIs or other people’s scripts easier to use, however.)

Leave a Reply

Your email address will not be published. Required fields are marked *