Home / server / Questions / 253064
Accepted
THEMike
Asked: 2011-03-30 00:48:42 +0800 CST

Backing up a SQL Server 2008 R2 Database without stored procedures

  • 772

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.

backup sql-server-2008 sql

5 Answers

  1. 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.

    • 1

  2. 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.

    • 1
  3. Best Answer
    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.

    • 1

  4. Its very simple.You need to generate a script for that. Right click on your database name from the object explorer and follow these steps - Task> Generate Script> Next> Select Specific database objects> select table and click next> Click "Advance

    A pop up windows will appear named "Advance Scripting Options". Find "Type of data to script" under "General" and change it to "Schema and Data". Then Press "Ok".

    • 1

  5. 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?

    • 0