Jump to content
Sign in to follow this  
IchBistTod

Backup SQL Database

Recommended Posts

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.


[center][/center][center]=][u][/u][/center][center][/center]

Share this post


Link to post
Share on other sites
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.


Best Regards BugFix  

Share this post


Link to post
Share on other sites
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.


[center][/center][center]=][u][/u][/center][center][/center]

Share this post


Link to post
Share on other sites
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. :blink:


Best Regards BugFix  

Share this post


Link to post
Share on other sites
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. :blink:

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.

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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?


[center][/center][center]=][u][/u][/center][center][/center]

Share this post


Link to post
Share on other sites
seandisanti

how do I set up triggers?

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

Share this post


Link to post
Share on other sites
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...


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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 by IchBistTod

[center][/center][center]=][u][/u][/center][center][/center]

Share this post


Link to post
Share on other sites
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).


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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?

[center][/center][center]=][u][/u][/center][center][/center]

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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.


[center][/center][center]=][u][/u][/center][center][/center]

Share this post


Link to post
Share on other sites
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!


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×