Backup SQL Database

[IchBistTod]

I was wondering if it would be possible to make an application to analyze an entire sql database (its about 3 gigs and growing) at the end of each day and see the changes that have been made from the previous day. After finding those changes then apply then to another database.

I ask this because I use SQL for a program called SOAPWare which has very important records that I need to back up.

However backing up the whole 3 gigs every day would take much too and use too much bandwidth.

If someone could show me maybe a small example I could expand on, or perhaps if there is a built in function I am missing, I would much appreciate it.

[BugFix]

There exists a lot of backup software with feature: incremental backup.

So you make one time an full backup and every day it will backup only changes.

[IchBistTod]

There exists a lot of backup software with feature: incremental backup.

So you make one time an full backup and every day it will backup only changes.

This is precisely what I wish to do.

I do not care if the way this is done is by making a wrapper and doing it via command line with another app, I just want to be able to make a GUI and fully control when it backs up, where it backs up to, be able to password protect it, integrate it into other apps etc.

However if no one knows of how to do this manually or knows a command line app to do it, then for now a link to a software that can do it will be fine.

[BugFix]

I use since many years True Image for

- Disk imaging

- Full, incremental and differential backups

- File backups

Price of 29,95 EUR its OK. Fast and sure software. Best way for disk cloning. I'am enthusiastic with this.

[seandisanti]

I use since many years True Image for

- Disk imaging

- Full, incremental and differential backups

- File backups

Price of 29,95 EUR its OK. Fast and sure software. Best way for disk cloning. I'am enthusiastic with this.

Check out triggers. you can setup triggers for adds, changes and deletes that create backup tables as they happen. then you just copy those tables out at the end of the day.

[jchd]

Depending on the DB engine, you might have access to versionning history, but I'd bet it's just simpler to rely on triggers.

[IchBistTod]

Check out triggers. you can setup triggers for adds, changes and deletes that create backup tables as they happen. then you just copy those tables out at the end of the day.

how do I set up triggers?

[seandisanti]

how do I set up triggers?

google is best bet, or sqlservercentral has several articles on triggers and all things sql

[jchd]

As we have no idea which SQL engine you're talking about, giving any generic hint is all you can expect. You might try something along these statements:

CREATE TRIGGER [trigInsTableA]
AFTER INSERT
ON TableA
FOR EACH ROW
BEGIN
    insert into TableAChanges select * from TableA where rowid = new.rowid
END

This assumes you have the right to change the schema, that you create a TableAChanges having the same columns than TableA...

[IchBistTod]

I am using SQL\MSDE

How do I implement this?

Do I save it as a file then load it as a script(in the control panel)?

Also I want to write the changes to a file, or somehow apply them to a different database.

The Idea is to have a second identical database on another server as a backup.

Edited August 10, 2010 by IchBistTod

[jchd]

I've never used MS SQL directly, but anyway CREATE TABLE ... , CREATE TRIGGER ... are all plain SQL statements so you should enter them the way you enter your other resultless SQL statements.

Creating a table to record changes allows you to query this table and apply the changes to your copy (in one shot if you can ATTACH the two bases).

Now if the ultimate goal is to have a backup elsewhere, you should look if there isn't a way to achieve this using MS SQL features (versionning?). Sync-ing DBs is generally very difficult and error-prone, but not so if the copy is left untouched (i.e. the only "live" base is the main one).

[IchBistTod]

As we have no idea which SQL engine you're talking about, giving any generic hint is all you can expect. You might try something along these statements:

CREATE TRIGGER [trigInsTableA]
AFTER INSERT
ON TableA
FOR EACH ROW
BEGIN
    insert into TableAChanges select * from TableA where rowid = new.rowid
END

This assumes you have the right to change the schema, that you create a TableAChanges having the same columns than TableA...

So you are suggesting I make identical tables for each table I have, appending the name "Changes" to it, then create these triggers for each one, then at the end of the day have a dump of these tables made, then apply them to the remote database?

[jchd]

That's what I'd do, for every table subject to meaningful change(s).

Now if the SQL engine supports ATTACH (I guess it should) you may attach the main DB to your local copy and perform the insert/replace from the query of the change tables:

ATTACH mainDB as ServerDB;

INSERT OR REPLACE INTO TableA select * FROM ServerDB.TableAChanges;

DETACH ServerDB;

Maybe MS uses the keyword REPLACE instead of INSERT OR REPLACE. Anyway, this is an atomic (all or nothing) operation.

You may need to adapt the SQL if ever there are constraints on TableA, foreign keys, triggers on this table, a.s.o.

