correct syntax for ADO.net recordsets Select method.

I have a interger stored in x.

I want to use x in a SELECT statement like so :

SELECT * from aTable WHERE A_Column = x

This select statement is then assigned to and passed as a string like :

sql = "SELECT * from aTable WHERE A_Column = x"

How does the x get interpreted correctly ?Try:

sql = "SELECT * from aTable WHERE A_Column = " & x

Copying the backup to another drive

I have set up our new SS2k install to use the Bulk-Logged backup method,
backup up TRN's every night and the whole DB every Sunday using the Agent and
a fairly out-of-the-box Maint plan.
What I would like to do is copy the latest file to another machine on our
network after every backup run. That machine is attached to an offsight SAN.
I tried simply pointing the backup directory at that machine, but people
noted this is a bad idea and it's not all that easy to do anyway.
I think the easy way to do this is to edit the backup jobs and add a copy.
However I don't see a way to name the file to copy, which will change every
day. Also whenever I've made changes like this in the past, the maint plan no
longer works, yet this is very important for our install (so other people can
edit it).
Any advice?"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:70154059-0A56-41A1-B07B-9DCD47C884BD@.microsoft.com...
>I have set up our new SS2k install to use the Bulk-Logged backup method,
> backup up TRN's every night and the whole DB every Sunday using the Agent
> and
> a fairly out-of-the-box Maint plan.
> What I would like to do is copy the latest file to another machine on our
> network after every backup run. That machine is attached to an offsight
> SAN.
> I tried simply pointing the backup directory at that machine, but people
> noted this is a bad idea and it's not all that easy to do anyway.
> I think the easy way to do this is to edit the backup jobs and add a copy.
> However I don't see a way to name the file to copy, which will change
> every
> day. Also whenever I've made changes like this in the past, the maint plan
> no
> longer works, yet this is very important for our install (so other people
> can
> edit it).
> Any advice?
The most easily maintainable method (in my opinion of your given scenario)
is to create a DTS job that will perform the backups and do the copying
process. The ActiveX scripting (VBScript) in DTS is conducive to making up
filenames, etc. and is fairly straightforward.
Then you simple schedule a job to fire off the DTS package.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Actually, backing up to a remote file share is pretty easy and is IMHO, a
best practice.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:70154059-0A56-41A1-B07B-9DCD47C884BD@.microsoft.com...
> I have set up our new SS2k install to use the Bulk-Logged backup method,
> backup up TRN's every night and the whole DB every Sunday using the Agent
and
> a fairly out-of-the-box Maint plan.
> What I would like to do is copy the latest file to another machine on our
> network after every backup run. That machine is attached to an offsight
SAN.
> I tried simply pointing the backup directory at that machine, but people
> noted this is a bad idea and it's not all that easy to do anyway.
> I think the easy way to do this is to edit the backup jobs and add a copy.
> However I don't see a way to name the file to copy, which will change
every
> day. Also whenever I've made changes like this in the past, the maint plan
no
> longer works, yet this is very important for our install (so other people
can
> edit it).
> Any advice?|||Geoff N. Hiten wrote:
> Actually, backing up to a remote file share is pretty easy and is
> IMHO, a best practice.
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
>
I don't agree. You are completely throttling the backup based on your
network bandwidth. For small databases this may be fine, but everything
else will likely suffer in terms of backup speed. Given that a native
backup writes out a backup file the size of the database, using a
network share will likely slow it down. That applies as well to backup
products like LiteSpeed which thrive on available disk bandwidth. Plus,
do you really want to flood the network pipe on the server with backup
data which could also cause network performance problems for all
connected users.
My experience has been to back up locally (SAN, NAS, local drive) and
then move off to the final location (tape, network share, etc.).
--
David Gugick
Imceda Software
www.imceda.com|||I did include a warning in the article about possible network saturation.
In practice I use a dedicated backup network and compression software to
limit impact on production systems. Yes, it is more expensive in terms of
disk storage and network capacity, however I believe it is the best approach
for database recoverability and backup management. I combine the backup to
disk woth a tape archive rotation to give me some backup history. Since I
am truly paranoid about my backups, I even have a separate restore folder so
my tape process can continue uninterrupted eve if I have to restore from
tape.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23reYGbk1EHA.3468@.TK2MSFTNGP14.phx.gbl...
> Geoff N. Hiten wrote:
> > Actually, backing up to a remote file share is pretty easy and is
> > IMHO, a best practice.
> >
> > HowTo: Backup to UNC name using Database Maintenance Wizard
> > http://support.microsoft.com/?kbid=555128
> >
> I don't agree. You are completely throttling the backup based on your
> network bandwidth. For small databases this may be fine, but everything
> else will likely suffer in terms of backup speed. Given that a native
> backup writes out a backup file the size of the database, using a
> network share will likely slow it down. That applies as well to backup
> products like LiteSpeed which thrive on available disk bandwidth. Plus,
> do you really want to flood the network pipe on the server with backup
> data which could also cause network performance problems for all
> connected users.
> My experience has been to back up locally (SAN, NAS, local drive) and
> then move off to the final location (tape, network share, etc.).
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Geoff N. Hiten wrote:
> I did include a warning in the article about possible network
> saturation.
> In practice I use a dedicated backup network and compression software
> to limit impact on production systems. Yes, it is more expensive in
> terms of disk storage and network capacity, however I believe it is
> the best approach for database recoverability and backup management.
> I combine the backup to disk woth a tape archive rotation to give me
> some backup history. Since I am truly paranoid about my backups, I
> even have a separate restore folder so my tape process can continue
> uninterrupted eve if I have to restore from tape.
>
I agree. For safety and faster recovery, it's good to have some backups
available on the network separate from the server drives as well as on
tape stored off-site. Compression is key here. But if you're using a
backup product that adds compression, like LiteSpeed, you'll still have
better performance backing up locally and then copying the files over
the network after the backup is complete. You could use LiteSpeed to
dump to a UNC, but you will throttle backup speed a lot and many
companies are very interested in keeping backup times to a minimum.
If you were not network or time constrained, you could back up directly
to a UNC, but I would still argue that using native backup in this
design would be silly because of the large backup sizes.
--
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" wrote:
> I agree. For safety and faster recovery, it's good to have some backups
> available on the network separate from the server drives as well as on
> tape stored off-site.
This is my basic goal. I want to have the file write out to disk, then ZIP
it, copy the ZIPed file to the SAN'ed drive, and then finally delete the ZIP.
The result would be an uncompressed original TRN on the DB's drive, which
I'll let SQL Server periodically delete.
So then back to the original question. When I add commands to the backup
steps it seems to make the Maint Plan stop working. Yet it is fairly
important to me to allow that to continue working so future admins can manage
the system easily. I actually find it very surprising the Agent doesn't have
Compress and Copy To options, given how painfully obvious these feature seem
to me.
So is there any way to do this? I believe the SQL Server account is logged
into a non-domain account, which might complicate things. But even having the
backup compress after saving would be useful, and then I could "copy from"
the SAN machine.|||I do use LiteSpeed. It definitely helps backup performance and space
consumption. I find a properly configured UNC share can digest a backup
much faster than all but the very fastest multi-drive tape libraries and
certainly close to a local drive under most conditions. Of course,
'properly configured' to me means a RAID 1+0 UltraSCSI-320 array with 146GB
10KRPM drives and a Ultra320 RAID controller and enclosure. With this
setup, I can run backups during a small overnight window and spin them to
tape during the day. I also use a dedicated backup network to separate the
load. Finally, since I am running a cluster, attaching the tape drive
directly to the host unit is not an option. SAN backup options were also
not useful since I needed to keep multiple LUNS in synch and I didn't want
to restore an entire LUN to retrieve a single database file. All the
benefits of a multi-stage disk/tape backup at about half of the cost.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O96IJxl1EHA.164@.TK2MSFTNGP10.phx.gbl...
> Geoff N. Hiten wrote:
> > I did include a warning in the article about possible network
> > saturation.
> > In practice I use a dedicated backup network and compression software
> > to limit impact on production systems. Yes, it is more expensive in
> > terms of disk storage and network capacity, however I believe it is
> > the best approach for database recoverability and backup management.
> > I combine the backup to disk woth a tape archive rotation to give me
> > some backup history. Since I am truly paranoid about my backups, I
> > even have a separate restore folder so my tape process can continue
> > uninterrupted eve if I have to restore from tape.
> >
> >
> I agree. For safety and faster recovery, it's good to have some backups
> available on the network separate from the server drives as well as on
> tape stored off-site. Compression is key here. But if you're using a
> backup product that adds compression, like LiteSpeed, you'll still have
> better performance backing up locally and then copying the files over
> the network after the backup is complete. You could use LiteSpeed to
> dump to a UNC, but you will throttle backup speed a lot and many
> companies are very interested in keeping backup times to a minimum.
> If you were not network or time constrained, you could back up directly
> to a UNC, but I would still argue that using native backup in this
> design would be silly because of the large backup sizes.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Copying the backup to another drive

