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

Copy views from one database server to another

Hi
I have few views on SQL server 2000, which I want to copy to sql server 2005
database. I want to schedule a job to do it every evening.
What is the best method to do this?
Thanks
ontario
ontario, canadaFirst, why do you need to copy the views every day? A view is just a
definition, and really shouldn't change on a daily basis. With that
said, the process is:
Script the view definition to a file from SQL 2000
Copy script file to new server
Execute script on SQL 2005
Another option is to use SSIS on the 2005 box and create a package to
move the objects. Search BOL for additional information on how to set
this up.
Jeff
db wrote:
> Hi
> I have few views on SQL server 2000, which I want to copy to sql server 20
05
> database. I want to schedule a job to do it every evening.
> What is the best method to do this?
> Thanks
> ontario
>
>
>
>|||Hi Jeffery
I want to copy data that is generated by view defination on sql server 2000.
When I execute the script to create table on another server I have specify
correct path (Server name, database name etc) Example to execute
CREATE VIEW viewname AS
select a,b,c
from d,e
where d.y=e.c
What would be correct syntex (With server name, database name etc)
CREATE table tablename AS
select a,b,c
from server.database.d, server.database.e
where d.y=e.c
Can I do this directly by executing a query or have to use SSIS,
import/export or replication.
ontario, canada
"Jeffrey Williams" wrote:

> First, why do you need to copy the views every day? A view is just a
> definition, and really shouldn't change on a daily basis. With that
> said, the process is:
> Script the view definition to a file from SQL 2000
> Copy script file to new server
> Execute script on SQL 2005
> Another option is to use SSIS on the 2005 box and create a package to
> move the objects. Search BOL for additional information on how to set
> this up.
> Jeff
> db wrote:
>|||access data from a remote server from within a query. Should a linked server
be a good idea.
--
ontario, canada
"db" wrote:
[vbcol=seagreen]
> Hi Jeffery
> I want to copy data that is generated by view defination on sql server 200
0.
> When I execute the script to create table on another server I have specify
> correct path (Server name, database name etc) Example to execute
> CREATE VIEW viewname AS
> select a,b,c
> from d,e
> where d.y=e.c
> What would be correct syntex (With server name, database name etc)
> CREATE table tablename AS
> select a,b,c
> from server.database.d, server.database.e
> where d.y=e.c
> Can I do this directly by executing a query or have to use SSIS,
> import/export or replication.
>
>
> --
> ontario, canada
>
> "Jeffrey Williams" wrote:
>|||Views do not contain data, they just reference them. Copying the views
won't copy the data.
"db" <db@.discussions.microsoft.com> wrote in message
news:94D1A6B0-5FE4-4C5A-B3D0-090CA9E770DC@.microsoft.com...[vbcol=seagreen]
> access data from a remote server from within a query. Should a linked
> server
> be a good idea.
> --
> ontario, canada
>
> "db" wrote:
>|||db wrote:
> access data from a remote server from within a query. Should a linked serv
er
> be a good idea.
>
I do not know what you are trying to accomplish. Are you trying to move
data from one server to another? Access data on server1 from server2?
If all you need to do is access data on a different server, then a
linked server might be the solution. Once the linked server is setup,
you can access the data using four-part naming (e.g. select <columns>
from server2.database.schema.table)
If you need to move the data to the other server, I would suggest
looking at SSIS to extract/import the data. This can also be done using
linked servers, but you have much more control using SSIS.
Jeff|||I want to move some data from one server to another server by an automated
process every evening. On server one that data reside in three tables and I
select desired information by a view defination.
The server name has a "-", like "abc-def" because of which distributed query
is giving me a error.
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> I do not know what you are trying to accomplish. Are you trying to move
> data from one server to another? Access data on server1 from server2?
> If all you need to do is access data on a different server, then a
> linked server might be the solution. Once the linked server is setup,
> you can access the data using four-part naming (e.g. select <columns>
> from server2.database.schema.table)
> If you need to move the data to the other server, I would suggest
> looking at SSIS to extract/import the data. This can also be done using
> linked servers, but you have much more control using SSIS.
> Jeff
>|||db wrote:
> I want to move some data from one server to another server by an automated
> process every evening. On server one that data reside in three tables and
I
> select desired information by a view defination.
> The server name has a "-", like "abc-def" because of which distributed que
ry
> is giving me a error.
>
Well, that is not copying views - that is moving data which can be done
through a linked server or through SSIS. I would recommend SSIS because
you have many more options.
What version of SQL are you using? If you are using SQL Server 2005 you
can create the linked server and then setup synonyms for each object you
want to access on the other system. Using synonyms you could setup the
following:
Linked Server name: abc-def
Synonym: ServerA.ObjectA
As [abc-def].remotedatabase.schema.object
And then access that object in code with:
Select <columns> From ServerA.ObjectA.
Jeff|||On source server I am using: SQL server 2000 standard edition SP4 (8.00.2039
)
On destination server I am using: SQL server standard edition 2005
(9.00.1399.06)
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>|||Source server : SQL server 2000 standard edition SP4 (8.00.2039)
Destination server: SQL server standard edition 2005 (9.00.1399.06)
--
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>

