copying a table and all of its objects

Is there any way, using t-sql, to copy a table, all its indices, contraints,
etc? The contraints and indices would have to have a different name, of
course, if they remain in the same database, as would the table.
What I'm trying to do is replicate a table in every way, except foreign
keys, so that I can restore it if needed. If my customer runs a routine in
his windows app that appends rows to an important table, but realizes that
he shouldn't have (for whatever reason), I want to create a backup before he
runs his routine and provide a means of restoring that backup if he realizes
his error, if something goes wrong in the middle (electrical problems, etc).
So I want to make my exact copy first and then be able to restore it
subsequently.
I know how to do this with some tedious and complicated sp's, but I was
hoping there'd be an easier way.
Thanks for any help.
Bernie YaegerBernie Yaeger (berniey@.cherwellinc.com) writes:
> Is there any way, using t-sql, to copy a table, all its indices,
> contraints, etc? The contraints and indices would have to have a
> different name, of course, if they remain in the same database, as would
> the table.
> What I'm trying to do is replicate a table in every way, except foreign
> keys, so that I can restore it if needed. If my customer runs a routine
> in his windows app that appends rows to an important table, but realizes
> that he shouldn't have (for whatever reason), I want to create a backup
> before he runs his routine and provide a means of restoring that backup
> if he realizes his error, if something goes wrong in the middle
> (electrical problems, etc). So I want to make my exact copy first and
> then be able to restore it subsequently.
> I know how to do this with some tedious and complicated sp's, but I was
> hoping there'd be an easier way.
Rather than answering your question, I think we should look at alternate
ways to handle this situation.
First, with proper transaction handling, electric outages should not be
a problem. If the routine is interrupted, nothing should be committed
until all is over. That is, once you bring the machine up again, SQL
Server till automatically rollback the transaction for you.
Next, assuming that once the routine is completed, someone realizes
that he messed up, and wants to restore, SQL Server offers a solution
for this, and therre is a third-party solution. Both of them requires
that you run with the full or bulk-logged recovery model.
SQL Server offer point-in-time restores. That is, you restore a full
backup, and then apply the transaction log to the point in time just
before the mistaken operation. As you may guess this also wipes out
any other activity that took place simultaneously or after the user
mistake.
The 3rd party solution is to rely on Lumigent Log Explorer
(www.lumigent.com). With Log Explorer you can examine the transaction
log for what happened, and you can also have Log Explorer to generate
SQL statements that revokes the operations that should not have occurred.
Of course, if you expect this to be a very common scenario that you
will need to restore data, then you need to develop something application-
specific. But in such case I would have some flag columns that tells
me whether a inserted row is approved. Updated and deleted rows I would
copy to a shadow table, or just set a status bit for "delete_pending" on.
Such a bit would have to be part of the primary key, to handle updates.
Note that your intented solution of restoring the entire table, has the
same problem as point-in-time resotres: you lose all activity in the
table.
Yet one solution is to put the table on a filegroup on its own. Then you
can restore that filegroup only, but I'm lukewarn for that solution.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hi Erland,
Tx for your reply.
Lots of what you say is of course quite relevant. I will look into several
of your suggetions.
Bernie
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9466ADEF7E64DYazorman@.127.0.0.1...
> Bernie Yaeger (berniey@.cherwellinc.com) writes:
> > Is there any way, using t-sql, to copy a table, all its indices,
> > contraints, etc? The contraints and indices would have to have a
> > different name, of course, if they remain in the same database, as would
> > the table.
> >
> > What I'm trying to do is replicate a table in every way, except foreign
> > keys, so that I can restore it if needed. If my customer runs a routine
> > in his windows app that appends rows to an important table, but realizes
> > that he shouldn't have (for whatever reason), I want to create a backup
> > before he runs his routine and provide a means of restoring that backup
> > if he realizes his error, if something goes wrong in the middle
> > (electrical problems, etc). So I want to make my exact copy first and
> > then be able to restore it subsequently.
> >
> > I know how to do this with some tedious and complicated sp's, but I was
> > hoping there'd be an easier way.
> Rather than answering your question, I think we should look at alternate
> ways to handle this situation.
> First, with proper transaction handling, electric outages should not be
> a problem. If the routine is interrupted, nothing should be committed
> until all is over. That is, once you bring the machine up again, SQL
> Server till automatically rollback the transaction for you.
> Next, assuming that once the routine is completed, someone realizes
> that he messed up, and wants to restore, SQL Server offers a solution
> for this, and therre is a third-party solution. Both of them requires
> that you run with the full or bulk-logged recovery model.
> SQL Server offer point-in-time restores. That is, you restore a full
> backup, and then apply the transaction log to the point in time just
> before the mistaken operation. As you may guess this also wipes out
> any other activity that took place simultaneously or after the user
> mistake.
> The 3rd party solution is to rely on Lumigent Log Explorer
> (www.lumigent.com). With Log Explorer you can examine the transaction
> log for what happened, and you can also have Log Explorer to generate
> SQL statements that revokes the operations that should not have occurred.
> Of course, if you expect this to be a very common scenario that you
> will need to restore data, then you need to develop something application-
> specific. But in such case I would have some flag columns that tells
> me whether a inserted row is approved. Updated and deleted rows I would
> copy to a shadow table, or just set a status bit for "delete_pending" on.
> Such a bit would have to be part of the primary key, to handle updates.
> Note that your intented solution of restoring the entire table, has the
> same problem as point-in-time resotres: you lose all activity in the
> table.
> Yet one solution is to put the table on a filegroup on its own. Then you
> can restore that filegroup only, but I'm lukewarn for that solution.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp