Should data and log files be stored on different drives in
SQL Server 2000. And should the backup files be stored on
yet another drive? I have 2 drives available and have put
the data and log on one and the backup files on the other.
All of these are backed up to tape as well. Thanks.Ideally, your data files should be on one or more drives and log on separate
drives.
It really depends how large your data set is and how important absolute
performance is
to your database. If you have no performance problems with the current setup
and don't
foresee any, then there is no reason to change it.
"Mark" <Mark.Neale@.rspb.org.uk> wrote in message
news:073a01c39f91$5b98ca00$a601280a@.phx.gbl...
> Should data and log files be stored on different drives in
> SQL Server 2000. And should the backup files be stored on
> yet another drive? I have 2 drives available and have put
> the data and log on one and the backup files on the other.
> All of these are backed up to tape as well. Thanks.|||Its better to have log on different drive and db on different if it is
largely growing db
--
Shaju Thomas
e-Cosmos Technologies Ltd.,
Mail: shaju@.e-cosmostech.com
Phone(Off) : 51217038/39 Extn: 132
Mobile: +91 98455 21794
"Mark" <Mark.Neale@.rspb.org.uk> wrote in message
news:073a01c39f91$5b98ca00$a601280a@.phx.gbl...
> Should data and log files be stored on different drives in
> SQL Server 2000. And should the backup files be stored on
> yet another drive? I have 2 drives available and have put
> the data and log on one and the backup files on the other.
> All of these are backed up to tape as well. Thanks.|||On Fri, 31 Oct 2003 01:28:35 -0800, "Mark" <Mark.Neale@.rspb.org.uk>
wrote:
>Should data and log files be stored on different drives in
>SQL Server 2000. And should the backup files be stored on
>yet another drive? I have 2 drives available and have put
>the data and log on one and the backup files on the other.
>All of these are backed up to tape as well. Thanks.
If you had the money to spend, a general baseline suggestion would be
data on one drive, transaction log on another, and backups on yet
another... however, the "yet another" would probably be better if it
was a network share.
Separating the DB file and the transaction log helps with performance,
if you're using SCSI drives.(It might also help with performance if
you are using IDE drives, and each drive is on a separate IDE channel,
however, I can not make any guarantee on this. I *can* guarantee that
it won't do jack for performance if you have two separate IDE drives
on the same IDE channel.)
Separating the backup from both helps with reliability; if your
backups are on your database or transaction log disk, and that disk
dies, you've lost both. Putting the backups on another machine also
means you can restore from backup without having to retrieve a drive
from the machine that crashed.
Of course, you can get even better, more expensive, setups if you want
them... this is just the baseline.
Showing posts with label drives. Show all posts
Showing posts with label drives. Show all posts
Thursday, February 16, 2012
Monday, February 13, 2012
Backup using GZIP
I'm sure I'm not the only one in this situation: out of disk space on
the backup drive, no other drives available, no money for new drives,
no money for software.
I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
with the fiscal year ending and our fuel costs consuming all revenue.
I've seen some hints of using GZIP to compress the backups using named
pipes. I can get the backup to work manually by startiing the restore
going to a command prompt and issuing the gzip command based on this
link:
http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_b
logpart_blogpart=blogview&_c=blogpart#permalink
I can't get the restore to work properly, although I can manually
uncompress the file and the restore works fine (the gunzip statement
can't locate the pipe).
Is anyone using something like this that is an automated job?
Anyone see any problems/issues with using gzip to compress the backup
files?
Thanks,
BillSQL Backup from RedGate is only like $300 per server.
<Disclosure>No connection with them (they did once give me a free pass to VS
Live, but that was after I was a satisfied customer).</Disclosure>
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148653651.258474.50350@.38g2000cwa.googlegroups.com...
> I'm sure I'm not the only one in this situation: out of disk space on
> the backup drive, no other drives available, no money for new drives,
> no money for software.
> I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
> with the fiscal year ending and our fuel costs consuming all revenue.
> I've seen some hints of using GZIP to compress the backups using named
> pipes. I can get the backup to work manually by startiing the restore
> going to a command prompt and issuing the gzip command based on this
> link:
> http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11
_blogpart_blogpart=blogview&_c=blogpart#permalink
> I can't get the restore to work properly, although I can manually
> uncompress the file and the restore works fine (the gunzip statement
> can't locate the pipe).
> Is anyone using something like this that is an automated job?
> Anyone see any problems/issues with using gzip to compress the backup
> files?
> Thanks,
> Bill
>|||You've never worked for the government. When I say there's no money -
there's really no money. I can barely get them to buy paper for the
printer.|||Hmmmm...that is a bummer. Are there any old backup files on the HD that you
can delete? Can you truncate any old static reporting tables or any load
tables that are no longer being used within the database? Any large files
or applications on the server that are no longer needed that can be removed?
Perhaps backup locally then automatically copy the backup files to a
different system with more space? Or backup to a different system
altogether using UNC?
Just a couple of ideas.
HTH
Jerry
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148655353.974446.11690@.i39g2000cwa.googlegroups.com...
> You've never worked for the government. When I say there's no money -
> there's really no money. I can barely get them to buy paper for the
> printer.
>|||No space anywhere - this is just a DB server so there aren't any other
applications on it. The backups have their own drive, it's just not
big enough. We're barely storing a days worth of data. I'm fighting
for space on a SAN, but not making much headway because of the location
of the server. We have some servers opening up in two or three months,
but I need to do something within the next couple of days.
the backup drive, no other drives available, no money for new drives,
no money for software.
I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
with the fiscal year ending and our fuel costs consuming all revenue.
I've seen some hints of using GZIP to compress the backups using named
pipes. I can get the backup to work manually by startiing the restore
going to a command prompt and issuing the gzip command based on this
link:
http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_b
logpart_blogpart=blogview&_c=blogpart#permalink
I can't get the restore to work properly, although I can manually
uncompress the file and the restore works fine (the gunzip statement
can't locate the pipe).
Is anyone using something like this that is an automated job?
Anyone see any problems/issues with using gzip to compress the backup
files?
Thanks,
BillSQL Backup from RedGate is only like $300 per server.
<Disclosure>No connection with them (they did once give me a free pass to VS
Live, but that was after I was a satisfied customer).</Disclosure>
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148653651.258474.50350@.38g2000cwa.googlegroups.com...
> I'm sure I'm not the only one in this situation: out of disk space on
> the backup drive, no other drives available, no money for new drives,
> no money for software.
> I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
> with the fiscal year ending and our fuel costs consuming all revenue.
> I've seen some hints of using GZIP to compress the backups using named
> pipes. I can get the backup to work manually by startiing the restore
> going to a command prompt and issuing the gzip command based on this
> link:
> http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11
_blogpart_blogpart=blogview&_c=blogpart#permalink
> I can't get the restore to work properly, although I can manually
> uncompress the file and the restore works fine (the gunzip statement
> can't locate the pipe).
> Is anyone using something like this that is an automated job?
> Anyone see any problems/issues with using gzip to compress the backup
> files?
> Thanks,
> Bill
>|||You've never worked for the government. When I say there's no money -
there's really no money. I can barely get them to buy paper for the
printer.|||Hmmmm...that is a bummer. Are there any old backup files on the HD that you
can delete? Can you truncate any old static reporting tables or any load
tables that are no longer being used within the database? Any large files
or applications on the server that are no longer needed that can be removed?
Perhaps backup locally then automatically copy the backup files to a
different system with more space? Or backup to a different system
altogether using UNC?
Just a couple of ideas.
HTH
Jerry
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148655353.974446.11690@.i39g2000cwa.googlegroups.com...
> You've never worked for the government. When I say there's no money -
> there's really no money. I can barely get them to buy paper for the
> printer.
>|||No space anywhere - this is just a DB server so there aren't any other
applications on it. The backups have their own drive, it's just not
big enough. We're barely storing a days worth of data. I'm fighting
for space on a SAN, but not making much headway because of the location
of the server. We have some servers opening up in two or three months,
but I need to do something within the next couple of days.
Backup using GZIP
I'm sure I'm not the only one in this situation: out of disk space on
the backup drive, no other drives available, no money for new drives,
no money for software.
I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
with the fiscal year ending and our fuel costs consuming all revenue.
I've seen some hints of using GZIP to compress the backups using named
pipes. I can get the backup to work manually by startiing the restore
going to a command prompt and issuing the gzip command based on this
link:
http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_blogpart_blogpart=blogview&_c=blogpart#permalink
I can't get the restore to work properly, although I can manually
uncompress the file and the restore works fine (the gunzip statement
can't locate the pipe).
Is anyone using something like this that is an automated job?
Anyone see any problems/issues with using gzip to compress the backup
files?
Thanks,
BillSQL Backup from RedGate is only like $300 per server.
<Disclosure>No connection with them (they did once give me a free pass to VS
Live, but that was after I was a satisfied customer).</Disclosure>
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148653651.258474.50350@.38g2000cwa.googlegroups.com...
> I'm sure I'm not the only one in this situation: out of disk space on
> the backup drive, no other drives available, no money for new drives,
> no money for software.
> I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
> with the fiscal year ending and our fuel costs consuming all revenue.
> I've seen some hints of using GZIP to compress the backups using named
> pipes. I can get the backup to work manually by startiing the restore
> going to a command prompt and issuing the gzip command based on this
> link:
> http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_blogpart_blogpart=blogview&_c=blogpart#permalink
> I can't get the restore to work properly, although I can manually
> uncompress the file and the restore works fine (the gunzip statement
> can't locate the pipe).
> Is anyone using something like this that is an automated job?
> Anyone see any problems/issues with using gzip to compress the backup
> files?
> Thanks,
> Bill
>|||You've never worked for the government. When I say there's no money -
there's really no money. I can barely get them to buy paper for the
printer.|||Hmmmm...that is a bummer. Are there any old backup files on the HD that you
can delete? Can you truncate any old static reporting tables or any load
tables that are no longer being used within the database? Any large files
or applications on the server that are no longer needed that can be removed?
Perhaps backup locally then automatically copy the backup files to a
different system with more space? Or backup to a different system
altogether using UNC?
Just a couple of ideas.
HTH
Jerry
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148655353.974446.11690@.i39g2000cwa.googlegroups.com...
> You've never worked for the government. When I say there's no money -
> there's really no money. I can barely get them to buy paper for the
> printer.
>|||No space anywhere - this is just a DB server so there aren't any other
applications on it. The backups have their own drive, it's just not
big enough. We're barely storing a days worth of data. I'm fighting
for space on a SAN, but not making much headway because of the location
of the server. We have some servers opening up in two or three months,
but I need to do something within the next couple of days.
the backup drive, no other drives available, no money for new drives,
no money for software.
I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
with the fiscal year ending and our fuel costs consuming all revenue.
I've seen some hints of using GZIP to compress the backups using named
pipes. I can get the backup to work manually by startiing the restore
going to a command prompt and issuing the gzip command based on this
link:
http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_blogpart_blogpart=blogview&_c=blogpart#permalink
I can't get the restore to work properly, although I can manually
uncompress the file and the restore works fine (the gunzip statement
can't locate the pipe).
Is anyone using something like this that is an automated job?
Anyone see any problems/issues with using gzip to compress the backup
files?
Thanks,
BillSQL Backup from RedGate is only like $300 per server.
<Disclosure>No connection with them (they did once give me a free pass to VS
Live, but that was after I was a satisfied customer).</Disclosure>
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148653651.258474.50350@.38g2000cwa.googlegroups.com...
> I'm sure I'm not the only one in this situation: out of disk space on
> the backup drive, no other drives available, no money for new drives,
> no money for software.
> I'd really like to use LiteSpeed or SQLZIP, but no $$$ in the budget
> with the fiscal year ending and our fuel costs consuming all revenue.
> I've seen some hints of using GZIP to compress the backups using named
> pipes. I can get the backup to work manually by startiing the restore
> going to a command prompt and issuing the gzip command based on this
> link:
> http://spaces.msn.com/jcarlossaez/blog/cns!B3378F057444B65C!107.entry?_c11_blogpart_blogpart=blogview&_c=blogpart#permalink
> I can't get the restore to work properly, although I can manually
> uncompress the file and the restore works fine (the gunzip statement
> can't locate the pipe).
> Is anyone using something like this that is an automated job?
> Anyone see any problems/issues with using gzip to compress the backup
> files?
> Thanks,
> Bill
>|||You've never worked for the government. When I say there's no money -
there's really no money. I can barely get them to buy paper for the
printer.|||Hmmmm...that is a bummer. Are there any old backup files on the HD that you
can delete? Can you truncate any old static reporting tables or any load
tables that are no longer being used within the database? Any large files
or applications on the server that are no longer needed that can be removed?
Perhaps backup locally then automatically copy the backup files to a
different system with more space? Or backup to a different system
altogether using UNC?
Just a couple of ideas.
HTH
Jerry
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148655353.974446.11690@.i39g2000cwa.googlegroups.com...
> You've never worked for the government. When I say there's no money -
> there's really no money. I can barely get them to buy paper for the
> printer.
>|||No space anywhere - this is just a DB server so there aren't any other
applications on it. The backups have their own drive, it's just not
big enough. We're barely storing a days worth of data. I'm fighting
for space on a SAN, but not making much headway because of the location
of the server. We have some servers opening up in two or three months,
but I need to do something within the next couple of days.
Friday, February 10, 2012
Backup to Network Drive
I need to be able to backup my databases through EM to a network drive. The problem is EM won't recognize any of the network drives. This worked for me in the past but I am now running sp3a and for some reason cannot backup to my remote servers anymore.
Does anyone know how to fix this issue?You should use a UNC name instead of a drive letter... any way..
just type in the UNC name..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kelly" <mavericc@.wepsoft.com> wrote in message
news:DCE35BB4-9F00-483A-ADB1-97F38DDB3075@.microsoft.com...
> I need to be able to backup my databases through EM to a network drive.
The problem is EM won't recognize any of the network drives. This worked
for me in the past but I am now running sp3a and for some reason cannot
backup to my remote servers anymore.
> Does anyone know how to fix this issue?|||Just for the record, trace flag 1807 is not needed to backup to a network
share. The flag is intended to allow SQL Server to use network attached
storage devices for database files as described in MSKB 304261
<http://support.microsoft.com/default.aspx?scid=kb;en-us;304261&Product=sql>
.
As Wayne said earlier in this thread, UNCs can be used. If this isn't
working, the likely cause us that the SQL Server service account lacks
permissions to the share. Note that this needs to be a Windows account
rather than Local System.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ted" <anonymous@.discussions.microsoft.com> wrote in message
news:179E57A3-786D-4CF6-B4D0-8DB9D5C987B6@.microsoft.com...
> If the trace didn't work, and it must be set, then it's permission of the
SQLServer and Agent services. I have a domain account I use and it has the
appropriate permission on the network. I use that same account for mail
notification as well. So:
> 1.) Verify the user starting the services has permission to the network
drive.
> 2.) Set the trace flag on
> 3.) Try it. If it works, you don't need to worry about the reboot. It
knows it wrote to the network once and will do it again. (I've never had a
problem).
> Ted-|||Ah yes. I stand corrected. The trace is for NAS. Thanks for the correction Dan.
Does anyone know how to fix this issue?You should use a UNC name instead of a drive letter... any way..
just type in the UNC name..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kelly" <mavericc@.wepsoft.com> wrote in message
news:DCE35BB4-9F00-483A-ADB1-97F38DDB3075@.microsoft.com...
> I need to be able to backup my databases through EM to a network drive.
The problem is EM won't recognize any of the network drives. This worked
for me in the past but I am now running sp3a and for some reason cannot
backup to my remote servers anymore.
> Does anyone know how to fix this issue?|||Just for the record, trace flag 1807 is not needed to backup to a network
share. The flag is intended to allow SQL Server to use network attached
storage devices for database files as described in MSKB 304261
<http://support.microsoft.com/default.aspx?scid=kb;en-us;304261&Product=sql>
.
As Wayne said earlier in this thread, UNCs can be used. If this isn't
working, the likely cause us that the SQL Server service account lacks
permissions to the share. Note that this needs to be a Windows account
rather than Local System.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ted" <anonymous@.discussions.microsoft.com> wrote in message
news:179E57A3-786D-4CF6-B4D0-8DB9D5C987B6@.microsoft.com...
> If the trace didn't work, and it must be set, then it's permission of the
SQLServer and Agent services. I have a domain account I use and it has the
appropriate permission on the network. I use that same account for mail
notification as well. So:
> 1.) Verify the user starting the services has permission to the network
drive.
> 2.) Set the trace flag on
> 3.) Try it. If it works, you don't need to worry about the reboot. It
knows it wrote to the network once and will do it again. (I've never had a
problem).
> Ted-|||Ah yes. I stand corrected. The trace is for NAS. Thanks for the correction Dan.
Backup to Network Drive
I need to be able to backup my databases through EM to a network drive. The problem is EM won't recognize any of the network drives. This worked for me in the past but I am now running sp3a and for some reason cannot backup to my remote servers anymor
e.
Does anyone know how to fix this issue?
You should use a UNC name instead of a drive letter... any way..
just type in the UNC name..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kelly" <mavericc@.wepsoft.com> wrote in message
news:DCE35BB4-9F00-483A-ADB1-97F38DDB3075@.microsoft.com...
> I need to be able to backup my databases through EM to a network drive.
The problem is EM won't recognize any of the network drives. This worked
for me in the past but I am now running sp3a and for some reason cannot
backup to my remote servers anymore.
> Does anyone know how to fix this issue?
|||From Query Analyzer:
dbcc traceon (1807)
Then use should be able to use the UNC path.
|||Does the traceflag setting only last until the computer is rebooted
though?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Even after putting the traceflag in it still woulnd't work. When I put
the UNC path in it says unable to verify the existance of backup file
location.
I tell it to use the directory anyway then I get an error cannot open
backup device. Device Error or device offline.
I don't see why it won't work.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||If the trace didn't work, and it must be set, then it's permission of the SQLServer and Agent services. I have a domain account I use and it has the appropriate permission on the network. I use that same account for mail notification as well. So:
1.) Verify the user starting the services has permission to the network drive.
2.) Set the trace flag on
3.) Try it. If it works, you don't need to worry about the reboot. It knows it wrote to the network once and will do it again. (I've never had a problem).
Ted-
|||Just for the record, trace flag 1807 is not needed to backup to a network
share. The flag is intended to allow SQL Server to use network attached
storage devices for database files as described in MSKB 304261
<http://support.microsoft.com/default...61&Product=sql>
..
As Wayne said earlier in this thread, UNCs can be used. If this isn't
working, the likely cause us that the SQL Server service account lacks
permissions to the share. Note that this needs to be a Windows account
rather than Local System.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ted" <anonymous@.discussions.microsoft.com> wrote in message
news:179E57A3-786D-4CF6-B4D0-8DB9D5C987B6@.microsoft.com...
> If the trace didn't work, and it must be set, then it's permission of the
SQLServer and Agent services. I have a domain account I use and it has the
appropriate permission on the network. I use that same account for mail
notification as well. So:
> 1.) Verify the user starting the services has permission to the network
drive.
> 2.) Set the trace flag on
> 3.) Try it. If it works, you don't need to worry about the reboot. It
knows it wrote to the network once and will do it again. (I've never had a
problem).
> Ted-
|||Ah yes. I stand corrected. The trace is for NAS. Thanks for the correction Dan.
e.
Does anyone know how to fix this issue?
You should use a UNC name instead of a drive letter... any way..
just type in the UNC name..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kelly" <mavericc@.wepsoft.com> wrote in message
news:DCE35BB4-9F00-483A-ADB1-97F38DDB3075@.microsoft.com...
> I need to be able to backup my databases through EM to a network drive.
The problem is EM won't recognize any of the network drives. This worked
for me in the past but I am now running sp3a and for some reason cannot
backup to my remote servers anymore.
> Does anyone know how to fix this issue?
|||From Query Analyzer:
dbcc traceon (1807)
Then use should be able to use the UNC path.
|||Does the traceflag setting only last until the computer is rebooted
though?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Even after putting the traceflag in it still woulnd't work. When I put
the UNC path in it says unable to verify the existance of backup file
location.
I tell it to use the directory anyway then I get an error cannot open
backup device. Device Error or device offline.
I don't see why it won't work.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||If the trace didn't work, and it must be set, then it's permission of the SQLServer and Agent services. I have a domain account I use and it has the appropriate permission on the network. I use that same account for mail notification as well. So:
1.) Verify the user starting the services has permission to the network drive.
2.) Set the trace flag on
3.) Try it. If it works, you don't need to worry about the reboot. It knows it wrote to the network once and will do it again. (I've never had a problem).
Ted-
|||Just for the record, trace flag 1807 is not needed to backup to a network
share. The flag is intended to allow SQL Server to use network attached
storage devices for database files as described in MSKB 304261
<http://support.microsoft.com/default...61&Product=sql>
..
As Wayne said earlier in this thread, UNCs can be used. If this isn't
working, the likely cause us that the SQL Server service account lacks
permissions to the share. Note that this needs to be a Windows account
rather than Local System.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ted" <anonymous@.discussions.microsoft.com> wrote in message
news:179E57A3-786D-4CF6-B4D0-8DB9D5C987B6@.microsoft.com...
> If the trace didn't work, and it must be set, then it's permission of the
SQLServer and Agent services. I have a domain account I use and it has the
appropriate permission on the network. I use that same account for mail
notification as well. So:
> 1.) Verify the user starting the services has permission to the network
drive.
> 2.) Set the trace flag on
> 3.) Try it. If it works, you don't need to worry about the reboot. It
knows it wrote to the network once and will do it again. (I've never had a
problem).
> Ted-
|||Ah yes. I stand corrected. The trace is for NAS. Thanks for the correction Dan.
Backup to Network Drive
I need to be able to backup my databases through EM to a network drive. Th
e problem is EM won't recognize any of the network drives. This worked for
me in the past but I am now running sp3a and for some reason cannot backup
to my remote servers anymor
e.
Does anyone know how to fix this issue?You should use a UNC name instead of a drive letter... any way..
just type in the UNC name..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kelly" <mavericc@.wepsoft.com> wrote in message
news:DCE35BB4-9F00-483A-ADB1-97F38DDB3075@.microsoft.com...
> I need to be able to backup my databases through EM to a network drive.
The problem is EM won't recognize any of the network drives. This worked
for me in the past but I am now running sp3a and for some reason cannot
backup to my remote servers anymore.
> Does anyone know how to fix this issue?|||From Query Analyzer:
dbcc traceon (1807)
Then use should be able to use the UNC path.|||Does the traceflag setting only last until the computer is rebooted
though?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Even after putting the traceflag in it still woulnd't work. When I put
the UNC path in it says unable to verify the existance of backup file
location.
I tell it to use the directory anyway then I get an error cannot open
backup device. Device Error or device offline.
I don't see why it won't work.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||If the trace didn't work, and it must be set, then it's permission of the SQ
LServer and Agent services. I have a domain account I use and it has the ap
propriate permission on the network. I use that same account for mail notif
ication as well. So:
1.) Verify the user starting the services has permission to the network driv
e.
2.) Set the trace flag on
3.) Try it. If it works, you don't need to worry about the reboot. It kno
ws it wrote to the network once and will do it again. (I've never had a prob
lem).
Ted-|||Just for the record, trace flag 1807 is not needed to backup to a network
share. The flag is intended to allow SQL Server to use network attached
storage devices for database files as described in MSKB 304261
<http://support.microsoft.com/defaul...261&Product=sql>
.
As Wayne said earlier in this thread, UNCs can be used. If this isn't
working, the likely cause us that the SQL Server service account lacks
permissions to the share. Note that this needs to be a Windows account
rather than Local System.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ted" <anonymous@.discussions.microsoft.com> wrote in message
news:179E57A3-786D-4CF6-B4D0-8DB9D5C987B6@.microsoft.com...
> If the trace didn't work, and it must be set, then it's permission of the
SQLServer and Agent services. I have a domain account I use and it has the
appropriate permission on the network. I use that same account for mail
notification as well. So:
> 1.) Verify the user starting the services has permission to the network
drive.
> 2.) Set the trace flag on
> 3.) Try it. If it works, you don't need to worry about the reboot. It
knows it wrote to the network once and will do it again. (I've never had a
problem).
> Ted-|||Ah yes. I stand corrected. The trace is for NAS. Thanks for the correctio
n Dan.
e problem is EM won't recognize any of the network drives. This worked for
me in the past but I am now running sp3a and for some reason cannot backup
to my remote servers anymor
e.
Does anyone know how to fix this issue?You should use a UNC name instead of a drive letter... any way..
just type in the UNC name..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kelly" <mavericc@.wepsoft.com> wrote in message
news:DCE35BB4-9F00-483A-ADB1-97F38DDB3075@.microsoft.com...
> I need to be able to backup my databases through EM to a network drive.
The problem is EM won't recognize any of the network drives. This worked
for me in the past but I am now running sp3a and for some reason cannot
backup to my remote servers anymore.
> Does anyone know how to fix this issue?|||From Query Analyzer:
dbcc traceon (1807)
Then use should be able to use the UNC path.|||Does the traceflag setting only last until the computer is rebooted
though?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Even after putting the traceflag in it still woulnd't work. When I put
the UNC path in it says unable to verify the existance of backup file
location.
I tell it to use the directory anyway then I get an error cannot open
backup device. Device Error or device offline.
I don't see why it won't work.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||If the trace didn't work, and it must be set, then it's permission of the SQ
LServer and Agent services. I have a domain account I use and it has the ap
propriate permission on the network. I use that same account for mail notif
ication as well. So:
1.) Verify the user starting the services has permission to the network driv
e.
2.) Set the trace flag on
3.) Try it. If it works, you don't need to worry about the reboot. It kno
ws it wrote to the network once and will do it again. (I've never had a prob
lem).
Ted-|||Just for the record, trace flag 1807 is not needed to backup to a network
share. The flag is intended to allow SQL Server to use network attached
storage devices for database files as described in MSKB 304261
<http://support.microsoft.com/defaul...261&Product=sql>
.
As Wayne said earlier in this thread, UNCs can be used. If this isn't
working, the likely cause us that the SQL Server service account lacks
permissions to the share. Note that this needs to be a Windows account
rather than Local System.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ted" <anonymous@.discussions.microsoft.com> wrote in message
news:179E57A3-786D-4CF6-B4D0-8DB9D5C987B6@.microsoft.com...
> If the trace didn't work, and it must be set, then it's permission of the
SQLServer and Agent services. I have a domain account I use and it has the
appropriate permission on the network. I use that same account for mail
notification as well. So:
> 1.) Verify the user starting the services has permission to the network
drive.
> 2.) Set the trace flag on
> 3.) Try it. If it works, you don't need to worry about the reboot. It
knows it wrote to the network once and will do it again. (I've never had a
problem).
> Ted-|||Ah yes. I stand corrected. The trace is for NAS. Thanks for the correctio
n Dan.
Subscribe to:
Posts (Atom)