Copy views from one database server to another

Hi
I have few views on SQL server 2000, which I want to copy to sql server 2005
database. I want to schedule a job to do it every evening.
What is the best method to do this?
Thanks
ontario
--
ontario, canadaFirst, why do you need to copy the views every day? A view is just a
definition, and really shouldn't change on a daily basis. With that
said, the process is:
Script the view definition to a file from SQL 2000
Copy script file to new server
Execute script on SQL 2005
Another option is to use SSIS on the 2005 box and create a package to
move the objects. Search BOL for additional information on how to set
this up.
Jeff
db wrote:
> Hi
> I have few views on SQL server 2000, which I want to copy to sql server 2005
> database. I want to schedule a job to do it every evening.
> What is the best method to do this?
> Thanks
> ontario
>
>
>
>|||Hi Jeffery
I want to copy data that is generated by view defination on sql server 2000.
When I execute the script to create table on another server I have specify
correct path (Server name, database name etc) Example to execute
CREATE VIEW viewname AS
select a,b,c
from d,e
where d.y=e.c
What would be correct syntex (With server name, database name etc)
CREATE table tablename AS
select a,b,c
from server.database.d, server.database.e
where d.y=e.c
Can I do this directly by executing a query or have to use SSIS,
import/export or replication.
ontario, canada
"Jeffrey Williams" wrote:
> First, why do you need to copy the views every day? A view is just a
> definition, and really shouldn't change on a daily basis. With that
> said, the process is:
> Script the view definition to a file from SQL 2000
> Copy script file to new server
> Execute script on SQL 2005
> Another option is to use SSIS on the 2005 box and create a package to
> move the objects. Search BOL for additional information on how to set
> this up.
> Jeff
> db wrote:
> > Hi
> >
> > I have few views on SQL server 2000, which I want to copy to sql server 2005
> > database. I want to schedule a job to do it every evening.
> >
> > What is the best method to do this?
> >
> > Thanks
> >
> > ontario
> >
> >
> >
> >
> >
> >
> >
>|||access data from a remote server from within a query. Should a linked server
be a good idea.
--
ontario, canada
"db" wrote:
> Hi Jeffery
> I want to copy data that is generated by view defination on sql server 2000.
> When I execute the script to create table on another server I have specify
> correct path (Server name, database name etc) Example to execute
> CREATE VIEW viewname AS
> select a,b,c
> from d,e
> where d.y=e.c
> What would be correct syntex (With server name, database name etc)
> CREATE table tablename AS
> select a,b,c
> from server.database.d, server.database.e
> where d.y=e.c
> Can I do this directly by executing a query or have to use SSIS,
> import/export or replication.
>
>
> --
> ontario, canada
>
> "Jeffrey Williams" wrote:
> > First, why do you need to copy the views every day? A view is just a
> > definition, and really shouldn't change on a daily basis. With that
> > said, the process is:
> >
> > Script the view definition to a file from SQL 2000
> > Copy script file to new server
> > Execute script on SQL 2005
> >
> > Another option is to use SSIS on the 2005 box and create a package to
> > move the objects. Search BOL for additional information on how to set
> > this up.
> >
> > Jeff
> >
> > db wrote:
> > > Hi
> > >
> > > I have few views on SQL server 2000, which I want to copy to sql server 2005
> > > database. I want to schedule a job to do it every evening.
> > >
> > > What is the best method to do this?
> > >
> > > Thanks
> > >
> > > ontario
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >|||Views do not contain data, they just reference them. Copying the views
won't copy the data.
"db" <db@.discussions.microsoft.com> wrote in message
news:94D1A6B0-5FE4-4C5A-B3D0-090CA9E770DC@.microsoft.com...
> access data from a remote server from within a query. Should a linked
> server
> be a good idea.
> --
> ontario, canada
>
> "db" wrote:
>> Hi Jeffery
>> I want to copy data that is generated by view defination on sql server
>> 2000.
>> When I execute the script to create table on another server I have
>> specify
>> correct path (Server name, database name etc) Example to execute
>> CREATE VIEW viewname AS
>> select a,b,c
>> from d,e
>> where d.y=e.c
>> What would be correct syntex (With server name, database name etc)
>> CREATE table tablename AS
>> select a,b,c
>> from server.database.d, server.database.e
>> where d.y=e.c
>> Can I do this directly by executing a query or have to use SSIS,
>> import/export or replication.
>>
>>
>> --
>> ontario, canada
>>
>> "Jeffrey Williams" wrote:
>> > First, why do you need to copy the views every day? A view is just a
>> > definition, and really shouldn't change on a daily basis. With that
>> > said, the process is:
>> >
>> > Script the view definition to a file from SQL 2000
>> > Copy script file to new server
>> > Execute script on SQL 2005
>> >
>> > Another option is to use SSIS on the 2005 box and create a package to
>> > move the objects. Search BOL for additional information on how to set
>> > this up.
>> >
>> > Jeff
>> >
>> > db wrote:
>> > > Hi
>> > >
>> > > I have few views on SQL server 2000, which I want to copy to sql
>> > > server 2005
>> > > database. I want to schedule a job to do it every evening.
>> > >
>> > > What is the best method to do this?
>> > >
>> > > Thanks
>> > >
>> > > ontario
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> >|||db wrote:
> access data from a remote server from within a query. Should a linked server
> be a good idea.
>
I do not know what you are trying to accomplish. Are you trying to move
data from one server to another? Access data on server1 from server2?
If all you need to do is access data on a different server, then a
linked server might be the solution. Once the linked server is setup,
you can access the data using four-part naming (e.g. select <columns>
from server2.database.schema.table)
If you need to move the data to the other server, I would suggest
looking at SSIS to extract/import the data. This can also be done using
linked servers, but you have much more control using SSIS.
Jeff|||I want to move some data from one server to another server by an automated
process every evening. On server one that data reside in three tables and I
select desired information by a view defination.
The server name has a "-", like "abc-def" because of which distributed query
is giving me a error.
--
ontario, canada
"Jeffrey Williams" wrote:
> db wrote:
> > access data from a remote server from within a query. Should a linked server
> > be a good idea.
> >
> I do not know what you are trying to accomplish. Are you trying to move
> data from one server to another? Access data on server1 from server2?
> If all you need to do is access data on a different server, then a
> linked server might be the solution. Once the linked server is setup,
> you can access the data using four-part naming (e.g. select <columns>
> from server2.database.schema.table)
> If you need to move the data to the other server, I would suggest
> looking at SSIS to extract/import the data. This can also be done using
> linked servers, but you have much more control using SSIS.
> Jeff
>|||db wrote:
> I want to move some data from one server to another server by an automated
> process every evening. On server one that data reside in three tables and I
> select desired information by a view defination.
> The server name has a "-", like "abc-def" because of which distributed query
> is giving me a error.
>
Well, that is not copying views - that is moving data which can be done
through a linked server or through SSIS. I would recommend SSIS because
you have many more options.
What version of SQL are you using? If you are using SQL Server 2005 you
can create the linked server and then setup synonyms for each object you
want to access on the other system. Using synonyms you could setup the
following:
Linked Server name: abc-def
Synonym: ServerA.ObjectA
As [abc-def].remotedatabase.schema.object
And then access that object in code with:
Select <columns> From ServerA.ObjectA.
Jeff|||On source server I am using: SQL server 2000 standard edition SP4 (8.00.2039)
On destination server I am using: SQL server standard edition 2005
(9.00.1399.06)
--
ontario, canada
"Jeffrey Williams" wrote:
> db wrote:
> > I want to move some data from one server to another server by an automated
> > process every evening. On server one that data reside in three tables and I
> > select desired information by a view defination.
> >
> > The server name has a "-", like "abc-def" because of which distributed query
> > is giving me a error.
> >
> >
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>|||Source server : SQL server 2000 standard edition SP4 (8.00.2039)
Destination server: SQL server standard edition 2005 (9.00.1399.06)
--
ontario, canada
"Jeffrey Williams" wrote:
> db wrote:
> > I want to move some data from one server to another server by an automated
> > process every evening. On server one that data reside in three tables and I
> > select desired information by a view defination.
> >
> > The server name has a "-", like "abc-def" because of which distributed query
> > is giving me a error.
> >
> >
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>