I have set up our new SS2k install to use the Bulk-Logged backup method,
backup up TRN's every night and the whole DB every Sunday using the Agent an
d
a fairly out-of-the-box Maint plan.
What I would like to do is copy the latest file to another machine on our
network after every backup run. That machine is attached to an offsight SAN.
I tried simply pointing the backup directory at that machine, but people
noted this is a bad idea and it's not all that easy to do anyway.
I think the easy way to do this is to edit the backup jobs and add a copy.
However I don't see a way to name the file to copy, which will change every
day. Also whenever I've made changes like this in the past, the maint plan n
o
longer works, yet this is very important for our install (so other people ca
n
edit it).
Any advice?"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:70154059-0A56-41A1-B07B-9DCD47C884BD@.microsoft.com...
>I have set up our new SS2k install to use the Bulk-Logged backup method,
> backup up TRN's every night and the whole DB every Sunday using the Agent
> and
> a fairly out-of-the-box Maint plan.
> What I would like to do is copy the latest file to another machine on our
> network after every backup run. That machine is attached to an offsight
> SAN.
> I tried simply pointing the backup directory at that machine, but people
> noted this is a bad idea and it's not all that easy to do anyway.
> I think the easy way to do this is to edit the backup jobs and add a copy.
> However I don't see a way to name the file to copy, which will change
> every
> day. Also whenever I've made changes like this in the past, the maint plan
> no
> longer works, yet this is very important for our install (so other people
> can
> edit it).
> Any advice?
The most easily maintainable method (in my opinion of your given scenario)
is to create a DTS job that will perform the backups and do the copying
process. The ActiveX scripting (VBScript) in DTS is conducive to making up
filenames, etc. and is fairly straightforward.
Then you simple schedule a job to fire off the DTS package.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Actually, backing up to a remote file share is pretty easy and is IMHO, a
best practice.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:70154059-0A56-41A1-B07B-9DCD47C884BD@.microsoft.com...
> I have set up our new SS2k install to use the Bulk-Logged backup method,
> backup up TRN's every night and the whole DB every Sunday using the Agent
and
> a fairly out-of-the-box Maint plan.
> What I would like to do is copy the latest file to another machine on our
> network after every backup run. That machine is attached to an offsight
SAN.
> I tried simply pointing the backup directory at that machine, but people
> noted this is a bad idea and it's not all that easy to do anyway.
> I think the easy way to do this is to edit the backup jobs and add a copy.
> However I don't see a way to name the file to copy, which will change
every
> day. Also whenever I've made changes like this in the past, the maint plan
no
> longer works, yet this is very important for our install (so other people
can
> edit it).
> Any advice?|||Geoff N. Hiten wrote:
> Actually, backing up to a remote file share is pretty easy and is
> IMHO, a best practice.
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
>
I don't agree. You are completely throttling the backup based on your
network bandwidth. For small databases this may be fine, but everything
else will likely suffer in terms of backup speed. Given that a native
backup writes out a backup file the size of the database, using a
network share will likely slow it down. That applies as well to backup
products like LiteSpeed which thrive on available disk bandwidth. Plus,
do you really want to flood the network pipe on the server with backup
data which could also cause network performance problems for all
connected users.
My experience has been to back up locally (SAN, NAS, local drive) and
then move off to the final location (tape, network share, etc.).
David Gugick
Imceda Software
www.imceda.com|||I did include a warning in the article about possible network saturation.
In practice I use a dedicated backup network and compression software to
limit impact on production systems. Yes, it is more expensive in terms of
disk storage and network capacity, however I believe it is the best approach
for database recoverability and backup management. I combine the backup to
disk woth a tape archive rotation to give me some backup history. Since I
am truly paranoid about my backups, I even have a separate restore folder so
my tape process can continue uninterrupted eve if I have to restore from
tape.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23reYGbk1EHA.3468@.TK2MSFTNGP14.phx.gbl...
> Geoff N. Hiten wrote:
> I don't agree. You are completely throttling the backup based on your
> network bandwidth. For small databases this may be fine, but everything
> else will likely suffer in terms of backup speed. Given that a native
> backup writes out a backup file the size of the database, using a
> network share will likely slow it down. That applies as well to backup
> products like LiteSpeed which thrive on available disk bandwidth. Plus,
> do you really want to flood the network pipe on the server with backup
> data which could also cause network performance problems for all
> connected users.
> My experience has been to back up locally (SAN, NAS, local drive) and
> then move off to the final location (tape, network share, etc.).
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Geoff N. Hiten wrote:
> I did include a warning in the article about possible network
> saturation.
> In practice I use a dedicated backup network and compression software
> to limit impact on production systems. Yes, it is more expensive in
> terms of disk storage and network capacity, however I believe it is
> the best approach for database recoverability and backup management.
> I combine the backup to disk woth a tape archive rotation to give me
> some backup history. Since I am truly paranoid about my backups, I
> even have a separate restore folder so my tape process can continue
> uninterrupted eve if I have to restore from tape.
>
I agree. For safety and faster recovery, it's good to have some backups
available on the network separate from the server drives as well as on
tape stored off-site. Compression is key here. But if you're using a
backup product that adds compression, like LiteSpeed, you'll still have
better performance backing up locally and then copying the files over
the network after the backup is complete. You could use LiteSpeed to
dump to a UNC, but you will throttle backup speed a lot and many
companies are very interested in keeping backup times to a minimum.
If you were not network or time constrained, you could back up directly
to a UNC, but I would still argue that using native backup in this
design would be silly because of the large backup sizes.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" wrote:

