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

Is it possible to back up a SQL Server database, but only back up the Table structures and content (i.e. not include the stored procedures, views etc?)

The scenario:

We have a data centre hosted product with one unique database per customer.

For many of the customers, we are required to provide them with a copy of their data daily to import into their data warehouses for reporting purposes not served well from the live transactional system.

So, currently, we do a daily full backup of the production database, restore this to a "staging" server where we run a sanitisation script to remove views and stored procedures (as our IPR on the code in the Sps/views etc matters) and then we back that up and export to them.

This leads to large databases being sent daily.

They are finding the overhead of downloading and processing a 20gb backup file daily an overhead, and would like us to provide incremental backups.

The problem with this is that if we do a full back up to copy to the staging server, this anhilates the change tracking and a differential backup contains everything.

We tried a process of:

  1. Day 1: Full backup
  2. Restore to staging
  3. Run Sanitisation
  4. COPY_ONLY full backup.
  5. Send to Customer
  6. Day 2: COPY_ONLY full backup.
  7. Restore to staging
  8. Run Sanitisation
  9. Differential backup
  10. Send to customer

This does not work because the differential baseline is considered incorrect when restoring the differential we send out.

Now, if we could do some kind of backup that didn't include the SPs but remained incremental, that would solve the problem.

share|improve this question

4 Answers 4

A 'backup' as called in the SQL server world includes everything. It's designed to pick up the entire database and drop it either to disk or tape.

If you wanted to just grab the table schema and data, you want something closer to the 'Generate Scripts' functionality that I know is in SQL 2008, and probably earlier versions as well. Right click on the database, hit 'tasks' then 'Generate scripts' and follow the wizard through.

share|improve this answer

If you want to backup only your tables structure and their content without anything else, you cannot use the SQL Backup Command.

The tables structures can be scripted in order to have their structure. As for their content you can export using several tools like: SQL Export/Import tool or BCP utility.

Keep in mind that with this solution you cannot recover the database with its options and security, unless you script the Database. You will only have some files (.xls or .txt) to be imported into an existing database created already. The task is much to heavier and not very handy.

If you need all the tables from a database, I suggest performing a regular backup and delete the views or SP's after a restore. These don't take too much space though.

share|improve this answer
up vote 1 down vote accepted
  1. Backup production database.
  2. Restore as staging_extract.
  3. Remove the SPs etc (we have a script for this).
  4. Do a full backup, send this to the customer.
  5. Backup production database.
  6. Restore as staging_master
  7. Use Redgate SQL Data Compare to do a data compare and copy changed data into staging_extract
  8. Do a differential backup of staging_extract
  9. Send this to the customer.

Repeat steps 5 to 9 for a week/month, then return to step 1.

share|improve this answer

SQL Server 6.5 had the option of backing up individual tables, but this was removed in later versions. Regular SQL Server backup will backup the entire database.

Any specific reason why you need this functionality?

share|improve this answer
    
Yes, we need to provide differential backups of a production system, excluding Stored Procedures (IPR issues). Clarified question. –  THEMike Mar 30 '11 at 9:20
    
You might be able to do that with 3rd party software, like the backup application from RedGate –  Nils Magne Lunde Mar 31 '11 at 13:13

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.