Copying detached databases over the network with T-SQL

I have a T-SQL script which deataches and attaches the database. Now here is what I want to do:
I

want to create a SQL Server Job and schedule it to run at a particular

time of the month to Detach all the databases on my local machine and

*Copy all the deatched databases over a network machine* and then

Attach those Database.

All this should be done in T-SQL. I

already have the T-SQLs for Attaching and Detaching. All I need is to

know the T-SQL (example) which will copy the *Detached Databases* from

mu local computer to my Network Computer. How do I achieve that?

Thanks

xp_cmdshell

Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell.

xp_cmdshell 'copy c:\x.mdb y:\x.mdb'

where y: is a mapped network drive

|||You can invoke DOS command through sqlserver (e.g. "copy <source> <\\target>"). You want to take a look at xp_cmdshell in book online for details.|||

aside from using the sql jobs you can use

the "windows scheduler" to

1. detach the database from the source server using sqlcmd

2. use dos command to copy the database

3. use sqlcmd to attach the db to the destination server

I recommend this method over the other

cheers

|||Thank you guys for you replies. I think I may take the path of xp_cmdshell.

joeydj your suggestion looks valid, but I have a question. What is the full command(an example) to detach the database using sqlcmd? Also do I need to run this command on 'Command Prompt'.

Thanks
|||You can use sp_detach_db to detach a database. See BOL for syntax and examples. It is easier doing these type of operations outside the database. Note that in SQL Server 2005 xp_cmdshell is disabled my default on most SKUs & enabling it increases the security risk on the server. So don't use it unless you absolutely need to. Writing a batch file to do these operations is very trivial.|||

for sql2k you can use OSQL the SQLCMD 2005 counterpart

and here's the syntax

c:\ osql -USa -P -S(local)\sql2k -Q"sp_detach_db demodb" -X

where

-Usa --user Sa

-P -- password in my case blank

-S(local)\sql2k --is the server

-Q"sp_detach_db demodb" -- this is the query to detach and watchout for the quotes

-X exit OSQL

sqlcmd has the same syntax except that it handles blank password differently

c:\ SQLCMD -USa -Pmypaswd -S(local)\sql2k -Q"sp_detach_db demodb" -X

you cann use the -E switch for trusted connection

for more help type sqlcmd/? or osql/? on your command prompt

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