Copy views from one database server to another

Hi
I have few views on SQL server 2000, which I want to copy to sql server 2005
database. I want to schedule a job to do it every evening.
What is the best method to do this?
Thanks
ontario
ontario, canada
First, why do you need to copy the views every day? A view is just a
definition, and really shouldn't change on a daily basis. With that
said, the process is:
Script the view definition to a file from SQL 2000
Copy script file to new server
Execute script on SQL 2005
Another option is to use SSIS on the 2005 box and create a package to
move the objects. Search BOL for additional information on how to set
this up.
Jeff
db wrote:
> Hi
> I have few views on SQL server 2000, which I want to copy to sql server 2005
> database. I want to schedule a job to do it every evening.
> What is the best method to do this?
> Thanks
> ontario
>
>
>
>
|||Hi Jeffery
I want to copy data that is generated by view defination on sql server 2000.
When I execute the script to create table on another server I have specify
correct path (Server name, database name etc) Example to execute
CREATE VIEW viewname AS
select a,b,c
from d,e
where d.y=e.c
What would be correct syntex (With server name, database name etc)
CREATE table tablename AS
select a,b,c
from server.database.d, server.database.e
where d.y=e.c
Can I do this directly by executing a query or have to use SSIS,
import/export or replication.
ontario, canada
"Jeffrey Williams" wrote:

