Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have MS SQL Server 2008 available to me. Is it possible to script (perhaps with Powershell?) the processing of all my backup files to drop a table (or two) from each one? I used 7-Zip on the command line to encrypt them so this script will have to be able to handle that.

I'd really like to avoid having to decrypt and then attach each one by hand. That's just not going to cut it but my skills don't afford me the knowledge to know if there's a better.

I'm not asking for a full solution by any means. I'd like to know whether this is actually possible and how someone might go about doing it first.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Sure, it's possible...

From a batch file you can use osql. From powershell you have your choice of osql, Sql Powershell or ADO.Net (SqlConnection and friends).

To unzip and decrypt, you can use the -pyourpasswordhere option with extract.


General script should look like this:

  • Get list of 7z files, store to variable
  • Loop on each file
    • Extract file using 7z command line to temp folder
    • Somehow execute (RESTORE DATABASE dbname FROM DISK = 'path to temp bak file' WITH MOVE 'datafile name' to 'temp file path\data.mdf', MOVE 'logfile name' to 'temp file path\log.ldf', RECOVERY, STATS=10)
    • Switch to databse and drop table: (USE dbname; DROP TABLE tableName; DROP TABLE tableName2; USE master;)
    • Create new backup (BACKUP DATABASE dbname TO DISK = 'path to new bak file' WITH COPY_ONLY, STATS=10 - COPY_ONLY can be used if log file restores are not used to speed up backup and decrease size)
    • Drop the database from the server "DROP DATABASE dbname;"
    • Encrypt and compress the created file using the 7z command line
    • Clean up all temporary files
share|improve this answer
    
As far as I'm aware it's not possible to directly modify the data within a SQL backup set which is what the poster appears to be asking for. –  Chris McKeown Jun 13 '13 at 22:55
    
upvoted though :-) –  Chris McKeown Jun 13 '13 at 22:59
    
@ChrisMcKeown No, I knew I would have to go through all those steps I just had no idea how to carry it out automatically. –  idon'twearsuits Jun 14 '13 at 15:58
    
Wow. Great response. Thank you. –  idon'twearsuits Jun 14 '13 at 15:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.