DB Backup Failure

Hello All,

I have created a Maintenance plan using Wizard, In this plan I am doing full backup of 12 DBs to share drive, I created only one step as full backup. And Scheduled this at 7 pm everyday, the
following is the error I am getting in the Logfile, can some one please through some light on this.

" failed with the following error: "Cannot open backup device
\\shq-ss2\sql backup\SQL_PRODDB\WorkFlow_backup_200708291911.bak. Operating
system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not
established correctly.

SQL Server Instance and SQL Agent are running under domain admin account, Share Drive folder has full control by domain admin.

I am the local admin on the SQL Server box, and I have read, write and delete, create permissions on the share drive for backups folder.

What must be the issue?

Thanks

Siebel

Can you try to run the backup of a db using the backup database syntax instead of job and tell us the results........if you have modify privilege in the backup folder it should work fine when you run the backup script as sql server will take your authentication (windows)..........in your case i guess its related to privileges for the shared folder for the startup account as the error is pretty expicit but if your sure try executing the script.........
|||

I am getting same error when I use the DB Backup syntax, I have full control on the shared folder.

What else could be the issue?

Thanks

Siebel

|||

How did you log on to the server, do you use SQL Server or Windows authentication ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

DB Backup Failure

Hello All,

I have created a Maintenance plan using Wizard, In this plan I am doing full backup of 12 DBs to share drive, I created only one step as full backup. And Scheduled this at 7 pm everyday, the
following is the error I am getting in the Logfile, can some one please through some light on this.

" failed with the following error: "Cannot open backup device
\\shq-ss2\sql backup\SQL_PRODDB\WorkFlow_backup_200708291911.bak. Operating
system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not
established correctly.

SQL Server Instance and SQL Agent are running under domain admin account, Share Drive folder has full control by domain admin.

I am the local admin on the SQL Server box, and I have read, write and delete, create permissions on the share drive for backups folder.

What must be the issue?

Thanks

Siebel

Can you try to run the backup of a db using the backup database syntax instead of job and tell us the results........if you have modify privilege in the backup folder it should work fine when you run the backup script as sql server will take your authentication (windows)..........in your case i guess its related to privileges for the shared folder for the startup account as the error is pretty expicit but if your sure try executing the script.........
|||

I am getting same error when I use the DB Backup syntax, I have full control on the shared folder.

What else could be the issue?

Thanks

Siebel

|||

How did you log on to the server, do you use SQL Server or Windows authentication ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

DB attaches as read only

