Ideally, Large tables actually belong to a separate database directory, so customers can back up their “data” and their “configuration” separately. However, I don’t have permission to make this type of change, so I’m looking for a good way to export or Copy the configuration part without copying the whole part.
Currently I have a fast and dirty .NET application that uses SQL Adapter and DataSet to blindly select from all tables *except those I want to omit , Dump it to an XML file (this is my poor man’s export function). I have a companion app that I can use locally to load the XML back into a DataSet and then use SQLBulkCopy to load it into my development database. I had to do some tricks to disable constraints and so on, but I made it succeed. This allowed me to get the data, but not the pattern… Ideally, I want a way to get the pattern. This is kind of brutal Power method, I believe there must be a simpler method. Any suggestions on a better method?
The solution needs to be automated because I am dealing with end users who usually have little IT support.
(SQL 2005 and later)
>Right click on the database
>Select Task ->Generate Script
>(If you get the introduction screen, click “Next”)
>Select ” Select specific database objects”
>Select objects to generate scripts (tables, stored procedures, etc…)
>Click “Next”, and then specify the output file name
>Click “Finish” to generate scripts
This will only generate the mode. If you want to execute the data generation script, please click the “Advanced” button in step 6) and scroll down to “Script data type”, and then change it from ” Change the “Schema Only” to “Data Only” or “Schema and Data”
In your case, you can execute “Schema and Data” for all small configuration tables, and then do not want to export data for large The table performs “schema only”.
I know this is not fully automated, but it is very close. If you want to look at automation further, please check this topic. Not sure if this applies to SQL 2005:
p>
How can I automate the “generate scripts” task in SQL Server Management Studio 2008?
My product uses SQL Server database-each client I have my own deployment instance on my Intranet. There are about 200 tables in db. Most of them are configuration tables with only a few rows, but there are several transaction data tables that may have millions of rows. I often need to solve The customer’s configuration problem, so I need a copy of their database to work locally in my development system… But since the transaction data can be very large, it makes it difficult for the customer to send me a backup job. I need a way to backup/ Copy/export everything except large tables.
Ideally, large tables actually belong to a separate database directory, so customers can back up their “data” and “configuration” separately .However, I don’t have permission to make this type of change, so I’m looking for a good way to export or copy the configuration part without copying the whole part.
Currently I have a quick and dirty A .NET application, which uses SQL Adapter and DataSet to blindly select from all tables* except the ones I want to omit, dump it to an XML file (this is my poor man’s export function). I have a companion app Program, I can use locally to load the XML back into the DataSet and then use SQLBulkCopy to load it into my development database. I had to do some tricks to disable constraints and so on, but I got it to succeed. This got me Data, but not patterns… Ideally, I want a way to get patterns. This is brute force I believe there must be a simpler way. Any suggestions on a better way?
The solution needs to be automated because I am dealing with end users who usually have little IT support.
(SQL 2005 and later)
Can you generate scripts directly from SSMS?
>Right click on the database
>Select Task ->Generate Script
>(If you get the introduction screen, click “Next”)
>Select ” Select specific database objects”
>Select objects to generate scripts (tables, stored procedures, etc…)
>Click “Next”, and then specify the output file name
>Click “Finish” to generate scripts
This will only generate the mode. If you want to execute the data generation script, please click the “Advanced” button in step 6) and scroll down to “Script data type”, and then change it from ” Change the “Schema Only” to “Data Only” or “Schema and Data”
In your case, you can perform “Schema and Data” for all small configuration tables, and then perform the The table executes “schema only”.
I know this is not fully automated, but it is very close. If you want to look at automation further, please check this topic. Not sure if this applies to SQL 2005:
p>
How can I automate the “generate scripts” task in SQL Server Management Studio 2008?