Database Schema scripting "Patrick Jackman" <pjackman[ at ]wimse.com> 03.03.2006 19:13:18 At http://www.trigeminal.com/code/basScriptJetSecurity.bas Michael Kaplan offers a module to script out security permissions in a database. Does anyone know of a similar script for saving/restoring a database schema? Patrick =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Patrick Jackman Microsoft Access Developer since 1994 Vancouver, BC

Re: Database Schema scripting "Douglas J Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 03.03.2006 19:29:19 There are 3rd party products out there that can do it. Unfortunately, I can't remember their names off the top of my head! What you're looking for is a product that claims it can generate a script to bring one database up to the same version as another database (such as SSW's DataRenovator http://www.ssw.com.au/ssw/DataRenovator/), and then point it to an empty database so that generates the script for the entire database. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Patrick Jackman" <pjackman[ at ]wimse.com> wrote in message news:O5AjuZvPGHA.2496[ at ]TK2MSFTNGP11.phx.gbl... [Quoted Text] > At http://www.trigeminal.com/code/basScriptJetSecurity.bas Michael Kaplan > offers a module to script out security permissions in a database. Does > anyone know of a similar script for saving/restoring a database schema? > > Patrick > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > Patrick Jackman > Microsoft Access Developer since 1994 > Vancouver, BC > >

Re: Database Schema scripting "Patrick Jackman" <pjackman[ at ]wimse.com> 03.03.2006 19:58:13 Thanks Doug. That makes sense. I'd prefer to build something though. I have an 800 MB Access 97 database that I'd like to live-backup every 2 hours during business hours. I was planning to incorporate Gary Robinson's approach of using ADO.Recordset.Save adPersistADTG. When I need to recover it would be great to rebuild the database from a script, import the data from the saved datagrams, then reestablish security using a script like Michael's. I've created databases, tables, fields and relationships in code for specific projects so I'm familiar with all the objects, methods and properties. Michael's module gives a good example of how to create a script. Before I dig in, I just wanted to ask if someone has already written a module that will script the creation of an Access database given a file reference. Patrick. "Douglas J Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> wrote in message news:OZzSGjvPGHA.4952[ at ]TK2MSFTNGP09.phx.gbl... There are 3rd party products out there that can do it. Unfortunately, I can't remember their names off the top of my head! What you're looking for is a product that claims it can generate a script to bring one database up to the same version as another database (such as SSW's DataRenovator http://www.ssw.com.au/ssw/DataRenovator/), and then point it to an empty database so that generates the script for the entire database. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Patrick Jackman" <pjackman[ at ]wimse.com> wrote in message news:O5AjuZvPGHA.2496[ at ]TK2MSFTNGP11.phx.gbl... [Quoted Text] > At http://www.trigeminal.com/code/basScriptJetSecurity.bas Michael Kaplan > offers a module to script out security permissions in a database. Does > anyone know of a similar script for saving/restoring a database schema? > > Patrick > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > Patrick Jackman > Microsoft Access Developer since 1994 > Vancouver, BC > >

Re: Database Schema scripting Mattias Jonsson <news[ at ]ett-remove-idesystem.se> 03.03.2006 21:16:13 Patrick, It might just be me, but it sounds like you are pushing the practical limits of Access with such a large mission critical database. If, for example you went for a MySQL (or similar RDBMS) back end database then you could have DDL scripts for restoring the structure of the database as well as user permissions. You would restore the data the same way as you describe for Access. Changing over might take no more work than building the maintenance application you are describing. Good luck Mattias From: Patrick Jackman, on 3/3/2006 2:58 PM: [Quoted Text] > Thanks Doug. That makes sense. I'd prefer to build something though. > > I have an 800 MB Access 97 database that I'd like to live-backup every 2 > hours during business hours. I was planning to incorporate Gary Robinson's > approach of using ADO.Recordset.Save adPersistADTG. When I need to recover > it would be great to rebuild the database from a script, import the data > from the saved datagrams, then reestablish security using a script like > Michael's. > > I've created databases, tables, fields and relationships in code for > specific projects so I'm familiar with all the objects, methods and > properties. Michael's module gives a good example of how to create a script. > > Before I dig in, I just wanted to ask if someone has already written a > module that will script the creation of an Access database given a file > reference. > > Patrick. > > "Douglas J Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> wrote in message > news:OZzSGjvPGHA.4952[ at ]TK2MSFTNGP09.phx.gbl... > There are 3rd party products out there that can do it. Unfortunately, I > can't remember their names off the top of my head! > > What you're looking for is a product that claims it can generate a script to > bring one database up to the same version as another database (such as SSW's > DataRenovator http://www.ssw.com.au/ssw/DataRenovator/), and then point it > to an empty database so that generates the script for the entire database. >

