Backing up database over network

I am trying to get my database to backup to a network
path (\\myserver\backup\db.bak) instead of a local path.
But, unfortunately it is not working, I keep the message
that it cannot connect to the device or the device is
offline.
Here is my backup statement
BACKUP DATABASE test
TO DISK = 'c:\temp\test.bak'
WITH FORMAT,
NAME = 'Test Backup'
Any Ideas?The account that sqls erver runs under is the one that is used when backups
occur. So you need to ensure that this account has permissions to the
network share you are attempting to backup to. Chances are the account is
the local admin account which has no domain privileges.
--
Andrew J. Kelly
SQL Server MVP
"Ben Skolmoski" <ben.skolmoski@.imcentric.com> wrote in message
news:02e901c3a581$03d068a0$a601280a@.phx.gbl...
> I am trying to get my database to backup to a network
> path (\\myserver\backup\db.bak) instead of a local path.
> But, unfortunately it is not working, I keep the message
> that it cannot connect to the device or the device is
> offline.
> Here is my backup statement
> BACKUP DATABASE test
> TO DISK = 'c:\temp\test.bak'
> WITH FORMAT,
> NAME = 'Test Backup'
> Any Ideas?|||I have already checked all of that and the account has
full access to my network share.
>--Original Message--
>The account that sqls erver runs under is the one that
is used when backups
>occur. So you need to ensure that this account has
permissions to the
>network share you are attempting to backup to. Chances
are the account is
>the local admin account which has no domain privileges.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ben Skolmoski" <ben.skolmoski@.imcentric.com> wrote in
message
>news:02e901c3a581$03d068a0$a601280a@.phx.gbl...
>> I am trying to get my database to backup to a network
>> path (\\myserver\backup\db.bak) instead of a local
path.
>> But, unfortunately it is not working, I keep the
message
>> that it cannot connect to the device or the device is
>> offline.
>> Here is my backup statement
>> BACKUP DATABASE test
>> TO DISK = 'c:\temp\test.bak'
>> WITH FORMAT,
>> NAME = 'Test Backup'
>> Any Ideas?
>
>.
>|||Ben,
The example you showed was not using UNC and was obviously not the real
code. It helps to show the actual code to alleviate any assumptions or the
possibility of simply a typo that doesn't get relayed with a simpler
example. So I assume from what you state you have logged on to the server
under the account sqls erver is running under and were able to copy a file
with the exact name of the backup to the UNC you want. If not then check to
ensure there isn't already a file with that name that is locked by some
other resource.
--
Andrew J. Kelly
SQL Server MVP
"Ben Skolmoski" <ben.skolmoski@.imcentric.com> wrote in message
news:037301c3a589$687422d0$a601280a@.phx.gbl...
> I have already checked all of that and the account has
> full access to my network share.
>
> >--Original Message--
> >The account that sqls erver runs under is the one that
> is used when backups
> >occur. So you need to ensure that this account has
> permissions to the
> >network share you are attempting to backup to. Chances
> are the account is
> >the local admin account which has no domain privileges.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Ben Skolmoski" <ben.skolmoski@.imcentric.com> wrote in
> message
> >news:02e901c3a581$03d068a0$a601280a@.phx.gbl...
> >> I am trying to get my database to backup to a network
> >> path (\\myserver\backup\db.bak) instead of a local
> path.
> >> But, unfortunately it is not working, I keep the
> message
> >> that it cannot connect to the device or the device is
> >> offline.
> >>
> >> Here is my backup statement
> >>
> >> BACKUP DATABASE test
> >> TO DISK = 'c:\temp\test.bak'
> >> WITH FORMAT,
> >> NAME = 'Test Backup'
> >>
> >> Any Ideas?
> >
> >
> >.
> >|||If you log on to the SQL Server machine with the SQL Server startup account
can you access the remote share that you are trying to restore to/
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

avoid repeating "if...end" in a stored procedure

I would like to avoid repeating 4 time the procedure if end and instead use someting like "switch case", but I can not find the equivalent.
Is there any command that could do that?

thanks

[code]
CREATE PROCEDURE sa_default

