OS backup

Question

Hello.

New MSSQL guy coming from Oracle.  I've read and understand that you can create backup files for a database using the server management studio.

However, I'm wondering if that is really necessary.  Suppose I do not care about the loss of transaction logs, is copying the .mdf database files via OS command a viable backup strategy? If so do I need to bring down the database engine before starting the file copy?

Thanks a lot

Answer 1

You can get a valid backup of a SQL Server database by shutting down the instance and then taking a full OS backup, but you do lose significant functionality by doing so:

• Obviously, you're giving up the ability to take your backups with the database online.  Native SQL database backups are all online.
• You lose the option of having a point-in-time restore.  By using TSQL backups combined with transaction log backups in Full recovery mode, you can roll your database forward to any arbitrary point in time.  This is useful for restoring a database and rolling it forward to JUST BEFORE that DROP TABLE was issued...
• You lose the option of using differential backups to limit the size of your daily database backups.

If you are concerned about the added complexity of getting the database backups to tape, you could use TSQL to back your database up to disk file(s), and then let the OS backup copy them off to tape.  That way you get the best of both worlds.

Answer 2

Thank you Kevin.

Our production operations run on Oracle systems. We have to run 1 small MSSQL instance since some MSFT application only use that. So, I'm not really concerned about that DB's size, around-the-clock availibility, or point-in-time recovery.

Given the above requirements and the fact that I have no experience in MSSQL, backing up via the OS route seems to be the path of least resistence :)

Thanks again!