How to Secure Database Backup Files in sql 2012?

How to Protect Database Backup Files in SQLserver 2012 even from system admin ?
ali_alannahAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
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.
0
ali_alannahAuthor Commented:
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
0
dsackerContract ERP Admin/ConsultantCommented:
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.
0
ali_alannahAuthor Commented:
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?
0
dsackerContract ERP Admin/ConsultantCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.