> First, why do you need to copy the views every day? A view is just a
> definition, and really shouldn't change on a daily basis. With that
> said, the process is:
> Script the view definition to a file from SQL 2000
> Copy script file to new server
> Execute script on SQL 2005
> Another option is to use SSIS on the 2005 box and create a package to
> move the objects. Search BOL for additional information on how to set
> this up.
> Jeff
> db wrote:
>
|||access data from a remote server from within a query. Should a linked server
be a good idea.
ontario, canada
"db" wrote:
[vbcol=seagreen]
> Hi Jeffery
> I want to copy data that is generated by view defination on sql server 2000.
> When I execute the script to create table on another server I have specify
> correct path (Server name, database name etc) Example to execute
> CREATE VIEW viewname AS
> select a,b,c
> from d,e
> where d.y=e.c
> What would be correct syntex (With server name, database name etc)
> CREATE table tablename AS
> select a,b,c
> from server.database.d, server.database.e
> where d.y=e.c
> Can I do this directly by executing a query or have to use SSIS,
> import/export or replication.
>
>
> --
> ontario, canada
>
> "Jeffrey Williams" wrote:
|||Views do not contain data, they just reference them. Copying the views
won't copy the data.
"db" <db@.discussions.microsoft.com> wrote in message
news:94D1A6B0-5FE4-4C5A-B3D0-090CA9E770DC@.microsoft.com...[vbcol=seagreen]
> access data from a remote server from within a query. Should a linked
> server
> be a good idea.
> --
> ontario, canada
>
> "db" wrote:
|||db wrote:
> access data from a remote server from within a query. Should a linked server
> be a good idea.
>
I do not know what you are trying to accomplish. Are you trying to move
data from one server to another? Access data on server1 from server2?
If all you need to do is access data on a different server, then a
linked server might be the solution. Once the linked server is setup,
you can access the data using four-part naming (e.g. select <columns>
from server2.database.schema.table)
If you need to move the data to the other server, I would suggest
looking at SSIS to extract/import the data. This can also be done using
linked servers, but you have much more control using SSIS.
Jeff
|||I want to move some data from one server to another server by an automated
process every evening. On server one that data reside in three tables and I
select desired information by a view defination.
The server name has a "-", like "abc-def" because of which distributed query
is giving me a error.
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> I do not know what you are trying to accomplish. Are you trying to move
> data from one server to another? Access data on server1 from server2?
> If all you need to do is access data on a different server, then a
> linked server might be the solution. Once the linked server is setup,
> you can access the data using four-part naming (e.g. select <columns>
> from server2.database.schema.table)
> If you need to move the data to the other server, I would suggest
> looking at SSIS to extract/import the data. This can also be done using
> linked servers, but you have much more control using SSIS.
> Jeff
>
|||db wrote:
> I want to move some data from one server to another server by an automated
> process every evening. On server one that data reside in three tables and I
> select desired information by a view defination.
> The server name has a "-", like "abc-def" because of which distributed query
> is giving me a error.
>
Well, that is not copying views - that is moving data which can be done
through a linked server or through SSIS. I would recommend SSIS because
you have many more options.
What version of SQL are you using? If you are using SQL Server 2005 you
can create the linked server and then setup synonyms for each object you
want to access on the other system. Using synonyms you could setup the
following:
Linked Server name: abc-def
Synonym: ServerA.ObjectA
As [abc-def].remotedatabase.schema.object
And then access that object in code with:
Select <columns> From ServerA.ObjectA.
Jeff
|||On source server I am using: SQL server 2000 standard edition SP4 (8.00.2039)
On destination server I am using: SQL server standard edition 2005
(9.00.1399.06)
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>
|||Source server : SQL server 2000 standard edition SP4 (8.00.2039)
Destination server: SQL server standard edition 2005 (9.00.1399.06)
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>