Copying Tables Across Servers

I'm trying to copy data from a remote server connected thru enterprise
manager. I' assuming there shld be a means to script the table structure an
d
the contents. Is this possible and how can I go about it.
--
Beaversuse import/export wizard
"Beavers" wrote:

> I'm trying to copy data from a remote server connected thru enterprise
> manager. I' assuming there shld be a means to script the table structure
and
> the contents. Is this possible and how can I go about it.
> --
> Beavers
>|||I had problems with that because of the server names. Nevertheless I have
managed to copy with the age old copy and paste function.
Thanks!
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> use import/export wizard
>
> "Beavers" wrote:
>

Copying Tables Across Servers

I'm trying to copy data from a remote server connected thru enterprise
manager. I' assuming there shld be a means to script the table structure and
the contents. Is this possible and how can I go about it.
Beavers
use import/export wizard
"Beavers" wrote:

> I'm trying to copy data from a remote server connected thru enterprise
> manager. I' assuming there shld be a means to script the table structure and
> the contents. Is this possible and how can I go about it.
> --
> Beavers
>
|||I had problems with that because of the server names. Nevertheless I have
managed to copy with the age old copy and paste function.
Thanks!
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> use import/export wizard
>
> "Beavers" wrote:

Copying Permissions on SPs and Views in Merge Rep