Re: Database Schema scripting "Patrick Jackman" <pjackman[ at ]wimse.com> 04.03.2006 08:16:53 Mattias, The database is large due mostly to a couple of tables that hold historical account aging information. The largest transactional table has fewer than 100,000 rows. The mid-day archiving would not include the larger tables. It's client and network -based outages that I want to protectect against with bi-hourly backups of the mdb. We do plan to switch the application over to MSDE or SQL Server Express before the end of the year but in the interim I would like to protect the data. I used Winzip for this purpose but as the file size grew we concluded that the zipping process was yeilding corruption. Unfortunately, changing over will be a bit of a chore. The front end is over 25 MB. Many objects will need recoding. Wouldn't scripting for recovery be less necessary with a product like SQL Server? I would think that daily backups should be adequate. Patrick. "Mattias Jonsson" <news[ at ]ett-remove-idesystem.se> wrote in message news:u96Z2ewPGHA.3728[ at ]tk2msftngp13.phx.gbl... Patrick, It might just be me, but it sounds like you are pushing the practical limits of Access with such a large mission critical database. If, for example you went for a MySQL (or similar RDBMS) back end database then you could have DDL scripts for restoring the structure of the database as well as user permissions. You would restore the data the same way as you describe for Access. Changing over might take no more work than building the maintenance application you are describing. Good luck Mattias From: Patrick Jackman, on 3/3/2006 2:58 PM: [Quoted Text] > Thanks Doug. That makes sense. I'd prefer to build something though. > > I have an 800 MB Access 97 database that I'd like to live-backup every 2 > hours during business hours. I was planning to incorporate Gary Robinson's > approach of using ADO.Recordset.Save adPersistADTG. When I need to recover > it would be great to rebuild the database from a script, import the data > from the saved datagrams, then reestablish security using a script like > Michael's. > > I've created databases, tables, fields and relationships in code for > specific projects so I'm familiar with all the objects, methods and > properties. Michael's module gives a good example of how to create a > script. > > Before I dig in, I just wanted to ask if someone has already written a > module that will script the creation of an Access database given a file > reference. > > Patrick. > > "Douglas J Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> wrote in message > news:OZzSGjvPGHA.4952[ at ]TK2MSFTNGP09.phx.gbl... > There are 3rd party products out there that can do it. Unfortunately, I > can't remember their names off the top of my head! > > What you're looking for is a product that claims it can generate a script > to > bring one database up to the same version as another database (such as > SSW's > DataRenovator http://www.ssw.com.au/ssw/DataRenovator/), and then point it > to an empty database so that generates the script for the entire database. >

