0

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.

Chris76786777
  • 979
  • 5
  • 21
  • 35

1 Answers1

3

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
Mitch
  • 2,363
  • 14
  • 23