CODEWITHSUNDEEP
sqlsql-serverssms
Jonathan De Badrihaye
Jonathan De Badrihaye

Reputation: 392

Create database backup, ignore column

I'd like to create a database backup using SSMS. The backup file will be a .bak file, but I would like to ignore 1 column in a certain table, because this column isn't necessary, but it takes up 95% of the backup size.

The column values should all be replaced by 0x00 (column type is varbinary(max), not null).

What's the best way to do this?

FYI: I know how to generate a regular backup using Tasks => Back Up..

Upvotes: 1

Views: 739

Answers (3)

sbiz
sbiz

Reputation: 321

You can create a copy of your table without the column and backup using filegroups https://msdn.microsoft.com/en-us/library/ms191539(SQL.90).aspx

Upvotes: 0

ughai
ughai

Reputation: 9890

There is a long way of doing what you ask. Its basically create a new restored database, remove the non required data and then do a new backup again.

  1. Create a Backup of the production database.
  2. Restore the backup locally on production with a new name
  3. Update the column with 0x00
  4. Shrink the database (Shrink is helpful when doing a restore. This wont reduce the bak file size)
  5. Take the backup of the new database (Also use Backup Compression to reduce the size even more)
  6. Ftp the bak file

If you only needed a few tables, you could have used bcp but that looks out of the picture for your current requirement.

Upvotes: 1

Polux2
Polux2

Reputation: 602

From SQL Server native backups, you can't. You'd have to restore the database to some other location and then migrate usefull data.

Upvotes: 1

Related Questions