> I agree. For safety and faster recovery, it's good to have some backups
> available on the network separate from the server drives as well as on
> tape stored off-site.
This is my basic goal. I want to have the file write out to disk, then ZIP
it, copy the ZIPed file to the SAN'ed drive, and then finally delete the ZIP
.
The result would be an uncompressed original TRN on the DB's drive, which
I'll let SQL Server periodically delete.
So then back to the original question. When I add commands to the backup
steps it seems to make the Maint Plan stop working. Yet it is fairly
important to me to allow that to continue working so future admins can manag
e
the system easily. I actually find it very surprising the Agent doesn't have
Compress and Copy To options, given how painfully obvious these feature seem
to me.
So is there any way to do this? I believe the SQL Server account is logged
into a non-domain account, which might complicate things. But even having th
e
backup compress after saving would be useful, and then I could "copy from"
the SAN machine.|||I do use LiteSpeed. It definitely helps backup performance and space
consumption. I find a properly configured UNC share can digest a backup
much faster than all but the very fastest multi-drive tape libraries and
certainly close to a local drive under most conditions. Of course,
'properly configured' to me means a RAID 1+0 UltraSCSI-320 array with 146GB
10KRPM drives and a Ultra320 RAID controller and enclosure. With this
setup, I can run backups during a small overnight window and spin them to
tape during the day. I also use a dedicated backup network to separate the
load. Finally, since I am running a cluster, attaching the tape drive
directly to the host unit is not an option. SAN backup options were also
not useful since I needed to keep multiple LUNS in synch and I didn't want
to restore an entire LUN to retrieve a single database file. All the
benefits of a multi-stage disk/tape backup at about half of the cost.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O96IJxl1EHA.164@.TK2MSFTNGP10.phx.gbl...
> Geoff N. Hiten wrote:
> I agree. For safety and faster recovery, it's good to have some backups
> available on the network separate from the server drives as well as on
> tape stored off-site. Compression is key here. But if you're using a
> backup product that adds compression, like LiteSpeed, you'll still have
> better performance backing up locally and then copying the files over
> the network after the backup is complete. You could use LiteSpeed to
> dump to a UNC, but you will throttle backup speed a lot and many
> companies are very interested in keeping backup times to a minimum.
> If you were not network or time constrained, you could back up directly
> to a UNC, but I would still argue that using native backup in this
> design would be silly because of the large backup sizes.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Copying the backup to another drive

