Re: Creating a copy of a database -- best practice? - checking email - did not come through before
Below is a function to backup a database from a source folder to a
target folder, including all triggers, stored procedures, default field
values etc. I do not think there was anything wrong with the original
"table" copying code that Mike had, but I did not have that email handy
when I did this. So just be advised if there was something special in
that original code, my code below may not handle it, etc. This code
should work even if the source database is open as well as tables in
that database being open by another application. Of course, the DB and
tables are expected to be open in SHARED mode (aka, non-EXCLUSIVE). Also
note that if this code is placed directly in an application, it will
close all databases/tables. So if the application needs to have this
function, some "environmental saving/restore" code would need to be
added (pretty simple to do with the AUSED() function, etc).
This uses my "Option 3" method from the earlier email. Essentially,
after all the base data is copied, a direct "file copy" on the DBC data
is performed. Technically, COPY FILE ... could probably be used for all
files of the DB, but by using the table "COPY" (not COPY FILE) it
drastically reduces any potential for 'corruption' due to multi-user
actions, etc. And, using COPY FILE only on the "database container"
files is quite reasonably safe since DATABASE LEVEL modifications are
generally not a "user" operation and so could be avoided during the
backup process.
It goes without saying, before using in production, this code should be
thoroughly tested, customized, etc.
HTH,
-Charlie
PS. Please don't make fun of my variable names - I was in a hurry when I
gen'd this up (hahahaha)
*--
FUNCTION db_backup
*-- purpose: create a backup copy of a VFP database, including all
*-- stored procedures, triggers, etc
*-- Parameters:
*-- from_db: full path qualified (x:\sample\one\dbname)
*-- to_db: full path qualified
*--
*-- Assupmtions:
*-- - this code is mainly 'proof of concept' code, so several
*-- sections should be checked before production use
*-- - the target db (to_db) is not OPEN by any application
PARAMETERS from_db, to_db
LOCAL target_dir, ntbls, ctbl, cdbname, from_dir
*-- should "harden" this code before production - e.g. ensure source
*-- db exists, decide and code what should be done if destination db
*-- already exists (only want one db in the backup folder, etc)
*-- Warning! Exclusive being off is pretty much required for this
*-- code. If your other code needs Exclusive ON, change code to
*-- turn it back on in the end of this function.
SET EXCLUSIVE OFF
*-- Warning! the following block deletes any VFP database files in
*-- the target folder. So this needs to be changed if that folder
*-- is being used to backup other DBs, free tables, etc.
target_dir = ADDBS(JUSTPATH(to_db))
DELETE FILE (target_dir + "*.db?")
DELETE FILE (target_dir + "*.dct")
DELETE FILE (target_dir + "*.dcx")
DELETE FILE (target_dir + "*.cdx")
DELETE FILE (target_dir + "*.fpt")
*-- Create the target database and copy all the base table data
CREATE DATABASE (to_db)
OPEN DATABASE (from_db)
ntbls = ADBOBJECTS(_atbls, 'TABLE')
FOR ni = 1 TO ntbls
ctbl = ALLTRIM(_atbls[ni])
*-- if the table being copied is opened elsewhere in the app
*-- need to use it 'again'
IF USED(ctbl)
USE (ctbl) AGAIN IN 0 ALIAS TMPCOPY
ELSE
USE (ctbl) IN 0 ALIAS TMPCOPY
ENDIF
SELECT TMPCOPY
COPY TO (target_dir + ctbl) DATABASE (to_db) WITH cdx
USE IN ("TMPCOPY")
ENDFOR
*-- now, to get all stored procs, etc, need to copy the dbc. Test
*-- this code thoroughly
CLOSE DATABASES all
*-- this assumes the backup db should have the same name as the
*-- source db. If there is a desire to rename the backup db,
*-- this code should be changed to grab the target name from
*-- the to_db parameter
cdbname = JUSTSTEM(from_db)
from_dir = ADDBS(JUSTPATH(from_db))
*-- NOTE: turning SAFETY off to avoid pop-up warning
SET SAFETY OFF
COPY FILE (from_dir + cdbname + ".dbc") to ;
(target_dir + cdbname + ".dbc")
COPY FILE (from_dir + cdbname + ".dcx") to ;
(target_dir + cdbname + ".dcx")
COPY FILE (from_dir + cdbname + ".dct") to ;
(target_dir + cdbname + ".dct")
SET SAFETY ON
*-- this might not be necessary, but including it for
*-- conceptual consideration
COMPILE DATABASE (to_db)
OPEN DATABASE (to_db) EXCLUSIVE
VALIDATE DATABASE
CLOSE DATABASE
RETURN
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/fa585f8a-89d2-13a4-f3a8-718d3a0e4...@gmail.com
** All postings, unless explicitly stated otherwise, are
Re: Creating a copy of a database -- best practice? - checking email - did not come through before
On 2019-03-02 08:14, Charlie-gm wrote:
Ok, I haven't done this in a while, but I think I have done this a few
ways.
First, by "metadata" of the database, I assume you mean stored
procedures, maybe table triggers, database comment field?
I'll also assume you are trying to run from within an .exe (so
commands like COPY PROCEDURES TO... and APPEND PROCEDURES FROM ...
will not be available)
So...
1) if you know the "data model" of the dbc, you can open it like a
table - then look for the metadata items you want (the stored
procedure code, trigger code, etc). Do a SCATTER MEMO NAME ... Then
open the other DBC as a table and do an APPEND FROM NAME ... After
everything is copied that way I think you will want to do a PACK
DATABASE or maybe VALIDATE DATABASE on the backup. I seem to recall
doing that.
2) after you've copied all the tables, copy the "database files" with
"COPY FILE TO " - copy the dbc, dct, and dcx this way.
Again a PACK or VALIDATE database may be needed afterwards. And of
course, be careful with this, test it out, etc. But I definitely used
this approach before.
3) if you are not worried about the tables being opened at the time of
doing the backup, you could just do the "COPY FILE TO "
- that command allows paths in the from/to. Also, I think it allows
wildcards, so you could do a complete copy in 1 command. Of course,
the downside is the assumption of files being closed.
Hi Charlie,
I've got VFP available so COPY/APPEND PROCEDURES is available. So I
wrote a quick PRG and got the stored procedures just fine with
that...but the part that's missing now is the table-specific data like
default values (important as some call the usual newid('table') function
in the default PK field) and captions (not important).
--Mike
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/e6efbd4226dee2145628baff4f262...@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Creating a copy of a database -- best practice? - checking email - did not come through before
Ok, I haven't done this in a while, but I think I have done this a few
ways.
First, by "metadata" of the database, I assume you mean stored
procedures, maybe table triggers, database comment field?
I'll also assume you are trying to run from within an .exe (so commands
like COPY PROCEDURES TO... and APPEND PROCEDURES FROM ... will not be
available)
So...
1) if you know the "data model" of the dbc, you can open it like a table
- then look for the metadata items you want (the stored procedure code,
trigger code, etc). Do a SCATTER MEMO NAME ... Then open the other DBC
as a table and do an APPEND FROM NAME ... After everything is copied
that way I think you will want to do a PACK DATABASE or maybe VALIDATE
DATABASE on the backup. I seem to recall doing that.
2) after you've copied all the tables, copy the "database files" with
"COPY FILE TO " - copy the dbc, dct, and dcx this way.
Again a PACK or VALIDATE database may be needed afterwards. And of
course, be careful with this, test it out, etc. But I definitely used
this approach before.
3) if you are not worried about the tables being opened at the time of
doing the backup, you could just do the "COPY FILE TO " -
that command allows paths in the from/to. Also, I think it allows
wildcards, so you could do a complete copy in 1 command. Of course, the
downside is the assumption of files being closed.
-HTH,
-Charlie
-
On 2/27/2019 12:22 PM, mbsoftwaresoluti...@mbsoftwaresolutions.com wrote:
I can easily do something like this:
CREATE DATABASE C:\Backup\MyDBC.dbc
OPEN DATABASE C:\Production\MyDBC.dbc
liNumTables = adbobjects(laTables,'TABLE')
for ii = 1 to liNumTables
lcFile = forceext("C:\BACKUP\" + laTables[ii],'dbf')
use laTables[ii]
copy to (lcFile) database C:\Backup\MyDBC.dbc with cdx
use
endfor
...and that would get me a copy of all of the tables with their indexes.
Great. But what's the easiest way to get all of the DBC meta-data
into that new Backup database copy? I can't USE the MyDBC.dbc and do a
COPY TO as that only makes the result a DBF and FPT.
Trying to think about a good disaster recovery plan (besides using
CleverFox from Rick Schummer and Frank Perez...which is probably the
best option!) for automating backups at the client who just got that
ransomware virus.
tia,
--Mike
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/9309004b-cd50-9d5a-70b8-ef16fd0b5...@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
