I have built a SQL Server Express database that is going to be housed on an external hd. I need to be able to add/update data on the database that is on my system, as well as other systems and then only backup or transfer data that has been added or edited to the external hard drive. What is the best way to accomplish this?
A:
You would probably use replication for this but as you're using SQL Server express this isn't an option.
You'll need some sort of mechanism to determine what has changed between backups. So each table will need a timestamp or last updated date time column that's updated every time a record is inserted or updated. It's probably easier to update this column from a trigger rather than from your application.
Once you know which records are inserted or updated then it's just a matter of searching for these from the last time the action was performed.
An alternative is to add a bit column which is updated but this seems less flexible.
Dave Barker
2009-12-02 03:08:03
A:
Sherry, please explain the application and what the rationale is for your design. The database does not have any mechanism to do this. You'll have to track changes yourself, and then do whatever you need to do. SQL Server 2008 has a change tracking feature built in, but I don't think that will help you with Express.
Also, take a look at the Sync Framework. Adding this into your platform is a major payload, but if keeping data in sync is one of the main objectives of your app, it may pay off for you.
cdonner
2009-12-02 03:12:57
A:
In an application
If you are doing this from an application, every time a row is updated or inserted - modify a bit/bool column called dirty and set to true. When you select the rows to be exported, then select only columns that have dirty set to true. After exporting, set all dirty columns to false.
Outside an application
DTS Wizard
If you are doing this outside of an application, then run this at the Command-Line:
Run "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe"
This article explains how to get the DTS Wizard (it is not included as default).
It is included in the SQL Server Express Edition Toolkit – and only that. It you have installed another version of SSE, it works fine to install this package afterwards without uninstalling the others. Get it here: http://go.microsoft.com/fwlink/?LinkId=65111
The DTS Wizard is included in the option “Business Intelligence Development Studio” so be sure to select that for install
If you have installed another version of SSE, the installer might report that there is nothing to install. Override this by checking the checkbox that displays the version number (in the installer wizard)
After install has finished, the DTS Wizard is available at c:\\Microsoft SQL Server\90\DTS\Binn\dtswizard.exe you might want to make a shortcut, or even include it on the tools menu of SQL Studio.
The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data > file in a user-specified format. The bcp utility can be used to import large numbers of > new rows into SQL Server tables or to export data out of tables into data files. Except > when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or > understand the structure of the table and the types of data that are valid for its columns.
0A0D
2009-12-02 03:13:05