I have set up our new SS2k install to use the Bulk-Logged backup method,
backup up TRN's every night and the whole DB every Sunday using the Agent and
a fairly out-of-the-box Maint plan.
What I would like to do is copy the latest file to another machine on our
network after every backup run. That machine is attached to an offsight SAN.
I tried simply pointing the backup directory at that machine, but people
noted this is a bad idea and it's not all that easy to do anyway.
I think the easy way to do this is to edit the backup jobs and add a copy.
However I don't see a way to name the file to copy, which will change every
day. Also whenever I've made changes like this in the past, the maint plan no
longer works, yet this is very important for our install (so other people can
edit it).
Any advice?
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:70154059-0A56-41A1-B07B-9DCD47C884BD@.microsoft.com...
>I have set up our new SS2k install to use the Bulk-Logged backup method,
> backup up TRN's every night and the whole DB every Sunday using the Agent
> and
> a fairly out-of-the-box Maint plan.
> What I would like to do is copy the latest file to another machine on our
> network after every backup run. That machine is attached to an offsight
> SAN.
> I tried simply pointing the backup directory at that machine, but people
> noted this is a bad idea and it's not all that easy to do anyway.
> I think the easy way to do this is to edit the backup jobs and add a copy.
> However I don't see a way to name the file to copy, which will change
> every
> day. Also whenever I've made changes like this in the past, the maint plan
> no
> longer works, yet this is very important for our install (so other people
> can
> edit it).
> Any advice?
The most easily maintainable method (in my opinion of your given scenario)
is to create a DTS job that will perform the backups and do the copying
process. The ActiveX scripting (VBScript) in DTS is conducive to making up
filenames, etc. and is fairly straightforward.
Then you simple schedule a job to fire off the DTS package.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Actually, backing up to a remote file share is pretty easy and is IMHO, a
best practice.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:70154059-0A56-41A1-B07B-9DCD47C884BD@.microsoft.com...
> I have set up our new SS2k install to use the Bulk-Logged backup method,
> backup up TRN's every night and the whole DB every Sunday using the Agent
and
> a fairly out-of-the-box Maint plan.
> What I would like to do is copy the latest file to another machine on our
> network after every backup run. That machine is attached to an offsight
SAN.
> I tried simply pointing the backup directory at that machine, but people
> noted this is a bad idea and it's not all that easy to do anyway.
> I think the easy way to do this is to edit the backup jobs and add a copy.
> However I don't see a way to name the file to copy, which will change
every
> day. Also whenever I've made changes like this in the past, the maint plan
no
> longer works, yet this is very important for our install (so other people
can
> edit it).
> Any advice?
|||Geoff N. Hiten wrote:
> Actually, backing up to a remote file share is pretty easy and is
> IMHO, a best practice.
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
>
I don't agree. You are completely throttling the backup based on your
network bandwidth. For small databases this may be fine, but everything
else will likely suffer in terms of backup speed. Given that a native
backup writes out a backup file the size of the database, using a
network share will likely slow it down. That applies as well to backup
products like LiteSpeed which thrive on available disk bandwidth. Plus,
do you really want to flood the network pipe on the server with backup
data which could also cause network performance problems for all
connected users.
My experience has been to back up locally (SAN, NAS, local drive) and
then move off to the final location (tape, network share, etc.).
David Gugick
Imceda Software
www.imceda.com
|||I did include a warning in the article about possible network saturation.
In practice I use a dedicated backup network and compression software to
limit impact on production systems. Yes, it is more expensive in terms of
disk storage and network capacity, however I believe it is the best approach
for database recoverability and backup management. I combine the backup to
disk woth a tape archive rotation to give me some backup history. Since I
am truly paranoid about my backups, I even have a separate restore folder so
my tape process can continue uninterrupted eve if I have to restore from
tape.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23reYGbk1EHA.3468@.TK2MSFTNGP14.phx.gbl...
> Geoff N. Hiten wrote:
> I don't agree. You are completely throttling the backup based on your
> network bandwidth. For small databases this may be fine, but everything
> else will likely suffer in terms of backup speed. Given that a native
> backup writes out a backup file the size of the database, using a
> network share will likely slow it down. That applies as well to backup
> products like LiteSpeed which thrive on available disk bandwidth. Plus,
> do you really want to flood the network pipe on the server with backup
> data which could also cause network performance problems for all
> connected users.
> My experience has been to back up locally (SAN, NAS, local drive) and
> then move off to the final location (tape, network share, etc.).
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Geoff N. Hiten wrote:
> I did include a warning in the article about possible network
> saturation.
> In practice I use a dedicated backup network and compression software
> to limit impact on production systems. Yes, it is more expensive in
> terms of disk storage and network capacity, however I believe it is
> the best approach for database recoverability and backup management.
> I combine the backup to disk woth a tape archive rotation to give me
> some backup history. Since I am truly paranoid about my backups, I
> even have a separate restore folder so my tape process can continue
> uninterrupted eve if I have to restore from tape.
>
I agree. For safety and faster recovery, it's good to have some backups
available on the network separate from the server drives as well as on
tape stored off-site. Compression is key here. But if you're using a
backup product that adds compression, like LiteSpeed, you'll still have
better performance backing up locally and then copying the files over
the network after the backup is complete. You could use LiteSpeed to
dump to a UNC, but you will throttle backup speed a lot and many
companies are very interested in keeping backup times to a minimum.
If you were not network or time constrained, you could back up directly
to a UNC, but I would still argue that using native backup in this
design would be silly because of the large backup sizes.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" wrote:

> I agree. For safety and faster recovery, it's good to have some backups
> available on the network separate from the server drives as well as on
> tape stored off-site.
This is my basic goal. I want to have the file write out to disk, then ZIP
it, copy the ZIPed file to the SAN'ed drive, and then finally delete the ZIP.
The result would be an uncompressed original TRN on the DB's drive, which
I'll let SQL Server periodically delete.
So then back to the original question. When I add commands to the backup
steps it seems to make the Maint Plan stop working. Yet it is fairly
important to me to allow that to continue working so future admins can manage
the system easily. I actually find it very surprising the Agent doesn't have
Compress and Copy To options, given how painfully obvious these feature seem
to me.
So is there any way to do this? I believe the SQL Server account is logged
into a non-domain account, which might complicate things. But even having the
backup compress after saving would be useful, and then I could "copy from"
the SAN machine.
|||I do use LiteSpeed. It definitely helps backup performance and space
consumption. I find a properly configured UNC share can digest a backup
much faster than all but the very fastest multi-drive tape libraries and
certainly close to a local drive under most conditions. Of course,
'properly configured' to me means a RAID 1+0 UltraSCSI-320 array with 146GB
10KRPM drives and a Ultra320 RAID controller and enclosure. With this
setup, I can run backups during a small overnight window and spin them to
tape during the day. I also use a dedicated backup network to separate the
load. Finally, since I am running a cluster, attaching the tape drive
directly to the host unit is not an option. SAN backup options were also
not useful since I needed to keep multiple LUNS in synch and I didn't want
to restore an entire LUN to retrieve a single database file. All the
benefits of a multi-stage disk/tape backup at about half of the cost.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O96IJxl1EHA.164@.TK2MSFTNGP10.phx.gbl...
> Geoff N. Hiten wrote:
> I agree. For safety and faster recovery, it's good to have some backups
> available on the network separate from the server drives as well as on
> tape stored off-site. Compression is key here. But if you're using a
> backup product that adds compression, like LiteSpeed, you'll still have
> better performance backing up locally and then copying the files over
> the network after the backup is complete. You could use LiteSpeed to
> dump to a UNC, but you will throttle backup speed a lot and many
> companies are very interested in keeping backup times to a minimum.
> If you were not network or time constrained, you could back up directly
> to a UNC, but I would still argue that using native backup in this
> design would be silly because of the large backup sizes.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
sql

Copying from one field to another in the same row

Is there a simple method within SQL to copy the contents of one field to
another field in the same row?
TGSYes,
UPDATE Sometable Set DestColumn = SourceColumn
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"The Good Son" <good-son@.freeuk.com> schrieb im Newsbeitrag
news:Xns964C68ECC509Dgoodsonfreeukcom@.13
0.133.1.4...
> Is there a simple method within SQL to copy the contents of one field to
> another field in the same row?
>
> TGS|||UPDATE <Table>
SET column1 = column2
thanks and regards
Chandra
"The Good Son" wrote:

> Is there a simple method within SQL to copy the contents of one field to
> another field in the same row?
>
> TGS
>|||UPDATE Table SET col1=col2
WHERE ..........
"The Good Son" <good-son@.freeuk.com> wrote in message
news:Xns964C68ECC509Dgoodsonfreeukcom@.13
0.133.1.4...
> Is there a simple method within SQL to copy the contents of one field to
> another field in the same row?
>
> TGS|||Many thanks to all those who replied.
TGS