This will work with "simple" tables, unless the main DB uses triggers to perform sideways actions on other tables.

If there may be DELETEs from some table you want to replicate, then you need to trap these as well in a separate TableADeletes table and apply them. But then, the question may arise about the right order to apply modifications. For instance, if a row is first deleted, then later re-created with the same rowid, you need to timestamp operations to replay them in the right order to your local copy. In the above example, the result would be different if you apply REPLACE [existing row] then DELETE vs. DELETE [existing row] then INSERT.

[IchBistTod]

That's what I'd do, for every table subject to meaningful change(s).

Now if the SQL engine supports ATTACH (I guess it should) you may attach the main DB to your local copy and perform the insert/replace from the query of the change tables:

ATTACH mainDB as ServerDB;

INSERT OR REPLACE INTO TableA select * FROM ServerDB.TableAChanges;

DETACH ServerDB;

Maybe MS uses the keyword REPLACE instead of INSERT OR REPLACE. Anyway, this is an atomic (all or nothing) operation.

You may need to adapt the SQL if ever there are constraints on TableA, foreign keys, triggers on this table, a.s.o.

This will work with "simple" tables, unless the main DB uses triggers to perform sideways actions on other tables.

If there may be DELETEs from some table you want to replicate, then you need to trap these as well in a separate TableADeletes table and apply them. But then, the question may arise about the right order to apply modifications. For instance, if a row is first deleted, then later re-created with the same rowid, you need to timestamp operations to replay them in the right order to your local copy. In the above example, the result would be different if you apply REPLACE [existing row] then DELETE vs. DELETE [existing row] then INSERT.

I have 6 hours to figure this out....

I greatly appreciate all help thus far.

I now proceed to ask that you please further explain ATTACH.

[jchd]

ATTACH allows you to access tables of another DB as part of your local statements. Google for MS SQL ATTACH. Again, I've zero direct experience with MS DB engines, but this has been standard SQL for a while now. How MS treats standards is another matter!

[seandisanti]

That's what I'd do, for every table subject to meaningful change(s).

Now if the SQL engine supports ATTACH (I guess it should) you may attach the main DB to your local copy and perform the insert/replace from the query of the change tables:

ATTACH mainDB as ServerDB;

INSERT OR REPLACE INTO TableA select * FROM ServerDB.TableAChanges;

DETACH ServerDB;

Maybe MS uses the keyword REPLACE instead of INSERT OR REPLACE. Anyway, this is an atomic (all or nothing) operation.

You may need to adapt the SQL if ever there are constraints on TableA, foreign keys, triggers on this table, a.s.o.

This will work with "simple" tables, unless the main DB uses triggers to perform sideways actions on other tables.

If there may be DELETEs from some table you want to replicate, then you need to trap these as well in a separate TableADeletes table and apply them. But then, the question may arise about the right order to apply modifications. For instance, if a row is first deleted, then later re-created with the same rowid, you need to timestamp operations to replay them in the right order to your local copy. In the above example, the result would be different if you apply REPLACE [existing row] then DELETE vs. DELETE [existing row] then INSERT.

One tip if you're using sql server, don't use the *, it locks the tempdb table while your query is running which can cause some issues in a production database. specifying column names avoids that and only takes a second longer if you're writing in query analyzer because you can just drag the "columns" folder out in place of the star and it will list all of the columns for the table.

[jchd]

You're right about * being the root of all evil in SQL (for important reasons beyond what you mention and valid whatever DB engine we're talking about).

For my defence, I'd invoke summer laziness and complete ignorance of MS SQL idiosyncrasies. I welcome better advises for the OP w.r.t. MS SQL features as her might exist much better solutions than what I came up with.

[seandisanti]

You're right about * being the root of all evil in SQL (for important reasons beyond what you mention and valid whatever DB engine we're talking about).

For my defence, I'd invoke summer laziness and complete ignorance of MS SQL idiosyncrasies. I welcome better advises for the OP w.r.t. MS SQL features as her might exist much better solutions than what I came up with.

Lol, i thought it was a universal no no, but i typically use sql server and didn't want someone following up saying i'm giving bad info i know dba's that still insist on using it in development db's, but i had a feeling that the OP was trying to do something in production so had to say something.

[jchd]

I sounds like I didn't make it clear that you were indeed giving a really good advice and you're welcome to correct me if MS SQL does things in other ways.

* is certainly fine for rush "sideways" operations and I find it fairly useful but stable production essentially demands column lists for many safety reason you know about.