Re: Database Schema scripting Mattias Jonsson <news[ at ]ett-remove-idesystem.se> 04.03.2006 14:19:23 Patrick, This may be overly cautious, but I don't trust the server backups any farther than I can throw them for being able to restore "my" data. For one, I'm not in charge of the server so I feel I have to take precautions in (the unlikely) case it's being mis-managed. Also, the server backups are proprietary; readable only by the server, or some "backup miner" software. The recovery of server backups are only guaranteed to be able to restore the database on the very same server for which you did the backup. But if you have "create table" and other DDL scripts then you could restore the structure of your database onto a re-built server that's a different version, or with some minor tweaks even a different vendor's server. There is nothing in my mind that changes this fact whether you go with MySQL, MS SQL Server, Oracle, or any other RDBMS vendor. Backups can for all practical purposes only be used to recover from catastrophic failure. For the databases that I am responsible for, in addition to keeping the DML on hand, I back up the actual data in all tables on a daily or weekly basis (more or less frequently depending on the need). It's basically done like this: SELECT * INTO [TABLE_NAME] IN 'FILE_NAME' FROM [QUERY_NAME] The query specified by [QUERY_NAME] is a passthrough query that selects * from each table found in a data dictionary view in the database server. Once the data is extracted onto another system like this, I zip it up to save space and keep it on multiple hard drives. Because I'm zipping an mdb that's not in use, there is no danger of getting a corrupt zip file. One advantage of this extra step beyond the server backups is that I have available true "snapshots in time" of what the data looked like. It's a lot of work to unzip and poke around to find where things started going wrong, but it _is_ possible. The server backup tapes are typically recycled in about a week or two. The way I have worked this out for my situation, I feel fairly confident that no more than a day's worth of data could be lost, even if the server blows up and the backups have not been running (it's been known to happen). I do have other clients who have mdb back-end databases (with very important data). I'm always nervous that some teenage child of one of the authorized users will find the front-end application and start messing with things in the back-end database. This is not a problem if you have an Oracle or other back-end with proper audit tables etc. set up. Like you say, it comes down to a matter of time and money. Maybe none of this applies to you since I don't know enough about your situation, but it has worked well for me. Good luck Mattias From: Patrick Jackman, on 3/4/2006 3:16 AM: [Quoted Text] > Mattias, > > The database is large due mostly to a couple of tables that hold historical > account aging information. The largest transactional table has fewer than > 100,000 rows. The mid-day archiving would not include the larger tables. > It's client and network -based outages that I want to protectect against > with bi-hourly backups of the mdb. > > We do plan to switch the application over to MSDE or SQL Server Express > before the end of the year but in the interim I would like to protect the > data. I used Winzip for this purpose but as the file size grew we concluded > that the zipping process was yeilding corruption. > > Unfortunately, changing over will be a bit of a chore. The front end is over > 25 MB. Many objects will need recoding. > > Wouldn't scripting for recovery be less necessary with a product like SQL > Server? I would think that daily backups should be adequate. > > Patrick. > > > "Mattias Jonsson" <news[ at ]ett-remove-idesystem.se> wrote in message > news:u96Z2ewPGHA.3728[ at ]tk2msftngp13.phx.gbl... > Patrick, > It might just be me, but it sounds like you are pushing the practical > limits of Access with such a large mission critical database. If, for > example you went for a MySQL (or similar RDBMS) back end database then > you could have DDL scripts for restoring the structure of the database > as well as user permissions. You would restore the data the same way as > you describe for Access. Changing over might take no more work than > building the maintenance application you are describing. > > Good luck > Mattias > > From: Patrick Jackman, on 3/3/2006 2:58 PM: >> Thanks Doug. That makes sense. I'd prefer to build something though. >> >> I have an 800 MB Access 97 database that I'd like to live-backup every 2 >> hours during business hours. I was planning to incorporate Gary Robinson's >> approach of using ADO.Recordset.Save adPersistADTG. When I need to recover >> it would be great to rebuild the database from a script, import the data >> from the saved datagrams, then reestablish security using a script like >> Michael's. >> >> I've created databases, tables, fields and relationships in code for >> specific projects so I'm familiar with all the objects, methods and >> properties. Michael's module gives a good example of how to create a >> script. >> >> Before I dig in, I just wanted to ask if someone has already written a >> module that will script the creation of an Access database given a file >> reference. >> >> Patrick. >> >> "Douglas J Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> wrote in message >> news:OZzSGjvPGHA.4952[ at ]TK2MSFTNGP09.phx.gbl... >> There are 3rd party products out there that can do it. Unfortunately, I >> can't remember their names off the top of my head! >> >> What you're looking for is a product that claims it can generate a script >> to >> bring one database up to the same version as another database (such as >> SSW's >> DataRenovator http://www.ssw.com.au/ssw/DataRenovator/), and then point it >> to an empty database so that generates the script for the entire database. >> > >

Re: Database Schema scripting "Patrick Jackman" <pjackman[ at ]wimse.com> 04.03.2006 22:16:47 Mattias, Thanks for sharing your rationale and strategies for data backup. You have provided me with some very helpful information. I like your approach of inserting data into an .mdb file. I could do that in this situation rather than using datagrams and I would have far fewer files to deal with as well. I was keeping a 3-deep backup of zip files so we could restore up to six hours back in the event that an issue wasn't detected immediately. Zipping inactive files is the way to go. The server is running Veritas Backup Exec but I'm not on site to monitor how reliably things are working, so your advice about server database systems is appreciated. Patrick.

Re: Database Schema scripting Mattias Jonsson <news[ at ]ett-remove-idesystem.se> 05.03.2006 13:51:54 Patrick, No problem! In my situation, I back up the tables when there is no activity (or I reasonably expect no activity). I don't know what would happen if this strategy is applied to a "live" database. You may get some records backed up that violate FK constraints and such. But in a less than perfect world, this may be the best bet. Good luck - Mattias From: Patrick Jackman, on 3/4/2006 5:16 PM: [Quoted Text] > Mattias, > > Thanks for sharing your rationale and strategies for data backup. You have > provided me with some very helpful information. > > I like your approach of inserting data into an .mdb file. I could do that in > this situation rather than using datagrams and I would have far fewer files > to deal with as well. I was keeping a 3-deep backup of zip files so we could > restore up to six hours back in the event that an issue wasn't detected > immediately. Zipping inactive files is the way to go. > > The server is running Veritas Backup Exec but I'm not on site to monitor how > reliably things are working, so your advice about server database systems is > appreciated. > > Patrick.