Copying DB in SQL 2005

I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
Object method to copy one DB onto a new one on the same server, it looks like
it worked but the Identity Seed and Identity increment properties are missing
on some of the columns on the new DB. Has anyone else experienced this
behavior?
Same here... It also occurs with the Transfer Object task in SSIS, the
Identity attribute is dropped from tables. Currently I am using Dump/Restore
to copy DBs. My preference is to use Transfer Object to transfer only tables
between DBs, but the droppped Identity prevents that solution.
"Garios" wrote:

> I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
> Object method to copy one DB onto a new one on the same server, it looks like
> it worked but the Identity Seed and Identity increment properties are missing
> on some of the columns on the new DB. Has anyone else experienced this
> behavior?

Copying DB in SQL 2005

I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
Object method to copy one DB onto a new one on the same server, it looks like
it worked but the Identity Seed and Identity increment properties are missing
on some of the columns on the new DB. Has anyone else experienced this
behavior?Same here... It also occurs with the Transfer Object task in SSIS, the
Identity attribute is dropped from tables. Currently I am using Dump/Restore
to copy DBs. My preference is to use Transfer Object to transfer only tables
between DBs, but the droppped Identity prevents that solution.
"Garios" wrote:
> I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
> Object method to copy one DB onto a new one on the same server, it looks like
> it worked but the Identity Seed and Identity increment properties are missing
> on some of the columns on the new DB. Has anyone else experienced this
> behavior?

Copying DB in SQL 2005

I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
Object method to copy one DB onto a new one on the same server, it looks lik
e
it worked but the Identity Seed and Identity increment properties are missin
g
on some of the columns on the new DB. Has anyone else experienced this
behavior?Same here... It also occurs with the Transfer Object task in SSIS, the
Identity attribute is dropped from tables. Currently I am using Dump/Restor
e
to copy DBs. My preference is to use Transfer Object to transfer only table
s
between DBs, but the droppped Identity prevents that solution.
"Garios" wrote:

> I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
> Object method to copy one DB onto a new one on the same server, it looks l
ike
> it worked but the Identity Seed and Identity increment properties are miss
ing
> on some of the columns on the new DB. Has anyone else experienced this
> behavior?

Copying a database...

I've used the backup/restore method of copying a database. The only problem
is that all the tables aren't getting copied! There are supposed to be 74
tables but only 60 are coming across--I haven't checked other kinds of
objects.
I've done it twice with the same result. I've been very careful to select
full backup when generating the *.bak file. Any ideas? Please?
Mike...
"...after all He's not a tame lion..."
mporter (mporter@.discussions.microsoft.com) writes:
> I've used the backup/restore method of copying a database. The only
> problem is that all the tables aren't getting copied! There are supposed
> to be 74 tables but only 60 are coming across--I haven't checked other
> kinds of objects.
> I've done it twice with the same result. I've been very careful to select
> full backup when generating the *.bak file. Any ideas? Please?
If you did:
BACKUP DATABASE db TO DISK = 'C:\mybackup.bak'
and there already was a a backup in that file, you did not overwrite it,
but you appended.
Do a RESTORE HEADERONLY on the backup file.
In the future, if you don't wish to keep the old backup, add WITH INIT
to the BACKUP command.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Hi,
Donot overwrite the media file or disk file.
from
killer

Copying a database...

I've used the backup/restore method of copying a database. The only problem
is that all the tables aren't getting copied! There are supposed to be 74
tables but only 60 are coming across--I haven't checked other kinds of
objects.
I've done it twice with the same result. I've been very careful to select
full backup when generating the *.bak file. Any ideas? Please?
Mike...
"...after all He's not a tame lion..."mporter (mporter@.discussions.microsoft.com) writes:
> I've used the backup/restore method of copying a database. The only
> problem is that all the tables aren't getting copied! There are supposed
> to be 74 tables but only 60 are coming across--I haven't checked other
> kinds of objects.
> I've done it twice with the same result. I've been very careful to select
> full backup when generating the *.bak file. Any ideas? Please?
If you did:
BACKUP DATABASE db TO DISK = 'C:\mybackup.bak'
and there already was a a backup in that file, you did not overwrite it,
but you appended.
Do a RESTORE HEADERONLY on the backup file.
In the future, if you don't wish to keep the old backup, add WITH INIT
to the BACKUP command.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,
Donot overwrite the media file or disk file.
from
killer

Copying a database...

I've used the backup/restore method of copying a database. The only problem
is that all the tables aren't getting copied! There are supposed to be 74
tables but only 60 are coming across--I haven't checked other kinds of
objects.
I've done it twice with the same result. I've been very careful to select
full backup when generating the *.bak file. Any ideas? Please?
Mike...
"...after all He's not a tame lion..."mporter (mporter@.discussions.microsoft.com) writes:
> I've used the backup/restore method of copying a database. The only
> problem is that all the tables aren't getting copied! There are supposed
> to be 74 tables but only 60 are coming across--I haven't checked other
> kinds of objects.
> I've done it twice with the same result. I've been very careful to select
> full backup when generating the *.bak file. Any ideas? Please?
If you did:
BACKUP DATABASE db TO DISK = 'C:\mybackup.bak'
and there already was a a backup in that file, you did not overwrite it,
but you appended.
Do a RESTORE HEADERONLY on the backup file.
In the future, if you don't wish to keep the old backup, add WITH INIT
to the BACKUP command.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hi,
Donot overwrite the media file or disk file.
from
killer

