Every Satuday evening my t-log balloons and consume my available drive
space. What can I do to monitor the og so i can try to see what is
causing this to keep happening.
I have interrogated the application support ema to see if they do any
large amount of work that would cause the problem but only "not me"
answers
any help/suggestions are greatly appreciated.Can you set up a trace to see who is doing what? My guess is something like
index rebuilds.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
> Every Satuday evening my t-log balloons and consume my available drive
> space. What can I do to monitor the og so i can try to see what is
> causing this to keep happening.
> I have interrogated the application support ema to see if they do any
> large amount of work that would cause the problem but only "not me"
> answers
> any help/suggestions are greatly appreciated.|||On Jan 20, 7:41=A0pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Can you set up a trace to see who is doing what? My guess is something lik=e
> index rebuilds.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelan=
ey.com
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
>
> > Every Satuday evening my t-log balloons and consume my available drive
> > space. What can I do to monitor the og so i can try to see what is
> > causing this to keep happening.
> > I have interrogated the application support ema to see if they do any
> > large amount of work =A0that would cause the problem but only "not me"
> > answers
> > any help/suggestions are greatly appreciated.- Hide quoted text -
> - Show quoted text -
I do perform maintenance at 1am on Sunday mornings which does an index
reorg but not a rebuild. would performing maintenance more often help
or will that balloon the logs suring the production week which I don't
want to happen.
Any suggestions are welcomed.|||Yes, re-organising OR rebuilding databases does cause massive transaction
logging.
Instead of actually rebuilding or re-organising your indexes, you might be
better off just rebuilding index statistics (which doesn't generate such
massive log entries). You should also look into tuing the queries which are
scanning your tables - these queries are the ones benefiting from the
re-orgs / rebuilds but they'd probably run much better if they just had a
good index.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:f69cf48c-bdeb-4026-9bb0-bd01ae9627b5@.c23g2000hsa.googlegroups.com...
On Jan 20, 7:41 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Can you set up a trace to see who is doing what? My guess is something
> like
> index rebuilds.
> --
> HTH
> Kalen Delaney, SQL Server
> MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
>
> > Every Satuday evening my t-log balloons and consume my available drive
> > space. What can I do to monitor the og so i can try to see what is
> > causing this to keep happening.
> > I have interrogated the application support ema to see if they do any
> > large amount of work that would cause the problem but only "not me"
> > answers
> > any help/suggestions are greatly appreciated.- Hide quoted text -
> - Show quoted text -
I do perform maintenance at 1am on Sunday mornings which does an index
reorg but not a rebuild. would performing maintenance more often help
or will that balloon the logs suring the production week which I don't
want to happen.
Any suggestions are welcomed.|||Also use the script from BOL that checks fragmentation first and only redoes
stuff that needs it. Also, if you avoid clustered indexes (unless severely
fragmented) that could keep logging down too.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OBPTWM9WIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Yes, re-organising OR rebuilding databases does cause massive transaction
> logging.
> Instead of actually rebuilding or re-organising your indexes, you might be
> better off just rebuilding index statistics (which doesn't generate such
> massive log entries). You should also look into tuing the queries which
> are scanning your tables - these queries are the ones benefiting from the
> re-orgs / rebuilds but they'd probably run much better if they just had a
> good index.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "NC3" <ncoleman3@.yahoo.com> wrote in message
> news:f69cf48c-bdeb-4026-9bb0-bd01ae9627b5@.c23g2000hsa.googlegroups.com...
> On Jan 20, 7:41 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>> Can you set up a trace to see who is doing what? My guess is something
>> like
>> index rebuilds.
>> --
>> HTH
>> Kalen Delaney, SQL Server
>> MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
>> "NC3" <ncolem...@.yahoo.com> wrote in message
>> news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
>>
>> > Every Satuday evening my t-log balloons and consume my available drive
>> > space. What can I do to monitor the og so i can try to see what is
>> > causing this to keep happening.
>> > I have interrogated the application support ema to see if they do any
>> > large amount of work that would cause the problem but only "not me"
>> > answers
>> > any help/suggestions are greatly appreciated.- Hide quoted text -
>> - Show quoted text -
> I do perform maintenance at 1am on Sunday mornings which does an index
> reorg but not a rebuild. would performing maintenance more often help
> or will that balloon the logs suring the production week which I don't
> want to happen.
> Any suggestions are welcomed.
>|||On Jan 20, 9:14=A0pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> Yes, re-organising OR rebuilding databases does cause massive transaction
> logging.
> Instead of actually rebuilding or re-organising your indexes, you might be=
> better off just rebuilding index statistics (which doesn't generate such
> massive log entries). You should also look into tuing the queries which ar=e
> scanning your tables - these queries are the ones benefiting from the
> re-orgs / rebuilds but they'd probably run much better if they just had a
> good index.
> Regards,
> Greg Linwood
> SQL Server MVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performancehttp://www.SQLBenchmarkPro.com
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:f69cf48c-bdeb-4026-9bb0-bd01ae9627b5@.c23g2000hsa.googlegroups.com...
> On Jan 20, 7:41 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>
>
> > Can you set up a trace to see who is doing what? My guess is something
> > like
> > index rebuilds.
> > --
> > HTH
> > Kalen Delaney, SQL Server
> > MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
> > "NC3" <ncolem...@.yahoo.com> wrote in message
> >news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...=
> > > Every Satuday evening my t-log balloons and consume my available drive=
> > > space. What can I do to monitor the og so i can try to see what is
> > > causing this to keep happening.
> > > I have interrogated the application support ema to see if they do any
> > > large amount of work that would cause the problem but only "not me"
> > > answers
> > > any help/suggestions are greatly appreciated.- Hide quoted text -
> > - Show quoted text -
> I do perform maintenance at 1am on Sunday mornings which does an index
> reorg but not a rebuild. would performing maintenance more often help
> or will that balloon the logs suring the production week which I don't
> want to happen.
> Any suggestions are welcomed.- Hide quoted text -
> - Show quoted text -
I appreciate your comments and will look into the suggestions that I
received. Tuning the queries are a problem since they belong to a
third party application. However I will learn how to use the SQL
traces suggested by Kalen Delaney, and reveiw BOL for fragmentation
queries as well as the rebuild stats.
New things to learn are always exciting (and frustrating)!
Thanks again.sql
Showing posts with label drive. Show all posts
Showing posts with label drive. Show all posts
Tuesday, March 20, 2012
Thursday, March 8, 2012
Backups/Restores of remote hosted SQL Server to Local?
Is there any way to backup a remote SQL Server that is on a hosted account to a local drive? We have a web hosted account that has a SQL Server that we do not have full admin rights on, just dbo access to the data and structure. We would like to be able to do backups and restores to our local development server if possible. Perhaps vis DTS or some similar means? WE cannot use the normal backup/restore as we do not have right to these fucntions nor can we access the servers local drives directly.You could certinly use DTS to transfer all objects from one server to another, presuming that the host allows SQL Server access through their firewall.|||
Yes the yallow that and support DTS packages.
Wha tis the best way to configure these to allow transferrring all data and objects? What needs to be setup first in terms of an empty database, user accounts, etc for this?
Friday, February 24, 2012
Backup/Restore SQL DB - webhost4life
I'm working with someone right now who wants to beable to click on a link and instantly beable to restore a SQL database from his hard drive or backup a SQL database to his hard drive.
I talked to webhost4life about this (where my MSSQL databases are hosted) and this is their response:
"The only way you can backup or restore a database yourself is by going to hosting control panel. You can have your clients contact us with the hosting account member ID and the database login information so we can do a manual restore or backup for them. "
This is the only way? This is not sufficient for my client that I am working with.
I've searched the forum for this and have received mixed responses. Some say to research SQLDMO and i've even seen this for restore, which I couldn't get to work:
strSQL = "RESTORE DATABASE [WebReqs] FROM DISK = N'C:\Inetpub\wwwroot\Administration\Database\WebReqsVanilla.bak' WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , REPLACE , MOVE N'WebReqs_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\WebReqs_data.mdf', MOVE N'WebReqs_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\WebReqs_log.ldf'"
I can't seem to get anything to work and webhost4life says it's not possible. Is there any other way??
TIAIs DTS (SQL Server Tools) working with webhost4life?|||I've successfully backed up my webhost4life database using DTS/Enterprise Manager. I don't actually do a backup though, I just pull down all the objects (do an object transfer). This will back up all the tables, sprocs, views and data, which for most web database scenerios is sufficient.
A normal Backup and Restore require sysadmin level permissions, which they are certainly not going to give you on a shared database server. I think there is a way to give that level of permission at the database level, but even if they did you would not be able to tell the remote server where you wanted the backup file (the location). In other words, it would not backup to your local computer where you could get at it.
cs|||The Intro to Webmatrix book has some scripts included for uploading/downloading table schema and data from MSDE/SQL.
They must be here on the site somewhere as well.
In the book, there's a disclaimer about security, you need remove the pages from the host server after you use them.
Thursday, February 16, 2012
Backup, data and log files
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.
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.
Monday, February 13, 2012
Backup w/RETAINDAYS just keeps growin', and growin'
Hi all;
I need to make daily backups to a computer in another domain, where I can't map a drive, so I use the TSQL to create a dump device, which works fine:
EXEC sp_addumpdevice 'disk', 'MyBackupDevice', [ipaddress & directory/file]
Then I schedule a server job which faithfully backs up my database, with one hitch - it never deletes days older than 5 as per the code - it just keeps growing. The command couldn't be much simpler:
BACKUP DATABASE ProductionETC to MyBackupDevice WITH RETAINDAYS = 5
For now, I just create a new backup device every now and then and go back and forth, deleting the oldest one when I create a new one. But it's annoying! Any ideas what I'm doing wrong?
Thanks,
L. Rap
RETAINDAYS only prevents SQL Server from overwriting the file unless the specified number of days have elapsed. It doesn't mean that SQL Server will automatically delete the file or erase the media after the specified number of days. So you will have to delete the files using your own scheduled job or use a database maintainence task (you can create from Enterprise Manager or SSMS).|||Thanks Umachandar, that's very helpful. I misunderstood the instructions.
Would you happen to know if it is possible to delete the earliest backup from the set using T-SQL or some other preset command, or if I need to schedule an OS job to simply delete the file and start over?
Regards,
L. Rap
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.
Sunday, February 12, 2012
Backup to USB drive failing
We have a SQL Server (2000, Win2K) that used to backup to a tape drive. The
tape drive was connected through our Domain Controller. DC crashed. As
emergency solution, a 256GB external USB drive was attached to the SQL
Server, and appears in structure as Drive F:\.
I can manually backup my TLog to the USB drive, but scheduled TLog backups
don't even launch, let alone fail.
I can launch a manual Full Database backup, but it fails, error 112, not
enough disk space. My db is only 50GB, and I have 230GB free on my external
USB drive.
Any ideas why my scheduled (Maintenance Plan) TLog backups don't even
launch, and why the Full backup says I am out of space?
We are using the Administrator login, with full permissions.
Thanks!Make sure your drive is formatted using NTFS, not FAT32.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Duane" <Duane@.discussions.microsoft.com> wrote in message
news:346BBF00-0EFA-4929-A452-DACF33FDE7C0@.microsoft.com...
> We have a SQL Server (2000, Win2K) that used to backup to a tape drive.
> The
> tape drive was connected through our Domain Controller. DC crashed. As
> emergency solution, a 256GB external USB drive was attached to the SQL
> Server, and appears in structure as Drive F:\.
> I can manually backup my TLog to the USB drive, but scheduled TLog backups
> don't even launch, let alone fail.
> I can launch a manual Full Database backup, but it fails, error 112, not
> enough disk space. My db is only 50GB, and I have 230GB free on my
> external
> USB drive.
> Any ideas why my scheduled (Maintenance Plan) TLog backups don't even
> launch, and why the Full backup says I am out of space?
> We are using the Administrator login, with full permissions.
> Thanks!|||Geoff, thanks. It is FAT32, and we will reformat Thursday and try again.
HOWEVER, I still have the issue of why my scheduled backups don't even
launch. This shouldn't have anything to do with formatting, as I can
manually backup a TLog, but it doesn't even launch when scheduled.
Suggestions?
Thanks,
Duane
"Geoff N. Hiten" wrote:
> Make sure your drive is formatted using NTFS, not FAT32.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Duane" <Duane@.discussions.microsoft.com> wrote in message
> news:346BBF00-0EFA-4929-A452-DACF33FDE7C0@.microsoft.com...
> > We have a SQL Server (2000, Win2K) that used to backup to a tape drive.
> > The
> > tape drive was connected through our Domain Controller. DC crashed. As
> > emergency solution, a 256GB external USB drive was attached to the SQL
> > Server, and appears in structure as Drive F:\.
> >
> > I can manually backup my TLog to the USB drive, but scheduled TLog backups
> > don't even launch, let alone fail.
> >
> > I can launch a manual Full Database backup, but it fails, error 112, not
> > enough disk space. My db is only 50GB, and I have 230GB free on my
> > external
> > USB drive.
> >
> > Any ideas why my scheduled (Maintenance Plan) TLog backups don't even
> > launch, and why the Full backup says I am out of space?
> >
> > We are using the Administrator login, with full permissions.
> >
> > Thanks!
>
>|||"Duane" <Duane@.discussions.microsoft.com> wrote in message
news:019B2438-DC3D-41D9-992F-C42B2CA300A4@.microsoft.com...
> Geoff, thanks. It is FAT32, and we will reformat Thursday and try again.
> HOWEVER, I still have the issue of why my scheduled backups don't even
> launch. This shouldn't have anything to do with formatting, as I can
> manually backup a TLog, but it doesn't even launch when scheduled.
GENERALLY I've seen that when folks backup to a shared drive and don't have
permissions right.
But if this is a local drive, that shouldn't be an issue... hmm, unless
somehow the user account SQL Admin is running under hasn't picked up the new
drive letter?
Might want to try cycling SQL Admin.
> Suggestions?
> Thanks,
> Duane
> "Geoff N. Hiten" wrote:
> > Make sure your drive is formatted using NTFS, not FAT32.
> >
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> >
> >
> > "Duane" <Duane@.discussions.microsoft.com> wrote in message
> > news:346BBF00-0EFA-4929-A452-DACF33FDE7C0@.microsoft.com...
> > > We have a SQL Server (2000, Win2K) that used to backup to a tape
drive.
> > > The
> > > tape drive was connected through our Domain Controller. DC crashed.
As
> > > emergency solution, a 256GB external USB drive was attached to the SQL
> > > Server, and appears in structure as Drive F:\.
> > >
> > > I can manually backup my TLog to the USB drive, but scheduled TLog
backups
> > > don't even launch, let alone fail.
> > >
> > > I can launch a manual Full Database backup, but it fails, error 112,
not
> > > enough disk space. My db is only 50GB, and I have 230GB free on my
> > > external
> > > USB drive.
> > >
> > > Any ideas why my scheduled (Maintenance Plan) TLog backups don't even
> > > launch, and why the Full backup says I am out of space?
> > >
> > > We are using the Administrator login, with full permissions.
> > >
> > > Thanks!
> >
> >
> >
tape drive was connected through our Domain Controller. DC crashed. As
emergency solution, a 256GB external USB drive was attached to the SQL
Server, and appears in structure as Drive F:\.
I can manually backup my TLog to the USB drive, but scheduled TLog backups
don't even launch, let alone fail.
I can launch a manual Full Database backup, but it fails, error 112, not
enough disk space. My db is only 50GB, and I have 230GB free on my external
USB drive.
Any ideas why my scheduled (Maintenance Plan) TLog backups don't even
launch, and why the Full backup says I am out of space?
We are using the Administrator login, with full permissions.
Thanks!Make sure your drive is formatted using NTFS, not FAT32.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Duane" <Duane@.discussions.microsoft.com> wrote in message
news:346BBF00-0EFA-4929-A452-DACF33FDE7C0@.microsoft.com...
> We have a SQL Server (2000, Win2K) that used to backup to a tape drive.
> The
> tape drive was connected through our Domain Controller. DC crashed. As
> emergency solution, a 256GB external USB drive was attached to the SQL
> Server, and appears in structure as Drive F:\.
> I can manually backup my TLog to the USB drive, but scheduled TLog backups
> don't even launch, let alone fail.
> I can launch a manual Full Database backup, but it fails, error 112, not
> enough disk space. My db is only 50GB, and I have 230GB free on my
> external
> USB drive.
> Any ideas why my scheduled (Maintenance Plan) TLog backups don't even
> launch, and why the Full backup says I am out of space?
> We are using the Administrator login, with full permissions.
> Thanks!|||Geoff, thanks. It is FAT32, and we will reformat Thursday and try again.
HOWEVER, I still have the issue of why my scheduled backups don't even
launch. This shouldn't have anything to do with formatting, as I can
manually backup a TLog, but it doesn't even launch when scheduled.
Suggestions?
Thanks,
Duane
"Geoff N. Hiten" wrote:
> Make sure your drive is formatted using NTFS, not FAT32.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Duane" <Duane@.discussions.microsoft.com> wrote in message
> news:346BBF00-0EFA-4929-A452-DACF33FDE7C0@.microsoft.com...
> > We have a SQL Server (2000, Win2K) that used to backup to a tape drive.
> > The
> > tape drive was connected through our Domain Controller. DC crashed. As
> > emergency solution, a 256GB external USB drive was attached to the SQL
> > Server, and appears in structure as Drive F:\.
> >
> > I can manually backup my TLog to the USB drive, but scheduled TLog backups
> > don't even launch, let alone fail.
> >
> > I can launch a manual Full Database backup, but it fails, error 112, not
> > enough disk space. My db is only 50GB, and I have 230GB free on my
> > external
> > USB drive.
> >
> > Any ideas why my scheduled (Maintenance Plan) TLog backups don't even
> > launch, and why the Full backup says I am out of space?
> >
> > We are using the Administrator login, with full permissions.
> >
> > Thanks!
>
>|||"Duane" <Duane@.discussions.microsoft.com> wrote in message
news:019B2438-DC3D-41D9-992F-C42B2CA300A4@.microsoft.com...
> Geoff, thanks. It is FAT32, and we will reformat Thursday and try again.
> HOWEVER, I still have the issue of why my scheduled backups don't even
> launch. This shouldn't have anything to do with formatting, as I can
> manually backup a TLog, but it doesn't even launch when scheduled.
GENERALLY I've seen that when folks backup to a shared drive and don't have
permissions right.
But if this is a local drive, that shouldn't be an issue... hmm, unless
somehow the user account SQL Admin is running under hasn't picked up the new
drive letter?
Might want to try cycling SQL Admin.
> Suggestions?
> Thanks,
> Duane
> "Geoff N. Hiten" wrote:
> > Make sure your drive is formatted using NTFS, not FAT32.
> >
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> >
> >
> > "Duane" <Duane@.discussions.microsoft.com> wrote in message
> > news:346BBF00-0EFA-4929-A452-DACF33FDE7C0@.microsoft.com...
> > > We have a SQL Server (2000, Win2K) that used to backup to a tape
drive.
> > > The
> > > tape drive was connected through our Domain Controller. DC crashed.
As
> > > emergency solution, a 256GB external USB drive was attached to the SQL
> > > Server, and appears in structure as Drive F:\.
> > >
> > > I can manually backup my TLog to the USB drive, but scheduled TLog
backups
> > > don't even launch, let alone fail.
> > >
> > > I can launch a manual Full Database backup, but it fails, error 112,
not
> > > enough disk space. My db is only 50GB, and I have 230GB free on my
> > > external
> > > USB drive.
> > >
> > > Any ideas why my scheduled (Maintenance Plan) TLog backups don't even
> > > launch, and why the Full backup says I am out of space?
> > >
> > > We are using the Administrator login, with full permissions.
> > >
> > > Thanks!
> >
> >
> >
Backup to USB drive failing
We have a SQL Server (2000, Win2K) that used to backup to a tape drive. The
tape drive was connected through our Domain Controller. DC crashed. As
emergency solution, a 256GB external USB drive was attached to the SQL
Server, and appears in structure as Drive F:\.
I can manually backup my TLog to the USB drive, but scheduled TLog backups
don't even launch, let alone fail.
I can launch a manual Full Database backup, but it fails, error 112, not
enough disk space. My db is only 50GB, and I have 230GB free on my external
USB drive.
Any ideas why my scheduled (Maintenance Plan) TLog backups don't even
launch, and why the Full backup says I am out of space?
We are using the Administrator login, with full permissions.
Thanks!Make sure your drive is formatted using NTFS, not FAT32.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Duane" <Duane@.discussions.microsoft.com> wrote in message
news:346BBF00-0EFA-4929-A452-DACF33FDE7C0@.microsoft.com...
> We have a SQL Server (2000, Win2K) that used to backup to a tape drive.
> The
> tape drive was connected through our Domain Controller. DC crashed. As
> emergency solution, a 256GB external USB drive was attached to the SQL
> Server, and appears in structure as Drive F:\.
> I can manually backup my TLog to the USB drive, but scheduled TLog backups
> don't even launch, let alone fail.
> I can launch a manual Full Database backup, but it fails, error 112, not
> enough disk space. My db is only 50GB, and I have 230GB free on my
> external
> USB drive.
> Any ideas why my scheduled (Maintenance Plan) TLog backups don't even
> launch, and why the Full backup says I am out of space?
> We are using the Administrator login, with full permissions.
> Thanks!|||Geoff, thanks. It is FAT32, and we will reformat Thursday and try again.
HOWEVER, I still have the issue of why my scheduled backups don't even
launch. This shouldn't have anything to do with formatting, as I can
manually backup a TLog, but it doesn't even launch when scheduled.
Suggestions?
Thanks,
Duane
"Geoff N. Hiten" wrote:
> Make sure your drive is formatted using NTFS, not FAT32.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Duane" <Duane@.discussions.microsoft.com> wrote in message
> news:346BBF00-0EFA-4929-A452-DACF33FDE7C0@.microsoft.com...
>
>|||"Duane" <Duane@.discussions.microsoft.com> wrote in message
news:019B2438-DC3D-41D9-992F-C42B2CA300A4@.microsoft.com...
> Geoff, thanks. It is FAT32, and we will reformat Thursday and try again.
> HOWEVER, I still have the issue of why my scheduled backups don't even
> launch. This shouldn't have anything to do with formatting, as I can
> manually backup a TLog, but it doesn't even launch when scheduled.
GENERALLY I've seen that when folks backup to a shared drive and don't have
permissions right.
But if this is a local drive, that shouldn't be an issue... hmm, unless
somehow the user account SQL Admin is running under hasn't picked up the new
drive letter?
Might want to try cycling SQL Admin.
[vbcol=seagreen]
> Suggestions?
> Thanks,
> Duane
> "Geoff N. Hiten" wrote:
>
drive.[vbcol=seagreen]
As[vbcol=seagreen]
backups[vbcol=seagreen]
not[vbcol=seagreen]
tape drive was connected through our Domain Controller. DC crashed. As
emergency solution, a 256GB external USB drive was attached to the SQL
Server, and appears in structure as Drive F:\.
I can manually backup my TLog to the USB drive, but scheduled TLog backups
don't even launch, let alone fail.
I can launch a manual Full Database backup, but it fails, error 112, not
enough disk space. My db is only 50GB, and I have 230GB free on my external
USB drive.
Any ideas why my scheduled (Maintenance Plan) TLog backups don't even
launch, and why the Full backup says I am out of space?
We are using the Administrator login, with full permissions.
Thanks!Make sure your drive is formatted using NTFS, not FAT32.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Duane" <Duane@.discussions.microsoft.com> wrote in message
news:346BBF00-0EFA-4929-A452-DACF33FDE7C0@.microsoft.com...
> We have a SQL Server (2000, Win2K) that used to backup to a tape drive.
> The
> tape drive was connected through our Domain Controller. DC crashed. As
> emergency solution, a 256GB external USB drive was attached to the SQL
> Server, and appears in structure as Drive F:\.
> I can manually backup my TLog to the USB drive, but scheduled TLog backups
> don't even launch, let alone fail.
> I can launch a manual Full Database backup, but it fails, error 112, not
> enough disk space. My db is only 50GB, and I have 230GB free on my
> external
> USB drive.
> Any ideas why my scheduled (Maintenance Plan) TLog backups don't even
> launch, and why the Full backup says I am out of space?
> We are using the Administrator login, with full permissions.
> Thanks!|||Geoff, thanks. It is FAT32, and we will reformat Thursday and try again.
HOWEVER, I still have the issue of why my scheduled backups don't even
launch. This shouldn't have anything to do with formatting, as I can
manually backup a TLog, but it doesn't even launch when scheduled.
Suggestions?
Thanks,
Duane
"Geoff N. Hiten" wrote:
> Make sure your drive is formatted using NTFS, not FAT32.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Duane" <Duane@.discussions.microsoft.com> wrote in message
> news:346BBF00-0EFA-4929-A452-DACF33FDE7C0@.microsoft.com...
>
>|||"Duane" <Duane@.discussions.microsoft.com> wrote in message
news:019B2438-DC3D-41D9-992F-C42B2CA300A4@.microsoft.com...
> Geoff, thanks. It is FAT32, and we will reformat Thursday and try again.
> HOWEVER, I still have the issue of why my scheduled backups don't even
> launch. This shouldn't have anything to do with formatting, as I can
> manually backup a TLog, but it doesn't even launch when scheduled.
GENERALLY I've seen that when folks backup to a shared drive and don't have
permissions right.
But if this is a local drive, that shouldn't be an issue... hmm, unless
somehow the user account SQL Admin is running under hasn't picked up the new
drive letter?
Might want to try cycling SQL Admin.
[vbcol=seagreen]
> Suggestions?
> Thanks,
> Duane
> "Geoff N. Hiten" wrote:
>
drive.[vbcol=seagreen]
As[vbcol=seagreen]
backups[vbcol=seagreen]
not[vbcol=seagreen]
Backup to USB 2.0 drive
Any reasons why backing up to a USB 2.0 HD would be bad? It's quick, easy,
and cheap. The main server is running low on storage and want to offload my
online backups ... thought this would be a cheap solution. The only backups
happening during production are log backups which are only at most 2mb.
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
Shaun,
I wouldn't do this in a corporate environment, but I might consider it
at home for pet projects. I always prefer to backup to local disk first,
and then xcopy/robocopy the file elsewhere, or get a tape backup agent
to grab it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Shaun Beane wrote:
> Any reasons why backing up to a USB 2.0 HD would be bad? It's quick, easy,
> and cheap. The main server is running low on storage and want to offload my
> online backups ... thought this would be a cheap solution. The only backups
> happening during production are log backups which are only at most 2mb.
>
|||I agree, but I'm running low on disk space and mr. big boss man doesn't want
to spend the money on an additional drive for the raid. I can get a USB 2.0
drive for about 100 bucks. I would be backing up to the local USB drive
instead of local internal disk. Then I would backup to tape from there.
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eRV7zzMyEHA.4028@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Shaun,
> I wouldn't do this in a corporate environment, but I might consider it at
> home for pet projects. I always prefer to backup to local disk first, and
> then xcopy/robocopy the file elsewhere, or get a tape backup agent to grab
> it.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Shaun Beane wrote:
|||For an additional 99 bucks, you could get MiniSQLBackup which ought to be
able to compress your backups to averagely 25% of their original size, and
then have it copy it to your USB drive upon completion. E.g. of syntax:
BACKUP mydb TO DISK = 'F:\Backups\db.bak' WITH COPYTO = 'U:\Backups\';
Or if you perform the backups manually, you could even use the free edition.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
and cheap. The main server is running low on storage and want to offload my
online backups ... thought this would be a cheap solution. The only backups
happening during production are log backups which are only at most 2mb.
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
Shaun,
I wouldn't do this in a corporate environment, but I might consider it
at home for pet projects. I always prefer to backup to local disk first,
and then xcopy/robocopy the file elsewhere, or get a tape backup agent
to grab it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Shaun Beane wrote:
> Any reasons why backing up to a USB 2.0 HD would be bad? It's quick, easy,
> and cheap. The main server is running low on storage and want to offload my
> online backups ... thought this would be a cheap solution. The only backups
> happening during production are log backups which are only at most 2mb.
>
|||I agree, but I'm running low on disk space and mr. big boss man doesn't want
to spend the money on an additional drive for the raid. I can get a USB 2.0
drive for about 100 bucks. I would be backing up to the local USB drive
instead of local internal disk. Then I would backup to tape from there.
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eRV7zzMyEHA.4028@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Shaun,
> I wouldn't do this in a corporate environment, but I might consider it at
> home for pet projects. I always prefer to backup to local disk first, and
> then xcopy/robocopy the file elsewhere, or get a tape backup agent to grab
> it.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Shaun Beane wrote:
|||For an additional 99 bucks, you could get MiniSQLBackup which ought to be
able to compress your backups to averagely 25% of their original size, and
then have it copy it to your USB drive upon completion. E.g. of syntax:
BACKUP mydb TO DISK = 'F:\Backups\db.bak' WITH COPYTO = 'U:\Backups\';
Or if you perform the backups manually, you could even use the free edition.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
Backup to USB 2.0 drive
Any reasons why backing up to a USB 2.0 HD would be bad? It's quick, easy,
and cheap. The main server is running low on storage and want to offload my
online backups ... thought this would be a cheap solution. The only backups
happening during production are log backups which are only at most 2mb.
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.comShaun,
I wouldn't do this in a corporate environment, but I might consider it
at home for pet projects. I always prefer to backup to local disk first,
and then xcopy/robocopy the file elsewhere, or get a tape backup agent
to grab it.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Shaun Beane wrote:
> Any reasons why backing up to a USB 2.0 HD would be bad? It's quick, easy
,
> and cheap. The main server is running low on storage and want to offload
my
> online backups ... thought this would be a cheap solution. The only backu
ps
> happening during production are log backups which are only at most 2mb.
>|||I agree, but I'm running low on disk space and mr. big boss man doesn't want
to spend the money on an additional drive for the raid. I can get a USB 2.0
drive for about 100 bucks. I would be backing up to the local USB drive
instead of local internal disk. Then I would backup to tape from there.
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eRV7zzMyEHA.4028@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Shaun,
> I wouldn't do this in a corporate environment, but I might consider it at
> home for pet projects. I always prefer to backup to local disk first, and
> then xcopy/robocopy the file elsewhere, or get a tape backup agent to grab
> it.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Shaun Beane wrote:|||For an additional 99 bucks, you could get MiniSQLBackup which ought to be
able to compress your backups to averagely 25% of their original size, and
then have it copy it to your USB drive upon completion. E.g. of syntax:
BACKUP mydb TO DISK = 'F:\Backups\db.bak' WITH COPYTO = 'U:\Backups';
Or if you perform the backups manually, you could even use the free edition.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
and cheap. The main server is running low on storage and want to offload my
online backups ... thought this would be a cheap solution. The only backups
happening during production are log backups which are only at most 2mb.
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.comShaun,
I wouldn't do this in a corporate environment, but I might consider it
at home for pet projects. I always prefer to backup to local disk first,
and then xcopy/robocopy the file elsewhere, or get a tape backup agent
to grab it.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Shaun Beane wrote:
> Any reasons why backing up to a USB 2.0 HD would be bad? It's quick, easy
,
> and cheap. The main server is running low on storage and want to offload
my
> online backups ... thought this would be a cheap solution. The only backu
ps
> happening during production are log backups which are only at most 2mb.
>|||I agree, but I'm running low on disk space and mr. big boss man doesn't want
to spend the money on an additional drive for the raid. I can get a USB 2.0
drive for about 100 bucks. I would be backing up to the local USB drive
instead of local internal disk. Then I would backup to tape from there.
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eRV7zzMyEHA.4028@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Shaun,
> I wouldn't do this in a corporate environment, but I might consider it at
> home for pet projects. I always prefer to backup to local disk first, and
> then xcopy/robocopy the file elsewhere, or get a tape backup agent to grab
> it.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Shaun Beane wrote:|||For an additional 99 bucks, you could get MiniSQLBackup which ought to be
able to compress your backups to averagely 25% of their original size, and
then have it copy it to your USB drive upon completion. E.g. of syntax:
BACKUP mydb TO DISK = 'F:\Backups\db.bak' WITH COPYTO = 'U:\Backups';
Or if you perform the backups manually, you could even use the free edition.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
Backup to USB 2.0 drive
Any reasons why backing up to a USB 2.0 HD would be bad? It's quick, easy,
and cheap. The main server is running low on storage and want to offload my
online backups ... thought this would be a cheap solution. The only backups
happening during production are log backups which are only at most 2mb.
--
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.comShaun,
I wouldn't do this in a corporate environment, but I might consider it
at home for pet projects. I always prefer to backup to local disk first,
and then xcopy/robocopy the file elsewhere, or get a tape backup agent
to grab it.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Shaun Beane wrote:
> Any reasons why backing up to a USB 2.0 HD would be bad? It's quick, easy,
> and cheap. The main server is running low on storage and want to offload my
> online backups ... thought this would be a cheap solution. The only backups
> happening during production are log backups which are only at most 2mb.
>|||I agree, but I'm running low on disk space and mr. big boss man doesn't want
to spend the money on an additional drive for the raid. I can get a USB 2.0
drive for about 100 bucks. I would be backing up to the local USB drive
instead of local internal disk. Then I would backup to tape from there.
--
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eRV7zzMyEHA.4028@.TK2MSFTNGP09.phx.gbl...
> Shaun,
> I wouldn't do this in a corporate environment, but I might consider it at
> home for pet projects. I always prefer to backup to local disk first, and
> then xcopy/robocopy the file elsewhere, or get a tape backup agent to grab
> it.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Shaun Beane wrote:
>> Any reasons why backing up to a USB 2.0 HD would be bad? It's quick,
>> easy, and cheap. The main server is running low on storage and want to
>> offload my online backups ... thought this would be a cheap solution.
>> The only backups happening during production are log backups which are
>> only at most 2mb.|||For an additional 99 bucks, you could get MiniSQLBackup which ought to be
able to compress your backups to averagely 25% of their original size, and
then have it copy it to your USB drive upon completion. E.g. of syntax:
BACKUP mydb TO DISK = 'F:\Backups\db.bak' WITH COPYTO = 'U:\Backups\';
Or if you perform the backups manually, you could even use the free edition.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
and cheap. The main server is running low on storage and want to offload my
online backups ... thought this would be a cheap solution. The only backups
happening during production are log backups which are only at most 2mb.
--
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.comShaun,
I wouldn't do this in a corporate environment, but I might consider it
at home for pet projects. I always prefer to backup to local disk first,
and then xcopy/robocopy the file elsewhere, or get a tape backup agent
to grab it.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Shaun Beane wrote:
> Any reasons why backing up to a USB 2.0 HD would be bad? It's quick, easy,
> and cheap. The main server is running low on storage and want to offload my
> online backups ... thought this would be a cheap solution. The only backups
> happening during production are log backups which are only at most 2mb.
>|||I agree, but I'm running low on disk space and mr. big boss man doesn't want
to spend the money on an additional drive for the raid. I can get a USB 2.0
drive for about 100 bucks. I would be backing up to the local USB drive
instead of local internal disk. Then I would backup to tape from there.
--
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eRV7zzMyEHA.4028@.TK2MSFTNGP09.phx.gbl...
> Shaun,
> I wouldn't do this in a corporate environment, but I might consider it at
> home for pet projects. I always prefer to backup to local disk first, and
> then xcopy/robocopy the file elsewhere, or get a tape backup agent to grab
> it.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Shaun Beane wrote:
>> Any reasons why backing up to a USB 2.0 HD would be bad? It's quick,
>> easy, and cheap. The main server is running low on storage and want to
>> offload my online backups ... thought this would be a cheap solution.
>> The only backups happening during production are log backups which are
>> only at most 2mb.|||For an additional 99 bucks, you could get MiniSQLBackup which ought to be
able to compress your backups to averagely 25% of their original size, and
then have it copy it to your USB drive upon completion. E.g. of syntax:
BACKUP mydb TO DISK = 'F:\Backups\db.bak' WITH COPYTO = 'U:\Backups\';
Or if you perform the backups manually, you could even use the free edition.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
Friday, February 10, 2012
Backup to Share with SMO fails, but works to local hard disk
I have a short program (vb.net) which backs up an SQLExpress database. It works fine if the destination is a file on the local drive. However, it always fails if the destination file is a network share or a mapped share. The same program verifies the share is available by trying to read and write a small file before performing the backup.
This is a stripped down version of the code
Dim svr As Server = New Server("Servername")
svr.ConnectionContext.LoginSecure = false
svr.ConnectionContext.Login = TextBoxBULogin.Text
svr.ConnectionContext.Password = TextBoxBUPwd.Text
Dim bkp As Backup = New Backup()
bkp.Action = BackupActionType.Database
bkp.Database = TextBoxBUDatabase.Text
Dim x As New Microsoft.SqlServer.Management.Smo.BackupDeviceItem(destfilepath, DeviceType.File)
bkp.Devices.Add(x)
bkp.SqlBackup(svr)
The exception generated is an SMO.FailedOperationException and reads "System.Data.SqlClient.SqlError: Cannot open backup device 'Z:\v1tbcontacts00.bak'. Device error or device off-line."
Where "Z:\" is a network share. If the backup device string is set to "C:\v1tbcontacts00.bak" the backup succeeds.
Have you tried to use Transact-SQL to backup to the share? In any case you may find it better to back up to the local drive, then copy the backup file using operating system commands to the network share for performance purposes.|||Henry,
using SQL authentication will make SMO to use the SQL Server Sevice account to authenticate on the network share, so although you might be able to check the network share for existance within your application, the credentials used for this is not used for making the backup on the file share.
Client application (Windows token) --> SQL Server using Windows authentication and connected with Windows Authentication --> SMO uses the Windows credentials of the connected client to access the share
Client application (Windows token) --> SQL Server using Mixed Authentication and connected with SQL Server Authentication --> SMO uses the SQL Server Service account credentials of to access the share
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||The practical solution to my issue is to perform a database backup to the local hard disk and then copy the backup to a share.
Better still. the nugget of information about how authentication works with SMO explains why the problem occured in the first place.
Thanks
Backup to networkdrive possible?
I can only see my local drive(s). I have mapped 2 networkshares but
can't see them when trying to choose a location for my backup. Can a
filebackup be made directly to a mapped (or non-mapped) networklocation?
Tnx,
Hans Brouwer
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Yes. You must manually type the UNC name for the target. Do not use drive
mappings since SQL may not know about them. Also, the SQL service account
must have rights to write files in the target directory.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"hansje" <hansjes@.anonymous.com> wrote in message
news:OFVHba0rDHA.2456@.TK2MSFTNGP12.phx.gbl...
> I can only see my local drive(s). I have mapped 2 networkshares but
> can't see them when trying to choose a location for my backup. Can a
> filebackup be made directly to a mapped (or non-mapped) networklocation?
> Tnx,
> Hans Brouwer
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi ,
To add on to geoff's mail.
You can map the drive and use it in Query Analyzer along with Backup
statement.
Say your network server name FINANCE and the drive is k$ then use the below
statement to backup,
Backup database HRMS to disk='\\FINANCE\k$\MSSQL\BACKUP\HRMS_BACKUP.BAK with
init
The below statement will succed provided you have rights on that drive.
Thanks
Hari
MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:#vUJ$e0rDHA.2084@.TK2MSFTNGP12.phx.gbl...
> Yes. You must manually type the UNC name for the target. Do not use
drive
> mappings since SQL may not know about them. Also, the SQL service account
> must have rights to write files in the target directory.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
>
> "hansje" <hansjes@.anonymous.com> wrote in message
> news:OFVHba0rDHA.2456@.TK2MSFTNGP12.phx.gbl...
> > I can only see my local drive(s). I have mapped 2 networkshares but
> > can't see them when trying to choose a location for my backup. Can a
> > filebackup be made directly to a mapped (or non-mapped) networklocation?
> >
> > Tnx,
> >
> > Hans Brouwer
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
>
can't see them when trying to choose a location for my backup. Can a
filebackup be made directly to a mapped (or non-mapped) networklocation?
Tnx,
Hans Brouwer
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Yes. You must manually type the UNC name for the target. Do not use drive
mappings since SQL may not know about them. Also, the SQL service account
must have rights to write files in the target directory.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"hansje" <hansjes@.anonymous.com> wrote in message
news:OFVHba0rDHA.2456@.TK2MSFTNGP12.phx.gbl...
> I can only see my local drive(s). I have mapped 2 networkshares but
> can't see them when trying to choose a location for my backup. Can a
> filebackup be made directly to a mapped (or non-mapped) networklocation?
> Tnx,
> Hans Brouwer
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi ,
To add on to geoff's mail.
You can map the drive and use it in Query Analyzer along with Backup
statement.
Say your network server name FINANCE and the drive is k$ then use the below
statement to backup,
Backup database HRMS to disk='\\FINANCE\k$\MSSQL\BACKUP\HRMS_BACKUP.BAK with
init
The below statement will succed provided you have rights on that drive.
Thanks
Hari
MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:#vUJ$e0rDHA.2084@.TK2MSFTNGP12.phx.gbl...
> Yes. You must manually type the UNC name for the target. Do not use
drive
> mappings since SQL may not know about them. Also, the SQL service account
> must have rights to write files in the target directory.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
>
> "hansje" <hansjes@.anonymous.com> wrote in message
> news:OFVHba0rDHA.2456@.TK2MSFTNGP12.phx.gbl...
> > I can only see my local drive(s). I have mapped 2 networkshares but
> > can't see them when trying to choose a location for my backup. Can a
> > filebackup be made directly to a mapped (or non-mapped) networklocation?
> >
> > Tnx,
> >
> > Hans Brouwer
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
>
Backup to network drive using Workgroup/Standard.
I am using SQL Server 2005 Express Edition. I am planning on upgrading to either Workgroup or Standard edition. While working with Express Edition I did find out that this version does not support backup to a network drive.
My question is:
Given a database limit of 50GB, which one of the editions, Workgroup or Standard, will be appropriate for use.Thank you for your time and patience.
Regards
Berly Sam
My question is:
- Is it possible to backup directly to a network drive using a Workgroup/ Standard edition of SQL Server rather than backing up to a local drive and then 'copy and paste' to a network drive like the Express Edition.
Given a database limit of 50GB, which one of the editions, Workgroup or Standard, will be appropriate for use.Thank you for your time and patience.
Regards
Berly Sam
You cannot backup to a network share with any edition of SQL Server.
SQL Server Backup will only used locally attached drives, or NAS/SAN LUNs. You can either backup locally, and then move the file to a network share (both tasks can be automated with SQL Agent -not included with SQL Express), or Check out the several third party backup tools. (Idera, Red-Gate, to name a couple.)
Workgroup-Standard-Enterprise, which is 'best'. It depends upon the functionality that you require. With a database of that size, you are definitely between the Standard and Enterprise Editions (in my opinion). You may wish to check out the functionality differences at this site.
|||Thats not exactly true, you cannot see the backup media in the SQL Server dialog as it only displays local fixed drives, but you can specify a UNC share within the dialog textbox or within the script. Depending on the situation you should rather consider using a local /SAN / NAS backup first and then copy it to a network share if needed.Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
Jens,
I consider it a gentle ‘challenge’ for me to give more consideration to why I responded as I did. And I appreciate that.
As I contemplate why I thought so, all I can remember is ever since 6.5, I have always taught to backup locally and xcopy to a net share. I can only imagine that was because of the speed issues of what used to be slower networks, as well as the persistence and permissions issues related to mapped drives.
And I now realize that I have not reconsidered or changed that practice in the intervening years.
I’m glad that you challenged me, now I have tested and affirmed that a backup to a UNC works just fine. But I doubt that I will change my practice of Backing up locally and using xcopy to move the backup file to a network resource -the BACKUP won't fail because of any network issues.
Regards,
- Arnie Rowland
backup to network drive
I'm trying to create a job to backup to a network drive. The following SQL does not execute successfully:
USE master
EXEC sp_addumpdevice 'disk', 'Thurs6', '\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
BACKUP LOG Mock8 TO Thurs6 WITH INIT
However, if I change the destination file to one located on the SQL server, it executes fine. I've also tried this using a mapped drive letter, and that doesn't work either. Any thoughts?
Backups are done under the account that sql server runs under and not the
account you are logged on as. So if SQL Server is not running under a domain
account with access to that share it can not see it.
Andrew J. Kelly SQL MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?
|||It could be that the account used by SQL Server doesn't have permissions to
that share. Can you post the exact error messages?
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?
|||Hi,
To solve the issue you have start the SQL Server service using an Domain OS
user which got write access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
USE master
go
EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
go
BACKUP LOG Mock8 TO Thurs6 WITH INIT
Thanks
Hari
MCDBA
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?
USE master
EXEC sp_addumpdevice 'disk', 'Thurs6', '\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
BACKUP LOG Mock8 TO Thurs6 WITH INIT
However, if I change the destination file to one located on the SQL server, it executes fine. I've also tried this using a mapped drive letter, and that doesn't work either. Any thoughts?
Backups are done under the account that sql server runs under and not the
account you are logged on as. So if SQL Server is not running under a domain
account with access to that share it can not see it.
Andrew J. Kelly SQL MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?
|||It could be that the account used by SQL Server doesn't have permissions to
that share. Can you post the exact error messages?
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?
|||Hi,
To solve the issue you have start the SQL Server service using an Domain OS
user which got write access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
USE master
go
EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
go
BACKUP LOG Mock8 TO Thurs6 WITH INIT
Thanks
Hari
MCDBA
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?
Backup to Network Drive
We are attempting to set our SQL Server 2000 backups to point to a network
drive. We have created a domain user and set the SQL Server Service and SQL
Server Agent to use this account. We have also set this account as a member
of the Administrators group on the SQL Server itself and added it to the sys
admin group within SQL Server. On the destination server, the service account
has full control on the share that we'd like to write to. Unfortunately, our
backups are failing. The SQL Server Log provides the following message
"BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
create. Operating system error = 5(Access is denied.)."
What are we missing here?Marcia,
Did you restart the SQL Server once the drive was mounted on your
server? Have you verified that as the user which SQL Server starts
under on the box you can read and write data to that location? These
are some of the gotcha's. I wish SQL Server allowed you to see network
mount points when you go to Backup Database options through Enterprise
Manager.
Shahryar
MarciaN wrote:
>We are attempting to set our SQL Server 2000 backups to point to a network
>drive. We have created a domain user and set the SQL Server Service and SQL
>Server Agent to use this account. We have also set this account as a member
>of the Administrators group on the SQL Server itself and added it to the sys
>admin group within SQL Server. On the destination server, the service account
>has full control on the share that we'd like to write to. Unfortunately, our
>backups are failing. The SQL Server Log provides the following message
>"BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
>create. Operating system error = 5(Access is denied.)."
>What are we missing here?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> We are attempting to set our SQL Server 2000 backups to point to a network
> drive. We have created a domain user and set the SQL Server Service and
> SQL
> Server Agent to use this account. We have also set this account as a
> member
> of the Administrators group on the SQL Server itself and added it to the
> sys
> admin group within SQL Server. On the destination server, the service
> account
> has full control on the share that we'd like to write to. Unfortunately,
> our
> backups are failing. The SQL Server Log provides the following message
> "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
> create. Operating system error = 5(Access is denied.)."
> What are we missing here?|||We have followed these procedures and still no luck. Can you give some
specifics related to the access levels that the WIndows Service account
should have?
"Geoff N. Hiten" wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> > We are attempting to set our SQL Server 2000 backups to point to a network
> > drive. We have created a domain user and set the SQL Server Service and
> > SQL
> > Server Agent to use this account. We have also set this account as a
> > member
> > of the Administrators group on the SQL Server itself and added it to the
> > sys
> > admin group within SQL Server. On the destination server, the service
> > account
> > has full control on the share that we'd like to write to. Unfortunately,
> > our
> > backups are failing. The SQL Server Log provides the following message
> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
> > create. Operating system error = 5(Access is denied.)."
> >
> > What are we missing here?
>
>|||The SQL Server service account requires FULL CONTROL over the share and the
underlying NTFS directory structure.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
> We have followed these procedures and still no luck. Can you give some
> specifics related to the access levels that the WIndows Service account
> should have?
>
> "Geoff N. Hiten" wrote:
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/?kbid=555128
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
>> > We are attempting to set our SQL Server 2000 backups to point to a
>> > network
>> > drive. We have created a domain user and set the SQL Server Service and
>> > SQL
>> > Server Agent to use this account. We have also set this account as a
>> > member
>> > of the Administrators group on the SQL Server itself and added it to
>> > the
>> > sys
>> > admin group within SQL Server. On the destination server, the service
>> > account
>> > has full control on the share that we'd like to write to.
>> > Unfortunately,
>> > our
>> > backups are failing. The SQL Server Log provides the following message
>> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
>> > to
>> > create. Operating system error = 5(Access is denied.)."
>> >
>> > What are we missing here?
>>|||We gave the service account FULL CONTROL over the share. After an
unsuccessful test of that, we added the service account to the local admins
group on the destination server and still no luck. It seems like we have
covered all the bases but still can not make this work. Can you think of
anything else?
"Geoff N. Hiten" wrote:
> The SQL Server service account requires FULL CONTROL over the share and the
> underlying NTFS directory structure.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
> > We have followed these procedures and still no luck. Can you give some
> > specifics related to the access levels that the WIndows Service account
> > should have?
> >
> >
> > "Geoff N. Hiten" wrote:
> >
> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> http://support.microsoft.com/?kbid=555128
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior Database Administrator
> >> Microsoft SQL Server MVP
> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> >> > We are attempting to set our SQL Server 2000 backups to point to a
> >> > network
> >> > drive. We have created a domain user and set the SQL Server Service and
> >> > SQL
> >> > Server Agent to use this account. We have also set this account as a
> >> > member
> >> > of the Administrators group on the SQL Server itself and added it to
> >> > the
> >> > sys
> >> > admin group within SQL Server. On the destination server, the service
> >> > account
> >> > has full control on the share that we'd like to write to.
> >> > Unfortunately,
> >> > our
> >> > backups are failing. The SQL Server Log provides the following message
> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
> >> > to
> >> > create. Operating system error = 5(Access is denied.)."
> >> >
> >> > What are we missing here?
> >>
> >>
> >>
>
>|||Verify NTFS file permissions on the destination directory. This is
different from the share security permissions.
Log in to the console of the SQL Server as the service account and try to
access the network share directly. Create, rename, and delete a file.
Run a backup via Query Analyzer (BACKUP DATABASE command) to the share
location.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...
> We gave the service account FULL CONTROL over the share. After an
> unsuccessful test of that, we added the service account to the local
> admins
> group on the destination server and still no luck. It seems like we have
> covered all the bases but still can not make this work. Can you think of
> anything else?
> "Geoff N. Hiten" wrote:
>> The SQL Server service account requires FULL CONTROL over the share and
>> the
>> underlying NTFS directory structure.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
>> > We have followed these procedures and still no luck. Can you give some
>> > specifics related to the access levels that the WIndows Service account
>> > should have?
>> >
>> >
>> > "Geoff N. Hiten" wrote:
>> >
>> >> HowTo: Backup to UNC name using Database Maintenance Wizard
>> >> http://support.microsoft.com/?kbid=555128
>> >>
>> >> --
>> >> Geoff N. Hiten
>> >> Senior Database Administrator
>> >> Microsoft SQL Server MVP
>> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
>> >> > We are attempting to set our SQL Server 2000 backups to point to a
>> >> > network
>> >> > drive. We have created a domain user and set the SQL Server Service
>> >> > and
>> >> > SQL
>> >> > Server Agent to use this account. We have also set this account as a
>> >> > member
>> >> > of the Administrators group on the SQL Server itself and added it to
>> >> > the
>> >> > sys
>> >> > admin group within SQL Server. On the destination server, the
>> >> > service
>> >> > account
>> >> > has full control on the share that we'd like to write to.
>> >> > Unfortunately,
>> >> > our
>> >> > backups are failing. The SQL Server Log provides the following
>> >> > message
>> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup'
>> >> > failed
>> >> > to
>> >> > create. Operating system error = 5(Access is denied.)."
>> >> >
>> >> > What are we missing here?
>> >>
>> >>
>> >>
>>|||I'd start by logging in on the SQL Server machine using the service account and see if I can access
and create files. If so, use xp_cmdshell to see if you can do the same.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...
> We gave the service account FULL CONTROL over the share. After an
> unsuccessful test of that, we added the service account to the local admins
> group on the destination server and still no luck. It seems like we have
> covered all the bases but still can not make this work. Can you think of
> anything else?
> "Geoff N. Hiten" wrote:
>> The SQL Server service account requires FULL CONTROL over the share and the
>> underlying NTFS directory structure.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
>> > We have followed these procedures and still no luck. Can you give some
>> > specifics related to the access levels that the WIndows Service account
>> > should have?
>> >
>> >
>> > "Geoff N. Hiten" wrote:
>> >
>> >> HowTo: Backup to UNC name using Database Maintenance Wizard
>> >> http://support.microsoft.com/?kbid=555128
>> >>
>> >> --
>> >> Geoff N. Hiten
>> >> Senior Database Administrator
>> >> Microsoft SQL Server MVP
>> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
>> >> > We are attempting to set our SQL Server 2000 backups to point to a
>> >> > network
>> >> > drive. We have created a domain user and set the SQL Server Service and
>> >> > SQL
>> >> > Server Agent to use this account. We have also set this account as a
>> >> > member
>> >> > of the Administrators group on the SQL Server itself and added it to
>> >> > the
>> >> > sys
>> >> > admin group within SQL Server. On the destination server, the service
>> >> > account
>> >> > has full control on the share that we'd like to write to.
>> >> > Unfortunately,
>> >> > our
>> >> > backups are failing. The SQL Server Log provides the following message
>> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
>> >> > to
>> >> > create. Operating system error = 5(Access is denied.)."
>> >> >
>> >> > What are we missing here?
>> >>
>> >>
>> >>
>>|||It's working! We deleted the shared folder and recreated it. Now it works.
Thanks to all for your suggestions.
"Tibor Karaszi" wrote:
> I'd start by logging in on the SQL Server machine using the service account and see if I can access
> and create files. If so, use xp_cmdshell to see if you can do the same.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...
> > We gave the service account FULL CONTROL over the share. After an
> > unsuccessful test of that, we added the service account to the local admins
> > group on the destination server and still no luck. It seems like we have
> > covered all the bases but still can not make this work. Can you think of
> > anything else?
> >
> > "Geoff N. Hiten" wrote:
> >
> >> The SQL Server service account requires FULL CONTROL over the share and the
> >> underlying NTFS directory structure.
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior Database Administrator
> >> Microsoft SQL Server MVP
> >>
> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> >> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
> >> > We have followed these procedures and still no luck. Can you give some
> >> > specifics related to the access levels that the WIndows Service account
> >> > should have?
> >> >
> >> >
> >> > "Geoff N. Hiten" wrote:
> >> >
> >> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> >> http://support.microsoft.com/?kbid=555128
> >> >>
> >> >> --
> >> >> Geoff N. Hiten
> >> >> Senior Database Administrator
> >> >> Microsoft SQL Server MVP
> >> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> >> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> >> >> > We are attempting to set our SQL Server 2000 backups to point to a
> >> >> > network
> >> >> > drive. We have created a domain user and set the SQL Server Service and
> >> >> > SQL
> >> >> > Server Agent to use this account. We have also set this account as a
> >> >> > member
> >> >> > of the Administrators group on the SQL Server itself and added it to
> >> >> > the
> >> >> > sys
> >> >> > admin group within SQL Server. On the destination server, the service
> >> >> > account
> >> >> > has full control on the share that we'd like to write to.
> >> >> > Unfortunately,
> >> >> > our
> >> >> > backups are failing. The SQL Server Log provides the following message
> >> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
> >> >> > to
> >> >> > create. Operating system error = 5(Access is denied.)."
> >> >> >
> >> >> > What are we missing here?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>|||Windows has two level access control.
You need to give the sql account first the access to the shared
drive/directory and then you need to give access to the
files
1. Access to the SHARE
2. Access to files on that share
1. On the shared directory right click-> sharing->permissions. Check the
SQL account has access rights.
2. same right click->security->permissions. check the SQL account has
rights.
That should do it
rgrds Matti
drive. We have created a domain user and set the SQL Server Service and SQL
Server Agent to use this account. We have also set this account as a member
of the Administrators group on the SQL Server itself and added it to the sys
admin group within SQL Server. On the destination server, the service account
has full control on the share that we'd like to write to. Unfortunately, our
backups are failing. The SQL Server Log provides the following message
"BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
create. Operating system error = 5(Access is denied.)."
What are we missing here?Marcia,
Did you restart the SQL Server once the drive was mounted on your
server? Have you verified that as the user which SQL Server starts
under on the box you can read and write data to that location? These
are some of the gotcha's. I wish SQL Server allowed you to see network
mount points when you go to Backup Database options through Enterprise
Manager.
Shahryar
MarciaN wrote:
>We are attempting to set our SQL Server 2000 backups to point to a network
>drive. We have created a domain user and set the SQL Server Service and SQL
>Server Agent to use this account. We have also set this account as a member
>of the Administrators group on the SQL Server itself and added it to the sys
>admin group within SQL Server. On the destination server, the service account
>has full control on the share that we'd like to write to. Unfortunately, our
>backups are failing. The SQL Server Log provides the following message
>"BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
>create. Operating system error = 5(Access is denied.)."
>What are we missing here?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> We are attempting to set our SQL Server 2000 backups to point to a network
> drive. We have created a domain user and set the SQL Server Service and
> SQL
> Server Agent to use this account. We have also set this account as a
> member
> of the Administrators group on the SQL Server itself and added it to the
> sys
> admin group within SQL Server. On the destination server, the service
> account
> has full control on the share that we'd like to write to. Unfortunately,
> our
> backups are failing. The SQL Server Log provides the following message
> "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
> create. Operating system error = 5(Access is denied.)."
> What are we missing here?|||We have followed these procedures and still no luck. Can you give some
specifics related to the access levels that the WIndows Service account
should have?
"Geoff N. Hiten" wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> > We are attempting to set our SQL Server 2000 backups to point to a network
> > drive. We have created a domain user and set the SQL Server Service and
> > SQL
> > Server Agent to use this account. We have also set this account as a
> > member
> > of the Administrators group on the SQL Server itself and added it to the
> > sys
> > admin group within SQL Server. On the destination server, the service
> > account
> > has full control on the share that we'd like to write to. Unfortunately,
> > our
> > backups are failing. The SQL Server Log provides the following message
> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
> > create. Operating system error = 5(Access is denied.)."
> >
> > What are we missing here?
>
>|||The SQL Server service account requires FULL CONTROL over the share and the
underlying NTFS directory structure.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
> We have followed these procedures and still no luck. Can you give some
> specifics related to the access levels that the WIndows Service account
> should have?
>
> "Geoff N. Hiten" wrote:
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/?kbid=555128
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
>> > We are attempting to set our SQL Server 2000 backups to point to a
>> > network
>> > drive. We have created a domain user and set the SQL Server Service and
>> > SQL
>> > Server Agent to use this account. We have also set this account as a
>> > member
>> > of the Administrators group on the SQL Server itself and added it to
>> > the
>> > sys
>> > admin group within SQL Server. On the destination server, the service
>> > account
>> > has full control on the share that we'd like to write to.
>> > Unfortunately,
>> > our
>> > backups are failing. The SQL Server Log provides the following message
>> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
>> > to
>> > create. Operating system error = 5(Access is denied.)."
>> >
>> > What are we missing here?
>>|||We gave the service account FULL CONTROL over the share. After an
unsuccessful test of that, we added the service account to the local admins
group on the destination server and still no luck. It seems like we have
covered all the bases but still can not make this work. Can you think of
anything else?
"Geoff N. Hiten" wrote:
> The SQL Server service account requires FULL CONTROL over the share and the
> underlying NTFS directory structure.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
> > We have followed these procedures and still no luck. Can you give some
> > specifics related to the access levels that the WIndows Service account
> > should have?
> >
> >
> > "Geoff N. Hiten" wrote:
> >
> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> http://support.microsoft.com/?kbid=555128
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior Database Administrator
> >> Microsoft SQL Server MVP
> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> >> > We are attempting to set our SQL Server 2000 backups to point to a
> >> > network
> >> > drive. We have created a domain user and set the SQL Server Service and
> >> > SQL
> >> > Server Agent to use this account. We have also set this account as a
> >> > member
> >> > of the Administrators group on the SQL Server itself and added it to
> >> > the
> >> > sys
> >> > admin group within SQL Server. On the destination server, the service
> >> > account
> >> > has full control on the share that we'd like to write to.
> >> > Unfortunately,
> >> > our
> >> > backups are failing. The SQL Server Log provides the following message
> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
> >> > to
> >> > create. Operating system error = 5(Access is denied.)."
> >> >
> >> > What are we missing here?
> >>
> >>
> >>
>
>|||Verify NTFS file permissions on the destination directory. This is
different from the share security permissions.
Log in to the console of the SQL Server as the service account and try to
access the network share directly. Create, rename, and delete a file.
Run a backup via Query Analyzer (BACKUP DATABASE command) to the share
location.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...
> We gave the service account FULL CONTROL over the share. After an
> unsuccessful test of that, we added the service account to the local
> admins
> group on the destination server and still no luck. It seems like we have
> covered all the bases but still can not make this work. Can you think of
> anything else?
> "Geoff N. Hiten" wrote:
>> The SQL Server service account requires FULL CONTROL over the share and
>> the
>> underlying NTFS directory structure.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
>> > We have followed these procedures and still no luck. Can you give some
>> > specifics related to the access levels that the WIndows Service account
>> > should have?
>> >
>> >
>> > "Geoff N. Hiten" wrote:
>> >
>> >> HowTo: Backup to UNC name using Database Maintenance Wizard
>> >> http://support.microsoft.com/?kbid=555128
>> >>
>> >> --
>> >> Geoff N. Hiten
>> >> Senior Database Administrator
>> >> Microsoft SQL Server MVP
>> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
>> >> > We are attempting to set our SQL Server 2000 backups to point to a
>> >> > network
>> >> > drive. We have created a domain user and set the SQL Server Service
>> >> > and
>> >> > SQL
>> >> > Server Agent to use this account. We have also set this account as a
>> >> > member
>> >> > of the Administrators group on the SQL Server itself and added it to
>> >> > the
>> >> > sys
>> >> > admin group within SQL Server. On the destination server, the
>> >> > service
>> >> > account
>> >> > has full control on the share that we'd like to write to.
>> >> > Unfortunately,
>> >> > our
>> >> > backups are failing. The SQL Server Log provides the following
>> >> > message
>> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup'
>> >> > failed
>> >> > to
>> >> > create. Operating system error = 5(Access is denied.)."
>> >> >
>> >> > What are we missing here?
>> >>
>> >>
>> >>
>>|||I'd start by logging in on the SQL Server machine using the service account and see if I can access
and create files. If so, use xp_cmdshell to see if you can do the same.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...
> We gave the service account FULL CONTROL over the share. After an
> unsuccessful test of that, we added the service account to the local admins
> group on the destination server and still no luck. It seems like we have
> covered all the bases but still can not make this work. Can you think of
> anything else?
> "Geoff N. Hiten" wrote:
>> The SQL Server service account requires FULL CONTROL over the share and the
>> underlying NTFS directory structure.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
>> > We have followed these procedures and still no luck. Can you give some
>> > specifics related to the access levels that the WIndows Service account
>> > should have?
>> >
>> >
>> > "Geoff N. Hiten" wrote:
>> >
>> >> HowTo: Backup to UNC name using Database Maintenance Wizard
>> >> http://support.microsoft.com/?kbid=555128
>> >>
>> >> --
>> >> Geoff N. Hiten
>> >> Senior Database Administrator
>> >> Microsoft SQL Server MVP
>> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
>> >> > We are attempting to set our SQL Server 2000 backups to point to a
>> >> > network
>> >> > drive. We have created a domain user and set the SQL Server Service and
>> >> > SQL
>> >> > Server Agent to use this account. We have also set this account as a
>> >> > member
>> >> > of the Administrators group on the SQL Server itself and added it to
>> >> > the
>> >> > sys
>> >> > admin group within SQL Server. On the destination server, the service
>> >> > account
>> >> > has full control on the share that we'd like to write to.
>> >> > Unfortunately,
>> >> > our
>> >> > backups are failing. The SQL Server Log provides the following message
>> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
>> >> > to
>> >> > create. Operating system error = 5(Access is denied.)."
>> >> >
>> >> > What are we missing here?
>> >>
>> >>
>> >>
>>|||It's working! We deleted the shared folder and recreated it. Now it works.
Thanks to all for your suggestions.
"Tibor Karaszi" wrote:
> I'd start by logging in on the SQL Server machine using the service account and see if I can access
> and create files. If so, use xp_cmdshell to see if you can do the same.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...
> > We gave the service account FULL CONTROL over the share. After an
> > unsuccessful test of that, we added the service account to the local admins
> > group on the destination server and still no luck. It seems like we have
> > covered all the bases but still can not make this work. Can you think of
> > anything else?
> >
> > "Geoff N. Hiten" wrote:
> >
> >> The SQL Server service account requires FULL CONTROL over the share and the
> >> underlying NTFS directory structure.
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior Database Administrator
> >> Microsoft SQL Server MVP
> >>
> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> >> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
> >> > We have followed these procedures and still no luck. Can you give some
> >> > specifics related to the access levels that the WIndows Service account
> >> > should have?
> >> >
> >> >
> >> > "Geoff N. Hiten" wrote:
> >> >
> >> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> >> http://support.microsoft.com/?kbid=555128
> >> >>
> >> >> --
> >> >> Geoff N. Hiten
> >> >> Senior Database Administrator
> >> >> Microsoft SQL Server MVP
> >> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> >> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> >> >> > We are attempting to set our SQL Server 2000 backups to point to a
> >> >> > network
> >> >> > drive. We have created a domain user and set the SQL Server Service and
> >> >> > SQL
> >> >> > Server Agent to use this account. We have also set this account as a
> >> >> > member
> >> >> > of the Administrators group on the SQL Server itself and added it to
> >> >> > the
> >> >> > sys
> >> >> > admin group within SQL Server. On the destination server, the service
> >> >> > account
> >> >> > has full control on the share that we'd like to write to.
> >> >> > Unfortunately,
> >> >> > our
> >> >> > backups are failing. The SQL Server Log provides the following message
> >> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
> >> >> > to
> >> >> > create. Operating system error = 5(Access is denied.)."
> >> >> >
> >> >> > What are we missing here?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>|||Windows has two level access control.
You need to give the sql account first the access to the shared
drive/directory and then you need to give access to the
files
1. Access to the SHARE
2. Access to files on that share
1. On the shared directory right click-> sharing->permissions. Check the
SQL account has access rights.
2. same right click->security->permissions. check the SQL account has
rights.
That should do it
rgrds Matti
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
We are attempting to set our SQL Server 2000 backups to point to a network
drive. We have created a domain user and set the SQL Server Service and SQL
Server Agent to use this account. We have also set this account as a member
of the Administrators group on the SQL Server itself and added it to the sys
admin group within SQL Server. On the destination server, the service account
has full control on the share that we'd like to write to. Unfortunately, our
backups are failing. The SQL Server Log provides the following message
"BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
create. Operating system error = 5(Access is denied.)."
What are we missing here?
Marcia,
Did you restart the SQL Server once the drive was mounted on your
server? Have you verified that as the user which SQL Server starts
under on the box you can read and write data to that location? These
are some of the gotcha's. I wish SQL Server allowed you to see network
mount points when you go to Backup Database options through Enterprise
Manager.
Shahryar
MarciaN wrote:
>We are attempting to set our SQL Server 2000 backups to point to a network
>drive. We have created a domain user and set the SQL Server Service and SQL
>Server Agent to use this account. We have also set this account as a member
>of the Administrators group on the SQL Server itself and added it to the sys
>admin group within SQL Server. On the destination server, the service account
>has full control on the share that we'd like to write to. Unfortunately, our
>backups are failing. The SQL Server Log provides the following message
>"BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
>create. Operating system error = 5(Access is denied.)."
>What are we missing here?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> We are attempting to set our SQL Server 2000 backups to point to a network
> drive. We have created a domain user and set the SQL Server Service and
> SQL
> Server Agent to use this account. We have also set this account as a
> member
> of the Administrators group on the SQL Server itself and added it to the
> sys
> admin group within SQL Server. On the destination server, the service
> account
> has full control on the share that we'd like to write to. Unfortunately,
> our
> backups are failing. The SQL Server Log provides the following message
> "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
> create. Operating system error = 5(Access is denied.)."
> What are we missing here?
|||We have followed these procedures and still no luck. Can you give some
specifics related to the access levels that the WIndows Service account
should have?
"Geoff N. Hiten" wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
>
>
|||The SQL Server service account requires FULL CONTROL over the share and the
underlying NTFS directory structure.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...[vbcol=seagreen]
> We have followed these procedures and still no luck. Can you give some
> specifics related to the access levels that the WIndows Service account
> should have?
>
> "Geoff N. Hiten" wrote:
|||We gave the service account FULL CONTROL over the share. After an
unsuccessful test of that, we added the service account to the local admins
group on the destination server and still no luck. It seems like we have
covered all the bases but still can not make this work. Can you think of
anything else?
"Geoff N. Hiten" wrote:
> The SQL Server service account requires FULL CONTROL over the share and the
> underlying NTFS directory structure.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
>
>
|||Verify NTFS file permissions on the destination directory. This is
different from the share security permissions.
Log in to the console of the SQL Server as the service account and try to
access the network share directly. Create, rename, and delete a file.
Run a backup via Query Analyzer (BACKUP DATABASE command) to the share
location.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...[vbcol=seagreen]
> We gave the service account FULL CONTROL over the share. After an
> unsuccessful test of that, we added the service account to the local
> admins
> group on the destination server and still no luck. It seems like we have
> covered all the bases but still can not make this work. Can you think of
> anything else?
> "Geoff N. Hiten" wrote:
|||I'd start by logging in on the SQL Server machine using the service account and see if I can access
and create files. If so, use xp_cmdshell to see if you can do the same.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...[vbcol=seagreen]
> We gave the service account FULL CONTROL over the share. After an
> unsuccessful test of that, we added the service account to the local admins
> group on the destination server and still no luck. It seems like we have
> covered all the bases but still can not make this work. Can you think of
> anything else?
> "Geoff N. Hiten" wrote:
|||It's working! We deleted the shared folder and recreated it. Now it works.
Thanks to all for your suggestions.
"Tibor Karaszi" wrote:
> I'd start by logging in on the SQL Server machine using the service account and see if I can access
> and create files. If so, use xp_cmdshell to see if you can do the same.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...
>
|||Windows has two level access control.
You need to give the sql account first the access to the shared
drive/directory and then you need to give access to the
files
1. Access to the SHARE
2. Access to files on that share
1. On the shared directory right click-> sharing->permissions. Check the
SQL account has access rights.
2. same right click->security->permissions. check the SQL account has
rights.
That should do it
rgrds Matti
drive. We have created a domain user and set the SQL Server Service and SQL
Server Agent to use this account. We have also set this account as a member
of the Administrators group on the SQL Server itself and added it to the sys
admin group within SQL Server. On the destination server, the service account
has full control on the share that we'd like to write to. Unfortunately, our
backups are failing. The SQL Server Log provides the following message
"BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
create. Operating system error = 5(Access is denied.)."
What are we missing here?
Marcia,
Did you restart the SQL Server once the drive was mounted on your
server? Have you verified that as the user which SQL Server starts
under on the box you can read and write data to that location? These
are some of the gotcha's. I wish SQL Server allowed you to see network
mount points when you go to Backup Database options through Enterprise
Manager.
Shahryar
MarciaN wrote:
>We are attempting to set our SQL Server 2000 backups to point to a network
>drive. We have created a domain user and set the SQL Server Service and SQL
>Server Agent to use this account. We have also set this account as a member
>of the Administrators group on the SQL Server itself and added it to the sys
>admin group within SQL Server. On the destination server, the service account
>has full control on the share that we'd like to write to. Unfortunately, our
>backups are failing. The SQL Server Log provides the following message
>"BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
>create. Operating system error = 5(Access is denied.)."
>What are we missing here?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> We are attempting to set our SQL Server 2000 backups to point to a network
> drive. We have created a domain user and set the SQL Server Service and
> SQL
> Server Agent to use this account. We have also set this account as a
> member
> of the Administrators group on the SQL Server itself and added it to the
> sys
> admin group within SQL Server. On the destination server, the service
> account
> has full control on the share that we'd like to write to. Unfortunately,
> our
> backups are failing. The SQL Server Log provides the following message
> "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
> create. Operating system error = 5(Access is denied.)."
> What are we missing here?
|||We have followed these procedures and still no luck. Can you give some
specifics related to the access levels that the WIndows Service account
should have?
"Geoff N. Hiten" wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
>
>
|||The SQL Server service account requires FULL CONTROL over the share and the
underlying NTFS directory structure.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...[vbcol=seagreen]
> We have followed these procedures and still no luck. Can you give some
> specifics related to the access levels that the WIndows Service account
> should have?
>
> "Geoff N. Hiten" wrote:
|||We gave the service account FULL CONTROL over the share. After an
unsuccessful test of that, we added the service account to the local admins
group on the destination server and still no luck. It seems like we have
covered all the bases but still can not make this work. Can you think of
anything else?
"Geoff N. Hiten" wrote:
> The SQL Server service account requires FULL CONTROL over the share and the
> underlying NTFS directory structure.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
>
>
|||Verify NTFS file permissions on the destination directory. This is
different from the share security permissions.
Log in to the console of the SQL Server as the service account and try to
access the network share directly. Create, rename, and delete a file.
Run a backup via Query Analyzer (BACKUP DATABASE command) to the share
location.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...[vbcol=seagreen]
> We gave the service account FULL CONTROL over the share. After an
> unsuccessful test of that, we added the service account to the local
> admins
> group on the destination server and still no luck. It seems like we have
> covered all the bases but still can not make this work. Can you think of
> anything else?
> "Geoff N. Hiten" wrote:
|||I'd start by logging in on the SQL Server machine using the service account and see if I can access
and create files. If so, use xp_cmdshell to see if you can do the same.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...[vbcol=seagreen]
> We gave the service account FULL CONTROL over the share. After an
> unsuccessful test of that, we added the service account to the local admins
> group on the destination server and still no luck. It seems like we have
> covered all the bases but still can not make this work. Can you think of
> anything else?
> "Geoff N. Hiten" wrote:
|||It's working! We deleted the shared folder and recreated it. Now it works.
Thanks to all for your suggestions.
"Tibor Karaszi" wrote:
> I'd start by logging in on the SQL Server machine using the service account and see if I can access
> and create files. If so, use xp_cmdshell to see if you can do the same.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...
>
|||Windows has two level access control.
You need to give the sql account first the access to the shared
drive/directory and then you need to give access to the
files
1. Access to the SHARE
2. Access to files on that share
1. On the shared directory right click-> sharing->permissions. Check the
SQL account has access rights.
2. same right click->security->permissions. check the SQL account has
rights.
That should do it
rgrds Matti
Subscribe to:
Posts (Atom)