Hi folks
I am writing a humdinger of a stored procedure, which I can use to
automatically create a second copy of a database, and ensure that the
tables, etc, are all of the same specification.
Here's what I've done so far:
1. Check to see if the second copy of the database exists. If it doesn't
exist, create it.
2. Build up a dynamic SQL string, by using the contents of the sysObjects
and sysColumns tables in the first database.
3. Build up a dynamic SQL string, by using the contents of the sysObjects
and sysColumns tables in the second database.
4. Compare the two SQL strings to ensure that each table in the second
database is an exact replica of the tables in the first database.
By the end of all of this, the result is that the second database contains
exactly the same tables as the first database, with both sets of tables
being identical. The only exception is that the second database doesn't
have any relationships set up between the tables, although that's to come.
So far, so good. However, when I turned my attention to the stored
procedures in the first database, it all went a bit wrong. I can use the
sysObjects and sysComments tables to build up the dynamic SQL from the first
database that would have to be executed against the second database.
However, I've discovered that it isn't possible to create a stored procedure
in any database other than the one you are currently working with. If I
append "Uses <databasename>" at the beginning of the dynamic SQL string, it
then complains that the "CREATE PROCEDURE" command should be the first
command in any batch process.
Can anyone tell me if it's possible for me to do this?
Incidentally, before anyone suggests it, I've never done any DTS stuff
before, so I'm hoping there are other ways of doing it.
TIA
UK_CodemonkeyHave you thought of backing up your database and
restoring it with a new name?|||Ian
DECLARE @.dbname AS VARCHAR(100),@.sql AS VARCHAR(100)
SET @.dbname ='pubs'
SET @.sql ='
CREATE PROCEDURE dbo.nameofSP
AS
SELECT * FROM '+@.dbname+'.dbo.Authors'
EXEC (@.sql)
EXEC dbo.nameofSP
Note: Learn using DTS Packages
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:drahq7$b7r$1$8300dec7@.news.demon.co.uk...
> Hi folks
> I am writing a humdinger of a stored procedure, which I can use to
> automatically create a second copy of a database, and ensure that the
> tables, etc, are all of the same specification.
> Here's what I've done so far:
> 1. Check to see if the second copy of the database exists. If it doesn't
> exist, create it.
> 2. Build up a dynamic SQL string, by using the contents of the sysObjects
> and sysColumns tables in the first database.
> 3. Build up a dynamic SQL string, by using the contents of the sysObjects
> and sysColumns tables in the second database.
> 4. Compare the two SQL strings to ensure that each table in the second
> database is an exact replica of the tables in the first database.
> By the end of all of this, the result is that the second database contains
> exactly the same tables as the first database, with both sets of tables
> being identical. The only exception is that the second database doesn't
> have any relationships set up between the tables, although that's to come.
> So far, so good. However, when I turned my attention to the stored
> procedures in the first database, it all went a bit wrong. I can use the
> sysObjects and sysComments tables to build up the dynamic SQL from the
> first database that would have to be executed against the second database.
> However, I've discovered that it isn't possible to create a stored
> procedure in any database other than the one you are currently working
> with. If I append "Uses <databasename>" at the beginning of the dynamic
> SQL string, it then complains that the "CREATE PROCEDURE" command should
> be the first command in any batch process.
> Can anyone tell me if it's possible for me to do this?
> Incidentally, before anyone suggests it, I've never done any DTS stuff
> before, so I'm hoping there are other ways of doing it.
> TIA
>
> UK_Codemonkey
>|||Uri
I'll give this a bash. Incidentally, I've been administering SQL Server for
the past 4 1/2 years, and have never needed to get into DTS, mainly because
I've been able to do everything through SQL Stored Procedures.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23x2PuxnIGHA.140@.TK2MSFTNGP12.phx.gbl...
> Ian
> DECLARE @.dbname AS VARCHAR(100),@.sql AS VARCHAR(100)
> SET @.dbname ='pubs'
> SET @.sql ='
> CREATE PROCEDURE dbo.nameofSP
> AS
> SELECT * FROM '+@.dbname+'.dbo.Authors'
> EXEC (@.sql)
> EXEC dbo.nameofSP
>
> Note: Learn using DTS Packages
>
>
> "Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
> news:drahq7$b7r$1$8300dec7@.news.demon.co.uk...
>
Showing posts with label second. Show all posts
Showing posts with label second. Show all posts
Thursday, March 22, 2012
Wednesday, March 7, 2012
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?
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?
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
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?
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
Saturday, February 25, 2012
Copying a column to a SSIS package variable
I need to use a value retrieved in one data flow in the second data flow. What's the best way to do this?
How do I copy the column retrieved to a variable so I can use that variable in the second data flow?
Data Flow implies multiple rows, which doesn't naturally fit with a single variable.
Normally I would be using an Exec SQL Task to populate a variable from a table. You could use a Script Component in the data flow, or perhaps the Recordset Destination if there are several rows.
If reading just one value/line from a file for example, then I'd just use the Script Task.
Subscribe to:
Posts (Atom)