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:
- Day 1: Full backup
- Restore to staging
- Run Sanitisation
- COPY_ONLY full backup.
- Send to Customer
- Day 2: COPY_ONLY full backup.
- Restore to staging
- Run Sanitisation
- Differential backup
- 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.
Best Answer
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.