Hi,
I've come across another issue with my merge replication on SQL 2005 SP2.
Every time it recreates the SPs or views (due to snapshot, or changes) it
drops all of the custom persmissions to roles, or users.
I found a "Copy permission" on the tables, but can't find it for views or
procs. Is there a way to automatically have this happen on the SPs and views?
If not, can someone point me in the way of a viable workaround? (such as a
script/CLR to run based on triggers, schedules, whatever)
Ryan S
Sr SQL DBA
1Jn5:12
I script the permissions out for the views and procs and then use a post
snapshot command to apply them.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
> Hi,
> I've come across another issue with my merge replication on SQL 2005 SP2.
> Every time it recreates the SPs or views (due to snapshot, or changes) it
> drops all of the custom persmissions to roles, or users.
> I found a "Copy permission" on the tables, but can't find it for views or
> procs. Is there a way to automatically have this happen on the SPs and
> views?
> If not, can someone point me in the way of a viable workaround? (such as a
> script/CLR to run based on triggers, schedules, whatever)
> --
> Ryan S
> Sr SQL DBA
> 1Jn5:12
|||BTW, here is something I came up with yesterday to do this somewhat
dynamically for the entire server...
if object_id('Tempdb.dbo.##permissions') != 0
Drop table ##permissions
Create Table ##Permissions (Own varchar(270), Ob varchar(270), Grantee
varchar(270), Grantor varchar(270), ProtectType varchar(270), Act
varchar(270), Col varchar(270), DB Varchar(1000))
exec sp_msForeachdb '
use [?]
if ''?'' not in (''master'', ''msdb'', ''tempdb'', ''Model'')
Begin
Declare @.DB nvarchar(1000)
, @.Cmd nvarchar(4000)
set @.DB = ''?''
print ''?''
Insert Into ##Permissions (Own , Ob , Grantee , Grantor , ProtectType ,
Act , Col)
exec sp_helprotect
update ##Permissions set DB = @.DB
where DB is null
Delete from ##Permissions
-- remove permissions for system objects
where ob in (Select sysobjects.Name COLLATE SQL_Latin1_General_CP1_CI_AS
From sysobjects
where OBJECTPROPERTY (sysobjects.id, ''IsSystemTable'') = 1)
End
'
Select 'Use [' + DB + '] ;
if Object_id(''' + Ob + ''') is not null
' + rtrim(ltrim(ProtectType)) + ' ' + rtrim(ltrim(Act)) + ' on [' +
rtrim(ltrim(Ob)) + '] to [' + rtrim(ltrim(Grantee)) + ']'
from ##Permissions p
where ob != '.'
and grantee != 'public' --Do not copy public permissions
and left(grantee, 2) != 'MS' --Remove replication object permissions
drop table ##permissions
Ryan S
Sr SQL DBA
1Jn5:12
"Hilary Cotter" wrote:

> I script the permissions out for the views and procs and then use a post
> snapshot command to apply them.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
> news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
>
>

Copying DTS Packages across Servers

HI All,

Now my brain isn't quite where it should be right about now - so hopefully you will be able to put me at rest.

I have been building a new server with SQL2000 on it. Some of the stuff on our existing server is going to be migrated across - however I for the life of me cannot make any sense of how to move stuff (like DTS packages) from one server to another.

moving the Database's - no problems, but all the other guff in the tree (in Enterprise Manager) I cant seem to get.

Any assistance here is greatly appreciated.

Cheers
TroyHi Troy,

You can save the packages as a structure file and save them to disk and then transfer them to your new server...

Or,.. you could get cunning and write a quick app to do it. I have some asp pages that I have written that will do this and I can flick them your way if you want... they will probably need tweaking to suit your needs...

What's the weather like in Auckland today??|||Hey thanks for that.
I will go and have a play and see what I can do - I assume it is a pretty straight forward process dumping them to file? And then reimporting them?

As for the ASP page - ummm errr, would love to take you up on that offer, but sadly I am about as literate with that sort of stuff as Cinderalla with a grenade laucher. (unless it is blatantly obvious). What are your thoughts on this?

Hmmm as for the weather - well all in all okay, and it is meant to be good through the weekend. Which will make a nice change because our new pool should have a chance to dry up and return to what was our lawn :(
Hopefully better in Aust.|||It's a relatively simple asp page,... the key thing is the SQL that we use. You could probably write it up as another stored proc or something to carry out the same function... ASP is a piece of cake to understand assuming some general knowledge of scripting languages...

Have a think about it and let me know.

Weather in Sydney today is overcast and cool,... not raining yet though... I think my cousin who is visiting from Chch brought this cloud with him...

Copying data between database tables

Hi. I need to move data from one database table to

another across database instances. A simple example of the typical

move would be:

[CODE]

INSERT into destination_db.dbo.table1

SELECT column1, column2, column3, column4 from source_db.dbo.table2

[/CODE]

My options are:

1. Create an SSIS package to perform the move.

2. Create sprocs and schedule the data move as jobs.

3. Write .NET code using sprocs to perform the move.

I'll have to move hundreds of thousands of records, so I want the

option that provides the best performance. I'm guessing that option 3

will be the slowest.

Thanks for the help!
To tell you the truth, you could just try it and find out which runs faster. We are probably talking seconds here at the 100,000 records level. One thing you might want to look into is partitions. If the table you are pulling to and from are identical then the switch option of the partion method with tables makes this happen in a second regardless of the number of records. It is just swapping data pages.|||

BSHOE wrote:

To tell you the truth, you could just try it and find out which runs faster. We are probably talking seconds here at the 100,000 records level. One thing you might want to look into is partitions. If the table you are pulling to and from are identical then the switch option of the partion method with tables makes this happen in a second regardless of the number of records. It is just swapping data pages.

BSHOE,

That won't work if the tables are on different instances. Which in this case they are.

To the original poster - if pure performance is your consideration then SSIS is the way to go.

-Jamie

|||

Jamie Thomson wrote:

BSHOE wrote:

To tell you the truth, you could just try it and find out which runs faster. We are probably talking seconds here at the 100,000 records level. One thing you might want to look into is partitions. If the table you are pulling to and from are identical then the switch option of the partion method with tables makes this happen in a second regardless of the number of records. It is just swapping data pages.

BSHOE,

That won't work if the tables are on different instances. Which in this case they are.

To the original poster - if pure performance is your consideration then SSIS is the way to go.

-Jamie

I concur with Jamie. Write a simple SSIS job to extract and then load the data. I can pull 100,000 records a second from SQL Server 2005 on my laptop into a flat file. Look on my website for details on how I do this and just substitute a table for the flat file in my demo.

Copying data across rows

I have one table where I am trying to copy a number from one field
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?
Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:

> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?
|||> Hope that following can help you:
Thanks for the reply!!

> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?
|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+

Copying data across rows

I have one table where I am trying to copy a number from one field
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:

> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?|||> Hope that following can help you:
Thanks for the reply!!

> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+

Copying data across rows

I have one table where I am trying to copy a number from one field
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:
> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?|||> Hope that following can help you:
Thanks for the reply!!
> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+

copying data across databases

I have a basic question:
We have a database db1 on SQLServer 2005 that gets updated v frequently.
We would to like use a different database db2 (on a different SQL Server
instance) that is a snapshot of db1. db2 is a read-only database that would
be used for reporting purposes. On a daily basis, the new data from db1 must
get copied to db2.
Is there a SQLServer Tool that would help? I tried using Copy data from SQL
Server management Studio; it copied most of the data (not all), but did not
create the indexes that existed for the tables in db1.
Option 1: DTS Package (SSIS in 2005) to update the tables
Option 2: Replication
Thanks & Rate the Postings.
-Ravi-
"Jason Manfield" wrote:

> I have a basic question:
> We have a database db1 on SQLServer 2005 that gets updated v frequently.
> We would to like use a different database db2 (on a different SQL Server
> instance) that is a snapshot of db1. db2 is a read-only database that would
> be used for reporting purposes. On a daily basis, the new data from db1 must
> get copied to db2.
> Is there a SQLServer Tool that would help? I tried using Copy data from SQL
> Server management Studio; it copied most of the data (not all), but did not
> create the indexes that existed for the tables in db1.
|||option 3 = LogShipping
Greg Jackson
PDX, Oregon
|||There is a slight change is our requirements:
Data does NOT have to copied on an incremental basis. A full copy needs to
be done from the database db1 that is periodically updated to the database
db2 that is a read-only database. In that case, what are the options
available on SqlServer 2005?
"pdxJaxon" wrote:

> option 3 = LogShipping
>
> Greg Jackson
> PDX, Oregon
>
>

copying data across databases

I have a basic question:
We have a database db1 on SQLServer 2005 that gets updated v frequently.
We would to like use a different database db2 (on a different SQL Server
instance) that is a snapshot of db1. db2 is a read-only database that would
be used for reporting purposes. On a daily basis, the new data from db1 must
get copied to db2.
Is there a SQLServer Tool that would help? I tried using Copy data from SQL
Server management Studio; it copied most of the data (not all), but did not
create the indexes that existed for the tables in db1.Option 1: DTS Package (SSIS in 2005) to update the tables
Option 2: Replication
--
Thanks & Rate the Postings.
-Ravi-
"Jason Manfield" wrote:
> I have a basic question:
> We have a database db1 on SQLServer 2005 that gets updated v frequently.
> We would to like use a different database db2 (on a different SQL Server
> instance) that is a snapshot of db1. db2 is a read-only database that would
> be used for reporting purposes. On a daily basis, the new data from db1 must
> get copied to db2.
> Is there a SQLServer Tool that would help? I tried using Copy data from SQL
> Server management Studio; it copied most of the data (not all), but did not
> create the indexes that existed for the tables in db1.|||option 3 = LogShipping
Greg Jackson
PDX, Oregon|||There is a slight change is our requirements:
Data does NOT have to copied on an incremental basis. A full copy needs to
be done from the database db1 that is periodically updated to the database
db2 that is a read-only database. In that case, what are the options
available on SqlServer 2005?
"pdxJaxon" wrote:
> option 3 = LogShipping
>
> Greg Jackson
> PDX, Oregon
>
>

copying data across databases

I have a basic question:
We have a database db1 on SQLServer 2005 that gets updated v frequently.
We would to like use a different database DB2 (on a different SQL Server
instance) that is a snapshot of db1. DB2 is a read-only database that would
be used for reporting purposes. On a daily basis, the new data from db1 must
get copied to db2.
Is there a SQLServer Tool that would help? I tried using Copy data from SQL
Server management Studio; it copied most of the data (not all), but did not
create the indexes that existed for the tables in db1.Option 1: DTS Package (SSIS in 2005) to update the tables
Option 2: Replication
Thanks & Rate the Postings.
-Ravi-
"Jason Manfield" wrote:

> I have a basic question:
> We have a database db1 on SQLServer 2005 that gets updated v frequently.
> We would to like use a different database DB2 (on a different SQL Server
> instance) that is a snapshot of db1. DB2 is a read-only database that woul
d
> be used for reporting purposes. On a daily basis, the new data from db1 mu
st
> get copied to db2.
> Is there a SQLServer Tool that would help? I tried using Copy data from SQ
L
> Server management Studio; it copied most of the data (not all), but did no
t
> create the indexes that existed for the tables in db1.|||option 3 = LogShipping
Greg Jackson
PDX, Oregon|||There is a slight change is our requirements:
Data does NOT have to copied on an incremental basis. A full copy needs to
be done from the database db1 that is periodically updated to the database
db2 that is a read-only database. In that case, what are the options
available on SqlServer 2005?
"pdxJaxon" wrote:

> option 3 = LogShipping
>
> Greg Jackson
> PDX, Oregon
>
>

Copying a Log Shipped database

I have a production server in a distant city across our WAN. One warm
standy by is there in that city, and a second warm standy by is here with me
at Corp HQ.
I need a copy of our data, but I don't want to have to bring a copy of the
Production sever down over the WAN.. that'll take too long (~4gbs). I would
like to take a copy of the local warm standby databases that are read-only.
How can I get a copy of the read-only databases for uses locally?Eric
read topic in BOL about standby servers.
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> I have a production server in a distant city across our WAN. One warm
> standy by is there in that city, and a second warm standy by is here with
me
> at Corp HQ.
> I need a copy of our data, but I don't want to have to bring a copy of the
> Production sever down over the WAN.. that'll take too long (~4gbs). I
would
> like to take a copy of the local warm standby databases that are
read-only.
> How can I get a copy of the read-only databases for uses locally?
>|||I've been all over BOL... this is why I'm asking! Because I'm unsure how
to do it.
I do not want to do a role change or anything.. Nor do I want to bring one
of my StandBy Servers online. I simply get a copy of all our data from my
local Destination servers/Standy server. I need to do this ASAP and would
appreciate some help rather than the standard "look in BOL".
If you know, please tell me a little.. I've done Google searchs on this
topic and looked in BOL, but its tough to narrow down apparently when you
don't know the answer. I've been trying to solve this on and off for a
couple weeks now.. Please help if you can. Sending me to BOL isn't any
help.
Eric
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> Eric
> read topic in BOL about standby servers.
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
with
> me
the
> would
> read-only.
>|||Eric
I really want to help you.( don't angry)
Unless I missunderstand you can try to move your backup as zip or rar files
over a network. (4 gb is not so big)
Backup your database then create batch file like that
CD \Program Files\WinRAR
WINRAR a D:\databasename%1 D:\database%1.BAK
After running this file you will get rar/zip file you can easily transfer
over a network.
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I've been all over BOL... this is why I'm asking! Because I'm unsure how
> to do it.
> I do not want to do a role change or anything.. Nor do I want to bring one
> of my StandBy Servers online. I simply get a copy of all our data from my
> local Destination servers/Standy server. I need to do this ASAP and would
> appreciate some help rather than the standard "look in BOL".
> If you know, please tell me a little.. I've done Google searchs on this
> topic and looked in BOL, but its tough to narrow down apparently when you
> don't know the answer. I've been trying to solve this on and off for a
> couple weeks now.. Please help if you can. Sending me to BOL isn't any
> help.
> Eric
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> with
> the
>|||No.. reread my Question. I'm well aware that you can backup up a database
and copy that from one place to another.
I do NOT want to backup the database and then copy over our LAN. That will
take many hours. I don't care if 4gb is considered a big database or not.
It's simply a big file to throw across the WAN. Uncompressed it will take 8
hours or something.. compressed it takes 5 hours.
I DO want to use my local warm standby copy. I can't back the database up
because it's in Warm standby mode. So.. can I use this local copy to
move/copy over to a another useable (such as in a development environment)
database. That is my question.
Eric Smith
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> Eric
> I really want to help you.( don't angry)
> Unless I missunderstand you can try to move your backup as zip or rar
files
> over a network. (4 gb is not so big)
> Backup your database then create batch file like that
> CD \Program Files\WinRAR
> WINRAR a D:\databasename%1 D:\database%1.BAK
> After running this file you will get rar/zip file you can easily transfer
> over a network.
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
how
one
my
would
you
warm
of
I
>|||Eric
I am not following you.
What do you mean by Warm standby mode?
Have you looked at "How to set up, maintain, and bring online a standby
server (Transact-SQL)"
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
> No.. reread my Question. I'm well aware that you can backup up a database
> and copy that from one place to another.
> I do NOT want to backup the database and then copy over our LAN. That
will
> take many hours. I don't care if 4gb is considered a big database or not.
> It's simply a big file to throw across the WAN. Uncompressed it will take
8
> hours or something.. compressed it takes 5 hours.
> I DO want to use my local warm standby copy. I can't back the database up
> because it's in Warm standby mode. So.. can I use this local copy to
> move/copy over to a another useable (such as in a development environment)
> database. That is my question.
> Eric Smith
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> files
transfer
> how
> one
from
> my
> would
this
> you
any
> warm
here
copy
> of
(~4gbs).
> I
>|||Uri,
I think it is you that needs to go back and learn a bit more about Log
Shipping.
A destination server in a log shipping scheme is referred to as a "warm
standby". A hot server would be considered live... A cold standby would be
a box that is available and usable but would need the most recent backup
restored to be ready to use.
A warm standby refers to the fact that it's warm and ready to go.. it's not
live, but its not dormant and cold. Its warm, such as in a Log Shipping
scheme. It has all the data and is ready to take over as primary at most
any time.
If you go and try and back up a destination log shipped databsase (a
warm-standby database) you get the message:
"Database XXX is in warm-standby state (set by executing RESTORE WITIH
STANDBY) and cannot be backed up until the entire load sequence is
completed. BACKUP DATABASE is terminating abnormallhy" Hence my original
question of how do I get a backup of a warm-standby database so I can move
it to another server and play with it, such as in a development mode.
No offense, but since I'm having to educate you on this, I don't expect
you'll be able to help with answer. Here is a bit of advise, please only
try and answer Questions for which you have the answer, OK? I need to get
this solved, not convince you that I don't need to go back to BOL.
Eric
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ffl67J8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> Eric
> I am not following you.
> What do you mean by Warm standby mode?
> Have you looked at "How to set up, maintain, and bring online a standby
> server (Transact-SQL)"
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
database
> will
not.
take
> 8
up
environment)
> transfer
unsure
bring
> from
> this
when
a
> any
One
> here
> copy
> (~4gbs).
locally?
>|||This method may not be officially supported but it works for me.
1. Take the standby database offline.
2. Copy all database files to a new names.
3. Bring the standby database back online.
4. Attach new database using files created in step 2.
Eric Smith wrote:
> Uri,
> I think it is you that needs to go back and learn a bit more about
Log
> Shipping.
> A destination server in a log shipping scheme is referred to as a
"warm
> standby". A hot server would be considered live... A cold standby
would be
> a box that is available and usable but would need the most recent
backup
> restored to be ready to use.
> A warm standby refers to the fact that it's warm and ready to go..
it's not
> live, but its not dormant and cold. Its warm, such as in a Log
Shipping
> scheme. It has all the data and is ready to take over as primary at
most
> any time.
> If you go and try and back up a destination log shipped databsase (a
> warm-standby database) you get the message:
> "Database XXX is in warm-standby state (set by executing RESTORE
WITIH
> STANDBY) and cannot be backed up until the entire load sequence is
> completed. BACKUP DATABASE is terminating abnormallhy" Hence my
original
> question of how do I get a backup of a warm-standby database so I
can move
> it to another server and play with it, such as in a development mode.
> No offense, but since I'm having to educate you on this, I don't
expect
> you'll be able to help with answer. Here is a bit of advise, please
only
> try and answer Questions for which you have the answer, OK? I need
to get[vbcol=seagreen]
> this solved, not convince you that I don't need to go back to BOL.
> Eric
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23ffl67J8DHA.2044@.TK2MSFTNGP10.phx.gbl...
standby[vbcol=seagreen]
> database
That[vbcol=seagreen]
database or[vbcol=seagreen]
> not.
will[vbcol=seagreen]
> take
database[vbcol=seagreen]
> up
copy to[vbcol=seagreen]
> environment)
or rar[vbcol=seagreen]
easily[vbcol=seagreen]
I'm[vbcol=seagreen]
> unsure
to[vbcol=seagreen]
> bring
data[vbcol=seagreen]
ASAP and[vbcol=seagreen]
searchs on[vbcol=seagreen]
apparently[vbcol=seagreen]
> when
off for[vbcol=seagreen]
> a
BOL isn't[vbcol=seagreen]
message[vbcol=seagreen]
WAN.[vbcol=seagreen]
> One
by is[vbcol=seagreen]
bring a[vbcol=seagreen]
long[vbcol=seagreen]
that are[vbcol=seagreen]
> locally?

