How to  Secure Database Backup Files in sql 2012?

Posted on 2013-10-14
Medium Priority
Last Modified: 2013-12-26
How to Protect Database Backup Files in SQLserver 2012 even from system admin ?
Question by:ali_alannah
  • 3
  • 2
LVL 20

Expert Comment

ID: 39570865
Assuming I understand the question, and assuming you can run backups yourself (or per your own schedule):
Immediately afterwards, compress it with password protection.
Delete the .bak file
Store it in your own storage area.
For double protection store a copy in a mirror area.
I compress my backups with 7z, using the following command:
7z a -mx9 -t7z -p<mypassword> mydatabase.7z mydatabase.bak
The filename "mydatabase" will actually be my database_backup_yyyy_mm_dd.... as named by a maintenance plan backup.

My compression is fired off by a task after the maintenance plan finished. There are a number of ways you can fire off a task after a backup, so choose what works best for you.

Author Comment

ID: 39570886
Hi dsacker
Could you please tell me how to fire the the the compression with the password? and IS 7z  is built in window s? Please do more clarification ... Thanks
LVL 20

Expert Comment

ID: 39570896
See if you have C:\Program Files\7-Zip

If so, test it manually. Open a command prompt, go to where your backup is, and try the command (as I listed above). You may have to prefix it with the absolute reference (if it exists on your computer), i.e., "C:\Program Files\7-Zip\7z.exe"

Otherwise, install it. It's free.

Author Comment

ID: 39570944
Hello  dsacker
how to delete the file .bak  if am scheduling the backup as sql job i means how to determine if z7 finished then delete the *.bak?
LVL 20

Accepted Solution

dsacker earned 1000 total points
ID: 39570961
You would want to do that as an outside task. Take a look at your windows task scheduler. You should be able to set up a task to be triggered off the event that SQL leaves when it finishes a maintenance plan (assuming that's how you do your backup).

Take a look at the event viewer where your SQL 2012 is installed. Look at the application events. Look for any entries that come from SQL 2012, especially completion of maintenance plans.

Then you will need to play around with creating a task that fires off a .BAT file (that you must write), where you can do your compression.

You must have at least an initial understanding of these. They are not hard to understand, and they are well worth your time to dig in a little bit. Spend a little time with them, read up where necessary, then let me know when you are comfortable with them. Otherwise, my explanations will not make as much sense as they will after you play with learning those things first (event viewer and task scheduler).

You will like what you learn. They are really cool and can be used for far more than just this.

Featured Post

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
The Windows Firewall provides an important layer of protection and a rich interface to configure it. Unfortunately, it lacks item level filtering. This article details my process of implementing firewall-as-code to reduce GPO bloat.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question