Recently, I needed to take a snapshot of a database for my client.
We are setting up a demo site which anyone can access and modify data.
We want the database to reset to it's previous state every 24 hours.
This would remove any garbage records created by potential customers
taking the application for a test drive.
My development machine is running SQL Server 2005 Enterprise Edition
and I used the "Database Snapshot" feature to create my snapshot, then
scheduled a job to run daily at midnight to reset the database. Works
great. The problem is my client is running the less expensive SQL
Server 2005 Workgroup Edition.
Read more...
A strange "feature" to be sure. I've got an AIR Application I built
which includes collecting the date/time for an event. When I sync my
sql lite database with MSSQL Server via ColdFusion Componenets (cfc), I
found the time was 2 hours ahead.
Read more...
I've been working with SQL Server 2000 DTS (Data Transfer Services) for some
time. DTS allows you to drop and create tables as well as import or export
complete data sets between different database instances. For example,
I work with a local copy of the database and want to deploy changes to a staging
server database during the development process.
Recent experience with SQL Server 2005 has left me disappointed in Microsoft
yet again. My development environment is SQL Server 2000 running
on VMWare Fusion on a Mac. First, I discovered that you could not use
SQL Server 2000 DTS tools to update database schema/data in a SQL Server
2005 database. So ...
I purchase the developer edition of SQL Server 2005, which includes SQL Server
Management Studio. I
thought this would be the end of my difficulties, but I was wrong. DTS
in SQL Server 2005 does not create primary keys, indexes or default values. A
simple example would be an "id" field that auto increments.
| Your original data |
| id |
firstname |
lastname |
| 1 |
Jim |
Smith |
| 2 |
Bill |
Johnson |
| 4 |
Bob |
Jones |
| 5 |
Al |
Green |
| After DTS in SQL Server 2005 |
| id |
firstname |
lastname |
| 1 |
Jim |
Smith |
| 2 |
Bill |
Johnson |
| 3 |
Bob |
Jones |
| 4 |
Al |
Green |
As you can see, the
data is corrupted.
Others have blogged about this issue and indicate that Microsoft isn't moving
to solve these problem anytime soon. So, I've documented my work around
and created a pdf for others to use.
Here are the basic steps.
- Generate SQL Scripts to create your tables
- Use a third party utility called Simple SQL Bulk Copy. This will
preserve your primary key information during the export process.
Recent Comments