Copying a Log Shipped database

I have a production server in a distant city across our WAN. One warm
standy by is there in that city, and a second warm standy by is here with me
at Corp HQ.
I need a copy of our data, but I don't want to have to bring a copy of the
Production sever down over the WAN.. that'll take too long (~4gbs). I would
like to take a copy of the local warm standby databases that are read-only.
How can I get a copy of the read-only databases for uses locally?Eric
read topic in BOL about standby servers.
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> I have a production server in a distant city across our WAN. One warm
> standy by is there in that city, and a second warm standy by is here with
me
> at Corp HQ.
> I need a copy of our data, but I don't want to have to bring a copy of the
> Production sever down over the WAN.. that'll take too long (~4gbs). I
would
> like to take a copy of the local warm standby databases that are
read-only.
> How can I get a copy of the read-only databases for uses locally?
>|||I've been all over BOL... this is why I'm asking! Because I'm unsure how
to do it.
I do not want to do a role change or anything.. Nor do I want to bring one
of my StandBy Servers online. I simply get a copy of all our data from my
local Destination servers/Standy server. I need to do this ASAP and would
appreciate some help rather than the standard "look in BOL".
If you know, please tell me a little.. I've done Google searchs on this
topic and looked in BOL, but its tough to narrow down apparently when you
don't know the answer. I've been trying to solve this on and off for a
couple weeks now.. Please help if you can. Sending me to BOL isn't any
help.
Eric
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> Eric
> read topic in BOL about standby servers.
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > I have a production server in a distant city across our WAN. One warm
> > standy by is there in that city, and a second warm standy by is here
with
> me
> > at Corp HQ.
> >
> > I need a copy of our data, but I don't want to have to bring a copy of
the
> > Production sever down over the WAN.. that'll take too long (~4gbs). I
> would
> > like to take a copy of the local warm standby databases that are
> read-only.
> >
> > How can I get a copy of the read-only databases for uses locally?
> >
> >
>|||Eric
I really want to help you.( don't angry)
Unless I missunderstand you can try to move your backup as zip or rar files
over a network. (4 gb is not so big)
Backup your database then create batch file like that
CD \Program Files\WinRAR
WINRAR a D:\databasename%1 D:\database%1.BAK
After running this file you will get rar/zip file you can easily transfer
over a network.
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I've been all over BOL... this is why I'm asking! Because I'm unsure how
> to do it.
> I do not want to do a role change or anything.. Nor do I want to bring one
> of my StandBy Servers online. I simply get a copy of all our data from my
> local Destination servers/Standy server. I need to do this ASAP and would
> appreciate some help rather than the standard "look in BOL".
> If you know, please tell me a little.. I've done Google searchs on this
> topic and looked in BOL, but its tough to narrow down apparently when you
> don't know the answer. I've been trying to solve this on and off for a
> couple weeks now.. Please help if you can. Sending me to BOL isn't any
> help.
> Eric
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > Eric
> > read topic in BOL about standby servers.
> >
> >
> >
> > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > I have a production server in a distant city across our WAN. One warm
> > > standy by is there in that city, and a second warm standy by is here
> with
> > me
> > > at Corp HQ.
> > >
> > > I need a copy of our data, but I don't want to have to bring a copy of
> the
> > > Production sever down over the WAN.. that'll take too long (~4gbs). I
> > would
> > > like to take a copy of the local warm standby databases that are
> > read-only.
> > >
> > > How can I get a copy of the read-only databases for uses locally?
> > >
> > >
> >
> >
>|||No.. reread my Question. I'm well aware that you can backup up a database
and copy that from one place to another.
I do NOT want to backup the database and then copy over our LAN. That will
take many hours. I don't care if 4gb is considered a big database or not.
It's simply a big file to throw across the WAN. Uncompressed it will take 8
hours or something.. compressed it takes 5 hours.
I DO want to use my local warm standby copy. I can't back the database up
because it's in Warm standby mode. So.. can I use this local copy to
move/copy over to a another useable (such as in a development environment)
database. That is my question.
Eric Smith
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> Eric
> I really want to help you.( don't angry)
> Unless I missunderstand you can try to move your backup as zip or rar
files
> over a network. (4 gb is not so big)
> Backup your database then create batch file like that
> CD \Program Files\WinRAR
> WINRAR a D:\databasename%1 D:\database%1.BAK
> After running this file you will get rar/zip file you can easily transfer
> over a network.
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> > I've been all over BOL... this is why I'm asking! Because I'm unsure
how
> > to do it.
> >
> > I do not want to do a role change or anything.. Nor do I want to bring
one
> > of my StandBy Servers online. I simply get a copy of all our data from
my
> > local Destination servers/Standy server. I need to do this ASAP and
would
> > appreciate some help rather than the standard "look in BOL".
> >
> > If you know, please tell me a little.. I've done Google searchs on this
> > topic and looked in BOL, but its tough to narrow down apparently when
you
> > don't know the answer. I've been trying to solve this on and off for a
> > couple weeks now.. Please help if you can. Sending me to BOL isn't any
> > help.
> >
> > Eric
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > Eric
> > > read topic in BOL about standby servers.
> > >
> > >
> > >
> > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > > I have a production server in a distant city across our WAN. One
warm
> > > > standy by is there in that city, and a second warm standy by is here
> > with
> > > me
> > > > at Corp HQ.
> > > >
> > > > I need a copy of our data, but I don't want to have to bring a copy
of
> > the
> > > > Production sever down over the WAN.. that'll take too long (~4gbs).
I
> > > would
> > > > like to take a copy of the local warm standby databases that are
> > > read-only.
> > > >
> > > > How can I get a copy of the read-only databases for uses locally?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Eric
I am not following you.
What do you mean by Warm standby mode?
Have you looked at "How to set up, maintain, and bring online a standby
server (Transact-SQL)"
"Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
> No.. reread my Question. I'm well aware that you can backup up a database
> and copy that from one place to another.
> I do NOT want to backup the database and then copy over our LAN. That
will
> take many hours. I don't care if 4gb is considered a big database or not.
> It's simply a big file to throw across the WAN. Uncompressed it will take
8
> hours or something.. compressed it takes 5 hours.
> I DO want to use my local warm standby copy. I can't back the database up
> because it's in Warm standby mode. So.. can I use this local copy to
> move/copy over to a another useable (such as in a development environment)
> database. That is my question.
> Eric Smith
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> > Eric
> > I really want to help you.( don't angry)
> > Unless I missunderstand you can try to move your backup as zip or rar
> files
> > over a network. (4 gb is not so big)
> > Backup your database then create batch file like that
> > CD \Program Files\WinRAR
> > WINRAR a D:\databasename%1 D:\database%1.BAK
> >
> > After running this file you will get rar/zip file you can easily
transfer
> > over a network.
> >
> >
> > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> > > I've been all over BOL... this is why I'm asking! Because I'm unsure
> how
> > > to do it.
> > >
> > > I do not want to do a role change or anything.. Nor do I want to bring
> one
> > > of my StandBy Servers online. I simply get a copy of all our data
from
> my
> > > local Destination servers/Standy server. I need to do this ASAP and
> would
> > > appreciate some help rather than the standard "look in BOL".
> > >
> > > If you know, please tell me a little.. I've done Google searchs on
this
> > > topic and looked in BOL, but its tough to narrow down apparently when
> you
> > > don't know the answer. I've been trying to solve this on and off for a
> > > couple weeks now.. Please help if you can. Sending me to BOL isn't
any
> > > help.
> > >
> > > Eric
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > > Eric
> > > > read topic in BOL about standby servers.
> > > >
> > > >
> > > >
> > > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > > > I have a production server in a distant city across our WAN. One
> warm
> > > > > standy by is there in that city, and a second warm standy by is
here
> > > with
> > > > me
> > > > > at Corp HQ.
> > > > >
> > > > > I need a copy of our data, but I don't want to have to bring a
copy
> of
> > > the
> > > > > Production sever down over the WAN.. that'll take too long
(~4gbs).
> I
> > > > would
> > > > > like to take a copy of the local warm standby databases that are
> > > > read-only.
> > > > >
> > > > > How can I get a copy of the read-only databases for uses locally?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Uri,
I think it is you that needs to go back and learn a bit more about Log
Shipping.
A destination server in a log shipping scheme is referred to as a "warm
standby". A hot server would be considered live... A cold standby would be
a box that is available and usable but would need the most recent backup
restored to be ready to use.
A warm standby refers to the fact that it's warm and ready to go.. it's not
live, but its not dormant and cold. Its warm, such as in a Log Shipping
scheme. It has all the data and is ready to take over as primary at most
any time.
If you go and try and back up a destination log shipped databsase (a
warm-standby database) you get the message:
"Database XXX is in warm-standby state (set by executing RESTORE WITIH
STANDBY) and cannot be backed up until the entire load sequence is
completed. BACKUP DATABASE is terminating abnormallhy" Hence my original
question of how do I get a backup of a warm-standby database so I can move
it to another server and play with it, such as in a development mode.
No offense, but since I'm having to educate you on this, I don't expect
you'll be able to help with answer. Here is a bit of advise, please only
try and answer Questions for which you have the answer, OK? I need to get
this solved, not convince you that I don't need to go back to BOL.
Eric
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ffl67J8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> Eric
> I am not following you.
> What do you mean by Warm standby mode?
> Have you looked at "How to set up, maintain, and bring online a standby
> server (Transact-SQL)"
>
> "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
> > No.. reread my Question. I'm well aware that you can backup up a
database
> > and copy that from one place to another.
> >
> > I do NOT want to backup the database and then copy over our LAN. That
> will
> > take many hours. I don't care if 4gb is considered a big database or
not.
> > It's simply a big file to throw across the WAN. Uncompressed it will
take
> 8
> > hours or something.. compressed it takes 5 hours.
> >
> > I DO want to use my local warm standby copy. I can't back the database
up
> > because it's in Warm standby mode. So.. can I use this local copy to
> > move/copy over to a another useable (such as in a development
environment)
> > database. That is my question.
> >
> > Eric Smith
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> > > Eric
> > > I really want to help you.( don't angry)
> > > Unless I missunderstand you can try to move your backup as zip or rar
> > files
> > > over a network. (4 gb is not so big)
> > > Backup your database then create batch file like that
> > > CD \Program Files\WinRAR
> > > WINRAR a D:\databasename%1 D:\database%1.BAK
> > >
> > > After running this file you will get rar/zip file you can easily
> transfer
> > > over a network.
> > >
> > >
> > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> > > > I've been all over BOL... this is why I'm asking! Because I'm
unsure
> > how
> > > > to do it.
> > > >
> > > > I do not want to do a role change or anything.. Nor do I want to
bring
> > one
> > > > of my StandBy Servers online. I simply get a copy of all our data
> from
> > my
> > > > local Destination servers/Standy server. I need to do this ASAP and
> > would
> > > > appreciate some help rather than the standard "look in BOL".
> > > >
> > > > If you know, please tell me a little.. I've done Google searchs on
> this
> > > > topic and looked in BOL, but its tough to narrow down apparently
when
> > you
> > > > don't know the answer. I've been trying to solve this on and off for
a
> > > > couple weeks now.. Please help if you can. Sending me to BOL isn't
> any
> > > > help.
> > > >
> > > > Eric
> > > >
> > > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > > news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > > > Eric
> > > > > read topic in BOL about standby servers.
> > > > >
> > > > >
> > > > >
> > > > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > > > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > > > > I have a production server in a distant city across our WAN.
One
> > warm
> > > > > > standy by is there in that city, and a second warm standy by is
> here
> > > > with
> > > > > me
> > > > > > at Corp HQ.
> > > > > >
> > > > > > I need a copy of our data, but I don't want to have to bring a
> copy
> > of
> > > > the
> > > > > > Production sever down over the WAN.. that'll take too long
> (~4gbs).
> > I
> > > > > would
> > > > > > like to take a copy of the local warm standby databases that are
> > > > > read-only.
> > > > > >
> > > > > > How can I get a copy of the read-only databases for uses
locally?
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||This method may not be officially supported but it works for me.
1. Take the standby database offline.
2. Copy all database files to a new names.
3. Bring the standby database back online.
4. Attach new database using files created in step 2.
Eric Smith wrote:
> Uri,
> I think it is you that needs to go back and learn a bit more about
Log
> Shipping.
> A destination server in a log shipping scheme is referred to as a
"warm
> standby". A hot server would be considered live... A cold standby
would be
> a box that is available and usable but would need the most recent
backup
> restored to be ready to use.
> A warm standby refers to the fact that it's warm and ready to go..
it's not
> live, but its not dormant and cold. Its warm, such as in a Log
Shipping
> scheme. It has all the data and is ready to take over as primary at
most
> any time.
> If you go and try and back up a destination log shipped databsase (a
> warm-standby database) you get the message:
> "Database XXX is in warm-standby state (set by executing RESTORE
WITIH
> STANDBY) and cannot be backed up until the entire load sequence is
> completed. BACKUP DATABASE is terminating abnormallhy" Hence my
original
> question of how do I get a backup of a warm-standby database so I
can move
> it to another server and play with it, such as in a development mode.
> No offense, but since I'm having to educate you on this, I don't
expect
> you'll be able to help with answer. Here is a bit of advise, please
only
> try and answer Questions for which you have the answer, OK? I need
to get
> this solved, not convince you that I don't need to go back to BOL.
> Eric
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23ffl67J8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> > Eric
> > I am not following you.
> > What do you mean by Warm standby mode?
> > Have you looked at "How to set up, maintain, and bring online a
standby
> > server (Transact-SQL)"
> >
> >
> > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > news:#OQwM2J8DHA.2576@.TK2MSFTNGP11.phx.gbl...
> > > No.. reread my Question. I'm well aware that you can backup up a
> database
> > > and copy that from one place to another.
> > >
> > > I do NOT want to backup the database and then copy over our LAN.
That
> > will
> > > take many hours. I don't care if 4gb is considered a big
database or
> not.
> > > It's simply a big file to throw across the WAN. Uncompressed it
will
> take
> > 8
> > > hours or something.. compressed it takes 5 hours.
> > >
> > > I DO want to use my local warm standby copy. I can't back the
database
> up
> > > because it's in Warm standby mode. So.. can I use this local
copy to
> > > move/copy over to a another useable (such as in a development
> environment)
> > > database. That is my question.
> > >
> > > Eric Smith
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:es1BCeI8DHA.3380@.tk2msftngp13.phx.gbl...
> > > > Eric
> > > > I really want to help you.( don't angry)
> > > > Unless I missunderstand you can try to move your backup as zip
or rar
> > > files
> > > > over a network. (4 gb is not so big)
> > > > Backup your database then create batch file like that
> > > > CD \Program Files\WinRAR
> > > > WINRAR a D:\databasename%1 D:\database%1.BAK
> > > >
> > > > After running this file you will get rar/zip file you can
easily
> > transfer
> > > > over a network.
> > > >
> > > >
> > > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in message
> > > > news:epR7XUI8DHA.360@.TK2MSFTNGP12.phx.gbl...
> > > > > I've been all over BOL... this is why I'm asking! Because
I'm
> unsure
> > > how
> > > > > to do it.
> > > > >
> > > > > I do not want to do a role change or anything.. Nor do I want
to
> bring
> > > one
> > > > > of my StandBy Servers online. I simply get a copy of all our
data
> > from
> > > my
> > > > > local Destination servers/Standy server. I need to do this
ASAP and
> > > would
> > > > > appreciate some help rather than the standard "look in BOL".
> > > > >
> > > > > If you know, please tell me a little.. I've done Google
searchs on
> > this
> > > > > topic and looked in BOL, but its tough to narrow down
apparently
> when
> > > you
> > > > > don't know the answer. I've been trying to solve this on and
off for
> a
> > > > > couple weeks now.. Please help if you can. Sending me to
BOL isn't
> > any
> > > > > help.
> > > > >
> > > > > Eric
> > > > >
> > > > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > > > news:%23kIY9yH8DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > > > > Eric
> > > > > > read topic in BOL about standby servers.
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Eric Smith" <esmith_at_cmsstl_dot_com@.nospam> wrote in
message
> > > > > > news:uZ36VMG8DHA.3704@.tk2msftngp13.phx.gbl...
> > > > > > > I have a production server in a distant city across our
WAN.
> One
> > > warm
> > > > > > > standy by is there in that city, and a second warm standy
by is
> > here
> > > > > with
> > > > > > me
> > > > > > > at Corp HQ.
> > > > > > >
> > > > > > > I need a copy of our data, but I don't want to have to
bring a
> > copy
> > > of
> > > > > the
> > > > > > > Production sever down over the WAN.. that'll take too
long
> > (~4gbs).
> > > I
> > > > > > would
> > > > > > > like to take a copy of the local warm standby databases
that are
> > > > > > read-only.
> > > > > > >
> > > > > > > How can I get a copy of the read-only databases for uses
> locally?
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >

copying a database across servers

I'm developing a web application locally using SQL Server 2000. When it's time for me to throw it up on my host, how do I copy the database from my computer to their server? Thanks.use the DTS tools provided with SQL Server (import/export data).

What I usually do is script the database structure (and objects), set that up and check it over, THEN transfer the data, but whether you want to do it that way is up to you

j|||Another way is to simply stop your sql server and copy the database and log files to the other location and then attach the database.

Regards|||There are multiple ways as in using DTS or other tools. BUt if the other server is far apart and you donot want to use the internet bandwidth then I feel the best bet is to backup the database and then transfer/FTP the DB backup to be restored into the destination server. Copying the data and the log files are fine. But not the cleanest of approaches AFAIK.|||All solutions provided here are good, I would personally go with Atrax' Scripting solution, it is probably the best way to do it if you don't need to transfer any data and bandwidth is an issue, but you will need the admin on the remote server to launch your .sql script. Here's what I do: Select all your user tables, right-click and copy. Paste them into the Query Analyzer and boom you've got the script to create your tables. Same with SPROC's and any other objects you have created. You can paste them all into one file.

Add this to the top of your SQL Script if the DB does not exist on the server:

CREATE DATABASE [YourDBName]--Skip these two lines if the DB does exist
Go
USE YourDBName -- Add this regardless

and Save it. The script can now be executed from the QA on any SQL server to create your DB.
Piece-o-cake.

If you did need to transfer any data, you could create a DTS package to export the data to the remote server. But of course you have to have access and it depends on the amount of data you have stored and bandwidth as to how effective a transfer would be.

But again, all of the solutions provided here by the other posters so far are viable. It's a matter of personal preference. All though, if you script it and something blows up locally, you can easily recreate your database without having to copy it back from the remote server. An unlikey scenario, but entirely possible... just a thought.

Good luck.

Copy rows across to another db

Hello all,

first of all please forgive me for asking what might turn out to be a very stupid question. I am new to SQL.

Here is my problem. I have to identical databases, one used for testing (db_test), the other for production (db_prod). What I want to do is to grab a row from a table in db_test and insert it into the relative table into db_prod.

Now, I know to use INSERT INTO, however I having problems specifiying which database to select from and which to insert to. I have tried specifying the full path ([db_test].[dbo].[Table1]) but it does not work. I can only get statements to work if is include the "USE [db_test]" line at the top, but I can't then access the other db.

Any suggestions?It is surprising that the cross database INSERT does not work. Perhaps you could post the query so I could see is there is an error?