Hello all,
I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there. It
attaches as Read Only.
When I go into the DB's properties and try to change the Read-only option, I get an error message:
Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
Could not restart database '(DB's name)'. Reverting back to old status.
ALTER DATABASE statement failed.
Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
sp_dboption command failed.
What am I doing wrong? How do I successfully detach from one & reattach to another?
Thanks for any help anyone can provide,
Conan Kelly
Conan Kelly wrote:
> Hello all,
> I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there. It
> attaches as Read Only.
> When I go into the DB's properties and try to change the Read-only option, I get an error message:
> Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
> Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
> Could not restart database '(DB's name)'. Reverting back to old status.
> ALTER DATABASE statement failed.
> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
> sp_dboption command failed.
> What am I doing wrong? How do I successfully detach from one & reattach to another?
>
Check the service account that SQL Server is running under, make sure
that account has read/write permissions on the folder that those
attached files are in.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Kelly,
Check what permissions are set for users on the folder where the
database files are placed. If the permissions are inadequate, then
grant the necessary permissions to the user account you logged in with.
Having done that you can proceed to attach the DB files again.
|||Tracy,
Thank you for the help.
It does look like permissions is the issue. When we moved the log/data files to SQL Servers default location (where tempdb, master,
model, Northwind, and pubs live), we can attach it just fine from there.
On further inspection, it appears that the SQL Server services are not running under its own (or even another's) domain user
account. It looks like it is running under the Local System account.
What are all the pro's & con's for using its own Domain User account vs using the Local System account?
I remember in my SQL Server Admin class, our instructor had us set up its own Domain User account and use that. I don't
think/remember if he explained why or if the book explains why, though.
Thanks again for all of your help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:454A4A41.6030007@.realsqlguy.com...
> Conan Kelly wrote:
> Check the service account that SQL Server is running under, make sure that account has read/write permissions on the folder that
> those attached files are in.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Conan Kelly wrote:
> Tracy,
> Thank you for the help.
> It does look like permissions is the issue. When we moved the log/data files to SQL Servers default location (where tempdb, master,
> model, Northwind, and pubs live), we can attach it just fine from there.
> On further inspection, it appears that the SQL Server services are not running under its own (or even another's) domain user
> account. It looks like it is running under the Local System account.
> What are all the pro's & con's for using its own Domain User account vs using the Local System account?
> I remember in my SQL Server Admin class, our instructor had us set up its own Domain User account and use that. I don't
> think/remember if he explained why or if the book explains why, though.
> Thanks again for all of your help,
> Conan
By running SQL under a domain account, you have greater control over
permissions. Also, if you need to access any network resources, for
doing backups to a remote drive for instance, you'll need to be running
under a domain account.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

DB and Log backup

Hello all,

We currently have disk space problem on our main SQL server (say server "A")
and I'l like to save DBs and logs backups on another server (say "B"). Is
that possible? I tried to set the backup path to point to B but it generate
an error. Serurity/Account issue? SS2K on A was running under the System
account. I tried to change it to an account that was created on both server.
Still an error.

Any help/suggestion would be appreciated.

Both server are running SS2000 SP3. Server A is W2K and B is NT4.

Thanks for your time.

Yannick"Yannick Turgeon" <nobody@.nowhere.com> wrote in message
news:U4cFd.34222$TN6.1120153@.news20.bellglobal.com ...
> Hello all,
> We currently have disk space problem on our main SQL server (say server
> "A") and I'l like to save DBs and logs backups on another server (say
> "B"). Is that possible? I tried to set the backup path to point to B but
> it generate an error. Serurity/Account issue? SS2K on A was running under
> the System account. I tried to change it to an account that was created on
> both server. Still an error.
> Any help/suggestion would be appreciated.
> Both server are running SS2000 SP3. Server A is W2K and B is NT4.
> Thanks for your time.
> Yannick

The MSSQL service account on server A needs to be a domain account with
access to the share on server B where you want to place the backups. Then a
command like this should work fine:

backup MyDB to disk = '\\serverB\share\folder\MyDB.bak

If you have a local account on each server with the same name and password,
then it will probably work too (I've never tried it myself), but using a
domain account would be easier anyway. There's more information about using
a domain account in Books Online under "Setting up Windows Services
Accounts" and "Changing Passwords and User Accounts".

If this doesn't help, it would be useful to know exactly what backup command
you're executing and what the error messages are.

Simon|||Hello Simon,

Your command worked. But I tried to make a backup from a Maintenance Plan
and it was this SQL command that was not working. After some comparaison
between both command I finally realised it was not working because I wrote
in the destination \\serverB\share instead of \\serverB\share\ (forgot the
trailing backslash).

Thanks for your help.

Yannick

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41e55501$1_1@.news.bluewin.ch...
> "Yannick Turgeon" <nobody@.nowhere.com> wrote in message
> news:U4cFd.34222$TN6.1120153@.news20.bellglobal.com ...
>> Hello all,
>>
>> We currently have disk space problem on our main SQL server (say server
>> "A") and I'l like to save DBs and logs backups on another server (say
>> "B"). Is that possible? I tried to set the backup path to point to B but
>> it generate an error. Serurity/Account issue? SS2K on A was running under
>> the System account. I tried to change it to an account that was created
>> on both server. Still an error.
>>
>> Any help/suggestion would be appreciated.
>>
>> Both server are running SS2000 SP3. Server A is W2K and B is NT4.
>>
>> Thanks for your time.
>>
>> Yannick
>>
> The MSSQL service account on server A needs to be a domain account with
> access to the share on server B where you want to place the backups. Then
> a command like this should work fine:
> backup MyDB to disk = '\\serverB\share\folder\MyDB.bak
> If you have a local account on each server with the same name and
> password, then it will probably work too (I've never tried it myself), but
> using a domain account would be easier anyway. There's more information
> about using a domain account in Books Online under "Setting up Windows
> Services Accounts" and "Changing Passwords and User Accounts".
> If this doesn't help, it would be useful to know exactly what backup
> command you're executing and what the error messages are.
> Simon

DB access revoked...

I have two servers (A & B) with 7 DBs and have created a
user on each server with DB_OWNER role.
When I restore a DB backup from one server A to B, the
user on server B, loses all its access to these 7 DBs from
a server level, though I can see that this user exists
from a DB perspective.
Any ideas why and how this can be corrected? Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0094_01C3810D.6E860260
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Check out sp_change_users_login in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Rob" <rhchin@.hotmail.com> wrote in message =news:0dfc01c3812e$29ce71e0$a001280a@.phx.gbl...
I have two servers (A & B) with 7 DBs and have created a user on each server with DB_OWNER role.
When I restore a DB backup from one server A to B, the user on server B, loses all its access to these 7 DBs from a server level, though I can see that this user exists from a DB perspective.
Any ideas why and how this can be corrected? Thanks.
--=_NextPart_000_0094_01C3810D.6E860260
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Check out sp_change_users_login in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Rob" wrote in message news:0dfc01c3812e$29=ce71e0$a001280a@.phx.gbl...I have two servers (A & B) with 7 DBs and have created a user on =each server with DB_OWNER role.When I restore a DB backup from one =server A to B, the user on server B, loses all its access to these 7 DBs from =a server level, though I can see that this user exists from a DB perspective.Any ideas why and how this can be corrected? Thanks.

---

--=_NextPart_000_0094_01C3810D.6E860260--