@.etriduser int= null,
@.locator int = null,
@.choix int = null,
@.login varchar(50) = null

AS
if @.choix=0
begin
........
end

if @.choix=1
begin
.........
end

if @.choix=2
begin
.........
end

if @.choix=3
begin
......
end

RETURN
GO

[/code]What is it you are trying to accomplish in your condition?|||I am selection a list of project which change depending on the level of right of the user

CREATE PROCEDURE sa_default
@.etriduser int= null,
@.locator int = null,
@.choix int = null,
@.login varchar(50) = null
 AS
if @.choix=0
begin
SELECT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service, Tproject.location, Tproject.ponumber, Tproject.state, Tcompany.company, Tproject.datestart,
 ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0 )) /
 (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1
FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany
 WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) and Tproject.locator=isnull(@.locator,locator) ORDER BY idproject
end
if @.choix=1
begin
SELECT DISTINCT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service,Tproject.location, Tproject.ponumber, Tproject.state,
Tcompany.company, Tproject.datestart, ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0)) /
 (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1
 FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject
INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany
 INNER JOIN Tuserproject ON Tproject.idproject = Tuserproject.etridproject
 WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) and (Tuserproject.etridperson=@.etriduser or Tproject.locator=@.locator) ORDER BY idproject
end
if @.choix=2
begin
SELECT DISTINCT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service,Tproject.location, Tproject.ponumber, Tproject.state,
 Tcompany.company, Tproject.datestart, ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0)) /
(SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1,Tuserproject.mc, Tuserproject.type, Tuserproject.wechart
 FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject
INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany INNER JOIN Tuserproject ON Tproject.idproject = Tuserproject.etridproject
 WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) and Tuserproject.etridperson=@.etriduser ORDER BY idproject
end
if @.choix=3
begin
SELECT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service, Tproject.location, Tproject.ponumber, Tproject.state, Tcompany.company, Tproject.datestart,
 ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0)) /
(SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1, Tuserproject.mc, Tuserproject.type, Tuserproject.wechart
 FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject
INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany INNER JOIN Tuserproject ON Tproject.idproject = Tuserproject.etridproject
WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) AND
 (Tuserproject.etridperson IN (SELECT idperson FROM Tperson WHERE login LIKE @.login))
ORDER BY Tproject.idproject
end
RETURN
GO

|||You can use a CASE statement in some situations, but it seems like you want to execute code within your BEGIN... ENDs. It won't work in those cases. I don't believe there is anything like Javascript's switch you can use to execute code like that. Depending on what you're trying to do, dynamic SQL might be an answer. Really though, more information is needed on what will happen based on each IF to really give a good answer.|||A CASE statement may work when you just need to change the WHERE clause, but it looks like you change the query as well. It looks like IF times 4 is the way to go.|||I second trying the CASE, but if you can't make it work then I'd make turn those inner batches into their own procs you'll prb' get better performance cause you won't suffer from the selective query plan issues of using IFs.|||Thanks for your comments.

Can you tell me more about your idea about "turn those inner batches into their own procs "
I never hear about that, do you have any sample or link for documentation?

Thanks|||There's no great mystery to it. Problem 1: One of the publicised problems with stored procedure plans is they can get "confused" by IF statements. I believe the problem is because it originally stores only one plan that represents only one path through the code. So the other IF conditions may not be ready to run. I've not tested it this myself but sounds plausable.

Problem 2: The more complicated the plan, the more chance the query optimiser has of making the wrong choices. I've certainly seen this. The more conditions in the proc seems to increase the likelyhood of the query plan having performance issues. Not suprising really because there is a slider between compiling optimal code and compiling the code quickly. So the longer it takes to compile the code the more likely the optimiser will throw its hands in the air.

Both of these problems can be helped by converting some of the query into other stored procedures. These have their own plans so the optimiser can, typically, do a better job.|||I agree with that post 100% and it's true that 4 significantly different queries will have a very good chance of not being run optimally. Create 4 procs and within your application code, decide which one to call based on what that parameter is at run time. The 4 procs will run more efficiently.