Copy Wizard Failure when coping a database on the same machine

I am trying to copy the a database on the same machine (source and destination are the same). The wizard fails using the SMO method. I have attemped to turn on the "Save Transfer logs", but it does not appear to save a file: I have looked in C:\Documents and Settings\<user-name>\Local Settings\Application Data\Microsoft\SQL Server\SMO for all the users on the server. None of them have the SMO directory in the that path nor can I find a SMO directory on the server.

The SQL Server agent account is set to Local System.

I have made the Local Administrator a Sysadmin in SQL server.

Job History after failure:

Date 6/15/2007 8:25:10 PM
Log Job History (CDW_YOUR-BQEHG1XTVQ_YOUR-BQEHG1XTVQ_9)

Step ID 1
Server YOUR-BQEHG1XTVQ
Job Name CDW_YOUR-BQEHG1XTVQ_YOUR-BQEHG1XTVQ_9
Step Name CDW_YOUR-BQEHG1XTVQ_YOUR-BQEHG1XTVQ_9_Step
Duration 00:00:48
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: YOUR-BQEHG1XTVQ\SYSTEM. ...GUE SQL USE [Tango_QA] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[strpcd_firewall_lan_objects_to_add]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[strpcd_firewall_lan_objects_to_add] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stpcd_call_view_your_account_firewall_policies]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[stpcd_call_view_your_account_firewall_policies] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stpcd_call_view_your_account_firewall_objects]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[stpcd_call_view_your_account_firewall_objects] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stpcd_GetSIPNetworkLocation]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[stpcd_GetSIPNetworkLocation] GO IF EXISTS (SELECT * FROM sys.objects WHERE obje... The package execution fa... The step failed.

Stumped as what what to try next or what the issue is?

What is the level of service pack on SQL Server?

BOL refers:
The destination server must be running SQL Server 2005 Service Pack 2 or a later version. The computer on which the Copy Database Wizard runs may be the source or destination server, or a separate computer. This computer must also be running SQL Server 2005 Service Pack 2 or a later version to use all the features of the wizard.

To use the Copy Database Wizard, you must be a member of the sysadmin fixed server role on the source and destination servers. To transfer databases by using the detach-and-attach method, you must have file system access to the file-system share that contains the source database files.

|||The source and the destination server are one and the same. It is running SQL 2005 SP2. I am using SA to make a copy of the database. The wizard starts to copy the database but then fails before coping all of the tables.|||Ensure SA login is not disabled, you might try using SQL SErver service account in this case.

Copy table between SQL servers without logging

Does anyone have a non-DTS method for copying large tables between SQL
servers without logging? bcp and BULK INSERT appear to be for files only.
Is there a way to use them with other SQL tables?Mark
DECLARE @.tablename AS SYSNAME
SET @.tablename = 'pubs..titles'
EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM ' +
@.tablename + ''') AS T')
Note: I assume I have created linked server , may I ask you why not using
DTS?
Also ,this appoach will fail if you have already test table ,what's more it
doesnt transfer pk to the new table.
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vtecr7qe5d1o7c@.corp.supernews.com...
> Does anyone have a non-DTS method for copying large tables between SQL
> servers without logging? bcp and BULK INSERT appear to be for files only.
> Is there a way to use them with other SQL tables?
>|||If the destination table doesn't have to already exist, you can use SELECT *
INTO new_table_name to minimize, but not entirely eliminate, logging.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vtecr7qe5d1o7c@.corp.supernews.com...
> Does anyone have a non-DTS method for copying large tables between SQL
> servers without logging? bcp and BULK INSERT appear to be for files only.
> Is there a way to use them with other SQL tables?
>|||Hi,
Make the recovery model for the database as "Simple". After that create a
linked server to connect to remote server.
1. Create the table strucute
2. Use insert into select * from server.db.dbo.tablename
Thanks
Hari
MCDBA
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vtecr7qe5d1o7c@.corp.supernews.com...
> Does anyone have a non-DTS method for copying large tables between SQL
> servers without logging? bcp and BULK INSERT appear to be for files only.
> Is there a way to use them with other SQL tables?
>|||Hi Aaron,
I feel we cannot use the command "select * into server.dbname.dbo.temptable
from table " between servers.
Thanks
Hari
MCDBA
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uW3NpDzvDHA.3140@.TK2MSFTNGP11.phx.gbl...
> If the destination table doesn't have to already exist, you can use SELECT
*
> INTO new_table_name to minimize, but not entirely eliminate, logging.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> news:vtecr7qe5d1o7c@.corp.supernews.com...
> > Does anyone have a non-DTS method for copying large tables between SQL
> > servers without logging? bcp and BULK INSERT appear to be for files
only.
> > Is there a way to use them with other SQL tables?
> >
> >
>|||Uri,
Thanks for your reply. I am not using DTS because the tables being copied
may change, I am using a cursor to loop through the tables to copy, then
executing code within the cursor to copy the data. I am running two
parallel cursors to maximize transfer speed. My issue is that the log file
is growing to 4+Gig during the copy operation on a 56Gig database. We can
not just backup and restore or copy the physical files in our case. As far
as keys, indexes, etc. they are all scripted and added at the appropriate
times. Database recovery model is Simple.
Previously, I have tried breaking up the copy into chunks and performing a
BACKUP LOG WITH TRUNCATE ONLY between chunks, but with all the overhead,
that was incredibly slower. However, it did use minimal log space.
Obviously, disk space concerns are driving this effort. It's unnecessary,
in our situation, to log the data, why do it? I feel like I am forced to do
so.
Mark
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#U0usDzvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> Mark
> DECLARE @.tablename AS SYSNAME
> SET @.tablename = 'pubs..titles'
> EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM ' +
> @.tablename + ''') AS T')
> Note: I assume I have created linked server , may I ask you why not using
> DTS?
> Also ,this appoach will fail if you have already test table ,what's more
it
> doesnt transfer pk to the new table.
>
>
> "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> news:vtecr7qe5d1o7c@.corp.supernews.com...
> > Does anyone have a non-DTS method for copying large tables between SQL
> > servers without logging? bcp and BULK INSERT appear to be for files
only.
> > Is there a way to use them with other SQL tables?
> >
> >
>|||Mark
Well ,perhaps you need to use Replications , did you think about it?
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vteg11mhsao054@.corp.supernews.com...
> Uri,
> Thanks for your reply. I am not using DTS because the tables being copied
> may change, I am using a cursor to loop through the tables to copy, then
> executing code within the cursor to copy the data. I am running two
> parallel cursors to maximize transfer speed. My issue is that the log
file
> is growing to 4+Gig during the copy operation on a 56Gig database. We can
> not just backup and restore or copy the physical files in our case. As
far
> as keys, indexes, etc. they are all scripted and added at the appropriate
> times. Database recovery model is Simple.
> Previously, I have tried breaking up the copy into chunks and performing a
> BACKUP LOG WITH TRUNCATE ONLY between chunks, but with all the overhead,
> that was incredibly slower. However, it did use minimal log space.
> Obviously, disk space concerns are driving this effort. It's unnecessary,
> in our situation, to log the data, why do it? I feel like I am forced to
do
> so.
> Mark
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#U0usDzvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > Mark
> > DECLARE @.tablename AS SYSNAME
> > SET @.tablename = 'pubs..titles'
> >
> > EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM '
+
> > @.tablename + ''') AS T')
> >
> > Note: I assume I have created linked server , may I ask you why not
using
> > DTS?
> > Also ,this appoach will fail if you have already test table ,what's more
> it
> > doesnt transfer pk to the new table.
> >
> >
> >
> >
> > "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> > news:vtecr7qe5d1o7c@.corp.supernews.com...
> > > Does anyone have a non-DTS method for copying large tables between SQL
> > > servers without logging? bcp and BULK INSERT appear to be for files
> only.
> > > Is there a way to use them with other SQL tables?
> > >
> > >
> >
> >
>|||Yes... and then again, a lot of overhead and additional database limitations
to perform a simple copy. I wish I just had the cash for larger drives.
Thanks for your comments.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#RjlOczvDHA.2712@.TK2MSFTNGP11.phx.gbl...
> Mark
> Well ,perhaps you need to use Replications , did you think about it?
>
> "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> news:vteg11mhsao054@.corp.supernews.com...
> > Uri,
> >
> > Thanks for your reply. I am not using DTS because the tables being
copied
> > may change, I am using a cursor to loop through the tables to copy, then
> > executing code within the cursor to copy the data. I am running two
> > parallel cursors to maximize transfer speed. My issue is that the log
> file
> > is growing to 4+Gig during the copy operation on a 56Gig database. We
can
> > not just backup and restore or copy the physical files in our case. As
> far
> > as keys, indexes, etc. they are all scripted and added at the
appropriate
> > times. Database recovery model is Simple.
> >
> > Previously, I have tried breaking up the copy into chunks and performing
a
> > BACKUP LOG WITH TRUNCATE ONLY between chunks, but with all the overhead,
> > that was incredibly slower. However, it did use minimal log space.
> >
> > Obviously, disk space concerns are driving this effort. It's
unnecessary,
> > in our situation, to log the data, why do it? I feel like I am forced
to
> do
> > so.
> >
> > Mark
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:#U0usDzvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > > Mark
> > > DECLARE @.tablename AS SYSNAME
> > > SET @.tablename = 'pubs..titles'
> > >
> > > EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM
'
> +
> > > @.tablename + ''') AS T')
> > >
> > > Note: I assume I have created linked server , may I ask you why not
> using
> > > DTS?
> > > Also ,this appoach will fail if you have already test table ,what's
more
> > it
> > > doesnt transfer pk to the new table.
> > >
> > >
> > >
> > >
> > > "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> > > news:vtecr7qe5d1o7c@.corp.supernews.com...
> > > > Does anyone have a non-DTS method for copying large tables between
SQL
> > > > servers without logging? bcp and BULK INSERT appear to be for files
> > only.
> > > > Is there a way to use them with other SQL tables?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Select * into [New Table name]
With the new name being fully qualified
Will
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!