Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-02-04 : 08:30:39
|
| Jims writes "The company I work for hosts an online application with a SQL 2000 database for each client. Some clients are requesting a Data-Only backup and our company does not want to give them its proprietary procedures, views etc. Would like to set up a DTS package/job to do the backups.Is there a way to just back up the tables and their data? orCan I restore a database to a temp database and then drop all sprocs and views in a cursor?orDo I need to create a script with each individual Drop statementWhat would be your recommendation on this subject?Thanks!" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-04 : 08:34:25
|
| There are a few ways to approach this:1. Encrypt the views and stored procedures. This is probably the easiest way, BUT, you MUST be using source control on ALL of your procedures and views. Otherwise you risk losing your code forever. As an aside, SQL Server encryption is not particularly strong and a dedicated person can decrypt it without too much effort.2. Create extra filegroups for your database and put all non-system tables on the other filegroups. You can then backup just the filegroups and send them to the client. However, this is a bit more admin work than most people would be used to and may not be practical for inexperienced clients.3. BCP out the data tables and send the files to the customer. They can use bcp or BULK INSERT to import the data. This is even more work than filegroup backups, but is pretty fast and offers a lot more control over which tables are sent.4. DTS would also work like bcp, but is much more labor intensive to set up. Once it's set up though it is easier to run than a bcp operation. |
 |
|
|
HendersonToo
Starting Member
20 Posts |
Posted - 2004-02-04 : 12:39:49
|
| Wanted to add this:5. Write views containing denormalized representations of all the data, and BCP/DTS these tables. You won't have to give up any proprietary design of the database that might have been the result of toiled performance analysis. But they'll have their data. Seems to make the clients happy. |
|
|
|
|
|
|
Data only backups