We have 4 servers in a workgroup environment. I have created 2 users (
SQLOwner and SQLAgent) on all the four servers with Admin privs. On all the
servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
Service.
I have created a DBMaintenance Plan on 3 servers to backup the user
databases directly onto Server 4 ( Use this directory ->
\\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running fine
(for 2 months). All of a sudden the jobs are failing. From the Job History I
see the following error ...
Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
I see the following error from SQLServer Logs ...
BackupDiskFile::CreateMedia: Backup device
'\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK ' failed to create.
Operating system error = 1330(Logon failure: the specified account password
has expired.).
I verified the password for the user SQLAgent (the owner SQLServer Agent
service) on all the servers and it is same. SQLAgent user is setup as non
expiring account.
When I tried to backup a single database (use this directory ->
\\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
verify backup file location. Do you want to use the backup file location
anyway ?"
RK
Look at
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/default...b;en-us;555128
Walk through the troubleshooting steps at the bottom. It is likely a
permissions issue or an expured account issue.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> We have 4 servers in a workgroup environment. I have created 2 users (
> SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> the
> servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
> Service.
> I have created a DBMaintenance Plan on 3 servers to backup the user
> databases directly onto Server 4 ( Use this directory ->
> \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running
> fine
> (for 2 months). All of a sudden the jobs are failing. From the Job History
> I
> see the following error ...
> Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> I see the following error from SQLServer Logs ...
> BackupDiskFile::CreateMedia: Backup device
> '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK ' failed to create.
> Operating system error = 1330(Logon failure: the specified account
> password
> has expired.).
> I verified the password for the user SQLAgent (the owner SQLServer Agent
> service) on all the servers and it is same. SQLAgent user is setup as non
> expiring account.
> When I tried to backup a single database (use this directory ->
> \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> verify backup file location. Do you want to use the backup file location
> anyway ?"
>
> --
> RK
|||Thanks fo r the quick Reply Geoff. I figured what the problem was. SQLOwner
account on SQL 03 server ( where all servers are writing their backups) was
expired. I changed it a non-expiring account
I thought for backup jobs we are only concerned about the user running the
SQLServerAgent service, which is SQLAgent user in my case.
"Geoff N. Hiten" wrote:
> Look at
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/default...b;en-us;555128
> Walk through the troubleshooting steps at the bottom. It is likely a
> permissions issue or an expured account issue.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
>
>
|||The SQL Agent invokes the backup command. The SQL engine actually runs the
command so you have to be concerned about the login account for the SQL
Server service, not the SQL Agent service.
GNH
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...[vbcol=seagreen]
> Thanks fo r the quick Reply Geoff. I figured what the problem was.
> SQLOwner
> account on SQL 03 server ( where all servers are writing their backups)
> was
> expired. I changed it a non-expiring account
> I thought for backup jobs we are only concerned about the user running the
> SQLServerAgent service, which is SQLAgent user in my case.
> "Geoff N. Hiten" wrote:
|||Thank you
"Geoff N. Hiten" wrote:
> The SQL Agent invokes the backup command. The SQL engine actually runs the
> command so you have to be concerned about the login account for the SQL
> Server service, not the SQL Agent service.
> GNH
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
>
>
|||I am backing up user databases on SQL04, but i am writing the backups to
SQL03 directly (use this directory -> \\SQL03\D$...). How does user account
on SQL03 expiring affect the backup job on SQL04 ?
In my case SQL03 happened to be another SQL Server. What is if the
destination server (SQL03) does not even have a SQL Serer running on it ?
"Geoff N. Hiten" wrote:
> The SQL Agent invokes the backup command. The SQL engine actually runs the
> command so you have to be concerned about the login account for the SQL
> Server service, not the SQL Agent service.
> GNH
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
>
>
|||The fact that the target server is a SQL server does not matter. It is the
SQL service account from the originating server that must have file system
rights to the target server. If the target server also happens to run a SQL
instance it does not matter.
GNH
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:067D0A4E-5B38-401D-98D5-E4C988DDCC8F@.microsoft.com...[vbcol=seagreen]
>I am backing up user databases on SQL04, but i am writing the backups to
> SQL03 directly (use this directory -> \\SQL03\D$...). How does user
> account
> on SQL03 expiring affect the backup job on SQL04 ?
> In my case SQL03 happened to be another SQL Server. What is if the
> destination server (SQL03) does not even have a SQL Serer running on it ?
> "Geoff N. Hiten" wrote:
|||It makes sense now. Thank you Geoff.
"Geoff N. Hiten" wrote:
> The fact that the target server is a SQL server does not matter. It is the
> SQL service account from the originating server that must have file system
> rights to the target server. If the target server also happens to run a SQL
> instance it does not matter.
> GNH
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:067D0A4E-5B38-401D-98D5-E4C988DDCC8F@.microsoft.com...
>
>
Showing posts with label maintenace. Show all posts
Showing posts with label maintenace. Show all posts
Thursday, February 16, 2012
Backup( thru DB Maintenace plan) to another server failing
We have 4 servers in a workgroup environment. I have created 2 users (
SQLOwner and SQLAgent) on all the four servers with Admin privs. On all the
servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
Service.
I have created a DBMaintenance Plan on 3 servers to backup the user
databases directly onto Server 4 ( Use this directory ->
\\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running fin
e
(for 2 months). All of a sudden the jobs are failing. From the Job History I
see the following error ...
Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
I see the following error from SQLServer Logs ...
BackupDiskFile::CreateMedia: Backup device
'\\SQL03\D$\SQLData\SQL04\ExIc_db_200503
150100.BAK' failed to create.
Operating system error = 1330(Logon failure: the specified account password
has expired.).
I verified the password for the user SQLAgent (the owner SQLServer Agent
service) on all the servers and it is same. SQLAgent user is setup as non
expiring account.
When I tried to backup a single database (use this directory ->
\\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
verify backup file location. Do you want to use the backup file location
anyway ?"
RKLook at
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/defaul...kb;en-us;555128
Walk through the troubleshooting steps at the bottom. It is likely a
permissions issue or an expured account issue.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> We have 4 servers in a workgroup environment. I have created 2 users (
> SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> the
> servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
> Service.
> I have created a DBMaintenance Plan on 3 servers to backup the user
> databases directly onto Server 4 ( Use this directory ->
> \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running
> fine
> (for 2 months). All of a sudden the jobs are failing. From the Job History
> I
> see the following error ...
> Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000
]
> (Error 22029). The step failed.
> I see the following error from SQLServer Logs ...
> BackupDiskFile::CreateMedia: Backup device
> '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503
150100.BAK' failed to create.
> Operating system error = 1330(Logon failure: the specified account
> password
> has expired.).
> I verified the password for the user SQLAgent (the owner SQLServer Agent
> service) on all the servers and it is same. SQLAgent user is setup as non
> expiring account.
> When I tried to backup a single database (use this directory ->
> \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> verify backup file location. Do you want to use the backup file location
> anyway ?"
>
> --
> RK|||Thanks fo r the quick Reply Geoff. I figured what the problem was. SQLOwner
account on SQL 03 server ( where all servers are writing their backups) was
expired. I changed it a non-expiring account
I thought for backup jobs we are only concerned about the user running the
SQLServerAgent service, which is SQLAgent user in my case.
"Geoff N. Hiten" wrote:
> Look at
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/defaul...kb;en-us;555128
> Walk through the troubleshooting steps at the bottom. It is likely a
> permissions issue or an expured account issue.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
>
>|||The SQL Agent invokes the backup command. The SQL engine actually runs the
command so you have to be concerned about the login account for the SQL
Server service, not the SQL Agent service.
GNH
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...[vbcol=seagreen]
> Thanks fo r the quick Reply Geoff. I figured what the problem was.
> SQLOwner
> account on SQL 03 server ( where all servers are writing their backups)
> was
> expired. I changed it a non-expiring account
> I thought for backup jobs we are only concerned about the user running the
> SQLServerAgent service, which is SQLAgent user in my case.
> "Geoff N. Hiten" wrote:
>|||Thank you
"Geoff N. Hiten" wrote:
> The SQL Agent invokes the backup command. The SQL engine actually runs th
e
> command so you have to be concerned about the login account for the SQL
> Server service, not the SQL Agent service.
> GNH
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
>
>|||I am backing up user databases on SQL04, but i am writing the backups to
SQL03 directly (use this directory -> \\SQL03\D$...). How does user account
on SQL03 expiring affect the backup job on SQL04 ?
In my case SQL03 happened to be another SQL Server. What is if the
destination server (SQL03) does not even have a SQL Serer running on it ?
"Geoff N. Hiten" wrote:
> The SQL Agent invokes the backup command. The SQL engine actually runs th
e
> command so you have to be concerned about the login account for the SQL
> Server service, not the SQL Agent service.
> GNH
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
>
>|||The fact that the target server is a SQL server does not matter. It is the
SQL service account from the originating server that must have file system
rights to the target server. If the target server also happens to run a SQL
instance it does not matter.
GNH
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:067D0A4E-5B38-401D-98D5-E4C988DDCC8F@.microsoft.com...[vbcol=seagreen]
>I am backing up user databases on SQL04, but i am writing the backups to
> SQL03 directly (use this directory -> \\SQL03\D$...). How does user
> account
> on SQL03 expiring affect the backup job on SQL04 ?
> In my case SQL03 happened to be another SQL Server. What is if the
> destination server (SQL03) does not even have a SQL Serer running on it ?
> "Geoff N. Hiten" wrote:
>|||It makes sense now. Thank you Geoff.
"Geoff N. Hiten" wrote:
> The fact that the target server is a SQL server does not matter. It is th
e
> SQL service account from the originating server that must have file system
> rights to the target server. If the target server also happens to run a S
QL
> instance it does not matter.
> GNH
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:067D0A4E-5B38-401D-98D5-E4C988DDCC8F@.microsoft.com...
>
>
SQLOwner and SQLAgent) on all the four servers with Admin privs. On all the
servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
Service.
I have created a DBMaintenance Plan on 3 servers to backup the user
databases directly onto Server 4 ( Use this directory ->
\\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running fin
e
(for 2 months). All of a sudden the jobs are failing. From the Job History I
see the following error ...
Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
I see the following error from SQLServer Logs ...
BackupDiskFile::CreateMedia: Backup device
'\\SQL03\D$\SQLData\SQL04\ExIc_db_200503
150100.BAK' failed to create.
Operating system error = 1330(Logon failure: the specified account password
has expired.).
I verified the password for the user SQLAgent (the owner SQLServer Agent
service) on all the servers and it is same. SQLAgent user is setup as non
expiring account.
When I tried to backup a single database (use this directory ->
\\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
verify backup file location. Do you want to use the backup file location
anyway ?"
RKLook at
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/defaul...kb;en-us;555128
Walk through the troubleshooting steps at the bottom. It is likely a
permissions issue or an expured account issue.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> We have 4 servers in a workgroup environment. I have created 2 users (
> SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> the
> servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
> Service.
> I have created a DBMaintenance Plan on 3 servers to backup the user
> databases directly onto Server 4 ( Use this directory ->
> \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running
> fine
> (for 2 months). All of a sudden the jobs are failing. From the Job History
> I
> see the following error ...
> Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000
]
> (Error 22029). The step failed.
> I see the following error from SQLServer Logs ...
> BackupDiskFile::CreateMedia: Backup device
> '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503
150100.BAK' failed to create.
> Operating system error = 1330(Logon failure: the specified account
> password
> has expired.).
> I verified the password for the user SQLAgent (the owner SQLServer Agent
> service) on all the servers and it is same. SQLAgent user is setup as non
> expiring account.
> When I tried to backup a single database (use this directory ->
> \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> verify backup file location. Do you want to use the backup file location
> anyway ?"
>
> --
> RK|||Thanks fo r the quick Reply Geoff. I figured what the problem was. SQLOwner
account on SQL 03 server ( where all servers are writing their backups) was
expired. I changed it a non-expiring account
I thought for backup jobs we are only concerned about the user running the
SQLServerAgent service, which is SQLAgent user in my case.
"Geoff N. Hiten" wrote:
> Look at
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/defaul...kb;en-us;555128
> Walk through the troubleshooting steps at the bottom. It is likely a
> permissions issue or an expured account issue.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
>
>|||The SQL Agent invokes the backup command. The SQL engine actually runs the
command so you have to be concerned about the login account for the SQL
Server service, not the SQL Agent service.
GNH
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...[vbcol=seagreen]
> Thanks fo r the quick Reply Geoff. I figured what the problem was.
> SQLOwner
> account on SQL 03 server ( where all servers are writing their backups)
> was
> expired. I changed it a non-expiring account
> I thought for backup jobs we are only concerned about the user running the
> SQLServerAgent service, which is SQLAgent user in my case.
> "Geoff N. Hiten" wrote:
>|||Thank you
"Geoff N. Hiten" wrote:
> The SQL Agent invokes the backup command. The SQL engine actually runs th
e
> command so you have to be concerned about the login account for the SQL
> Server service, not the SQL Agent service.
> GNH
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
>
>|||I am backing up user databases on SQL04, but i am writing the backups to
SQL03 directly (use this directory -> \\SQL03\D$...). How does user account
on SQL03 expiring affect the backup job on SQL04 ?
In my case SQL03 happened to be another SQL Server. What is if the
destination server (SQL03) does not even have a SQL Serer running on it ?
"Geoff N. Hiten" wrote:
> The SQL Agent invokes the backup command. The SQL engine actually runs th
e
> command so you have to be concerned about the login account for the SQL
> Server service, not the SQL Agent service.
> GNH
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
>
>|||The fact that the target server is a SQL server does not matter. It is the
SQL service account from the originating server that must have file system
rights to the target server. If the target server also happens to run a SQL
instance it does not matter.
GNH
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:067D0A4E-5B38-401D-98D5-E4C988DDCC8F@.microsoft.com...[vbcol=seagreen]
>I am backing up user databases on SQL04, but i am writing the backups to
> SQL03 directly (use this directory -> \\SQL03\D$...). How does user
> account
> on SQL03 expiring affect the backup job on SQL04 ?
> In my case SQL03 happened to be another SQL Server. What is if the
> destination server (SQL03) does not even have a SQL Serer running on it ?
> "Geoff N. Hiten" wrote:
>|||It makes sense now. Thank you Geoff.
"Geoff N. Hiten" wrote:
> The fact that the target server is a SQL server does not matter. It is th
e
> SQL service account from the originating server that must have file system
> rights to the target server. If the target server also happens to run a S
QL
> instance it does not matter.
> GNH
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:067D0A4E-5B38-401D-98D5-E4C988DDCC8F@.microsoft.com...
>
>
Backup( thru DB Maintenace plan) to another server failing
We have 4 servers in a workgroup environment. I have created 2 users (
SQLOwner and SQLAgent) on all the four servers with Admin privs. On all the
servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
Service.
I have created a DBMaintenance Plan on 3 servers to backup the user
databases directly onto Server 4 ( Use this directory ->
\\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running fine
(for 2 months). All of a sudden the jobs are failing. From the Job History I
see the following error ...
Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
I see the following error from SQLServer Logs ...
BackupDiskFile::CreateMedia: Backup device
'\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
Operating system error = 1330(Logon failure: the specified account password
has expired.).
I verified the password for the user SQLAgent (the owner SQLServer Agent
service) on all the servers and it is same. SQLAgent user is setup as non
expiring account.
When I tried to backup a single database (use this directory ->
\\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
verify backup file location. Do you want to use the backup file location
anyway ?"
--
RKLook at
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
Walk through the troubleshooting steps at the bottom. It is likely a
permissions issue or an expured account issue.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> We have 4 servers in a workgroup environment. I have created 2 users (
> SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> the
> servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
> Service.
> I have created a DBMaintenance Plan on 3 servers to backup the user
> databases directly onto Server 4 ( Use this directory ->
> \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running
> fine
> (for 2 months). All of a sudden the jobs are failing. From the Job History
> I
> see the following error ...
> Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> I see the following error from SQLServer Logs ...
> BackupDiskFile::CreateMedia: Backup device
> '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
> Operating system error = 1330(Logon failure: the specified account
> password
> has expired.).
> I verified the password for the user SQLAgent (the owner SQLServer Agent
> service) on all the servers and it is same. SQLAgent user is setup as non
> expiring account.
> When I tried to backup a single database (use this directory ->
> \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> verify backup file location. Do you want to use the backup file location
> anyway ?"
>
> --
> RK|||Thanks fo r the quick Reply Geoff. I figured what the problem was. SQLOwner
account on SQL 03 server ( where all servers are writing their backups) was
expired. I changed it a non-expiring account
I thought for backup jobs we are only concerned about the user running the
SQLServerAgent service, which is SQLAgent user in my case.
"Geoff N. Hiten" wrote:
> Look at
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
> Walk through the troubleshooting steps at the bottom. It is likely a
> permissions issue or an expured account issue.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> > We have 4 servers in a workgroup environment. I have created 2 users (
> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> > the
> > servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
> > Service.
> >
> > I have created a DBMaintenance Plan on 3 servers to backup the user
> > databases directly onto Server 4 ( Use this directory ->
> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running
> > fine
> > (for 2 months). All of a sudden the jobs are failing. From the Job History
> > I
> > see the following error ...
> >
> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
> > (Error 22029). The step failed.
> >
> > I see the following error from SQLServer Logs ...
> >
> > BackupDiskFile::CreateMedia: Backup device
> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
> > Operating system error = 1330(Logon failure: the specified account
> > password
> > has expired.).
> >
> > I verified the password for the user SQLAgent (the owner SQLServer Agent
> > service) on all the servers and it is same. SQLAgent user is setup as non
> > expiring account.
> >
> > When I tried to backup a single database (use this directory ->
> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> > verify backup file location. Do you want to use the backup file location
> > anyway ?"
> >
> >
> > --
> > RK
>
>|||The SQL Agent invokes the backup command. The SQL engine actually runs the
command so you have to be concerned about the login account for the SQL
Server service, not the SQL Agent service.
GNH
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
> Thanks fo r the quick Reply Geoff. I figured what the problem was.
> SQLOwner
> account on SQL 03 server ( where all servers are writing their backups)
> was
> expired. I changed it a non-expiring account
> I thought for backup jobs we are only concerned about the user running the
> SQLServerAgent service, which is SQLAgent user in my case.
> "Geoff N. Hiten" wrote:
>> Look at
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
>> Walk through the troubleshooting steps at the bottom. It is likely a
>> permissions issue or an expured account issue.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>>
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
>> > We have 4 servers in a workgroup environment. I have created 2 users (
>> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
>> > the
>> > servers SQLOwner runs MSSQLServer service and SQLAgent run
>> > SQLSERVERAgent
>> > Service.
>> >
>> > I have created a DBMaintenance Plan on 3 servers to backup the user
>> > databases directly onto Server 4 ( Use this directory ->
>> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
>> > running
>> > fine
>> > (for 2 months). All of a sudden the jobs are failing. From the Job
>> > History
>> > I
>> > see the following error ...
>> >
>> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
>> > (Error 22029). The step failed.
>> >
>> > I see the following error from SQLServer Logs ...
>> >
>> > BackupDiskFile::CreateMedia: Backup device
>> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
>> > Operating system error = 1330(Logon failure: the specified account
>> > password
>> > has expired.).
>> >
>> > I verified the password for the user SQLAgent (the owner SQLServer
>> > Agent
>> > service) on all the servers and it is same. SQLAgent user is setup as
>> > non
>> > expiring account.
>> >
>> > When I tried to backup a single database (use this directory ->
>> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
>> > verify backup file location. Do you want to use the backup file
>> > location
>> > anyway ?"
>> >
>> >
>> > --
>> > RK
>>|||Thank you
"Geoff N. Hiten" wrote:
> The SQL Agent invokes the backup command. The SQL engine actually runs the
> command so you have to be concerned about the login account for the SQL
> Server service, not the SQL Agent service.
> GNH
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
> > Thanks fo r the quick Reply Geoff. I figured what the problem was.
> > SQLOwner
> > account on SQL 03 server ( where all servers are writing their backups)
> > was
> > expired. I changed it a non-expiring account
> > I thought for backup jobs we are only concerned about the user running the
> > SQLServerAgent service, which is SQLAgent user in my case.
> >
> > "Geoff N. Hiten" wrote:
> >
> >> Look at
> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
> >>
> >> Walk through the troubleshooting steps at the bottom. It is likely a
> >> permissions issue or an expured account issue.
> >>
> >> Geoff N. Hiten
> >> Microsoft SQL Server MVP
> >> Senior Database Administrator
> >>
> >>
> >>
> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> >> > We have 4 servers in a workgroup environment. I have created 2 users (
> >> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> >> > the
> >> > servers SQLOwner runs MSSQLServer service and SQLAgent run
> >> > SQLSERVERAgent
> >> > Service.
> >> >
> >> > I have created a DBMaintenance Plan on 3 servers to backup the user
> >> > databases directly onto Server 4 ( Use this directory ->
> >> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
> >> > running
> >> > fine
> >> > (for 2 months). All of a sudden the jobs are failing. From the Job
> >> > History
> >> > I
> >> > see the following error ...
> >> >
> >> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
> >> > (Error 22029). The step failed.
> >> >
> >> > I see the following error from SQLServer Logs ...
> >> >
> >> > BackupDiskFile::CreateMedia: Backup device
> >> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
> >> > Operating system error = 1330(Logon failure: the specified account
> >> > password
> >> > has expired.).
> >> >
> >> > I verified the password for the user SQLAgent (the owner SQLServer
> >> > Agent
> >> > service) on all the servers and it is same. SQLAgent user is setup as
> >> > non
> >> > expiring account.
> >> >
> >> > When I tried to backup a single database (use this directory ->
> >> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> >> > verify backup file location. Do you want to use the backup file
> >> > location
> >> > anyway ?"
> >> >
> >> >
> >> > --
> >> > RK
> >>
> >>
> >>
>
>|||I am backing up user databases on SQL04, but i am writing the backups to
SQL03 directly (use this directory -> \\SQL03\D$...). How does user account
on SQL03 expiring affect the backup job on SQL04 ?
In my case SQL03 happened to be another SQL Server. What is if the
destination server (SQL03) does not even have a SQL Serer running on it ?
"Geoff N. Hiten" wrote:
> The SQL Agent invokes the backup command. The SQL engine actually runs the
> command so you have to be concerned about the login account for the SQL
> Server service, not the SQL Agent service.
> GNH
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
> > Thanks fo r the quick Reply Geoff. I figured what the problem was.
> > SQLOwner
> > account on SQL 03 server ( where all servers are writing their backups)
> > was
> > expired. I changed it a non-expiring account
> > I thought for backup jobs we are only concerned about the user running the
> > SQLServerAgent service, which is SQLAgent user in my case.
> >
> > "Geoff N. Hiten" wrote:
> >
> >> Look at
> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
> >>
> >> Walk through the troubleshooting steps at the bottom. It is likely a
> >> permissions issue or an expured account issue.
> >>
> >> Geoff N. Hiten
> >> Microsoft SQL Server MVP
> >> Senior Database Administrator
> >>
> >>
> >>
> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> >> > We have 4 servers in a workgroup environment. I have created 2 users (
> >> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> >> > the
> >> > servers SQLOwner runs MSSQLServer service and SQLAgent run
> >> > SQLSERVERAgent
> >> > Service.
> >> >
> >> > I have created a DBMaintenance Plan on 3 servers to backup the user
> >> > databases directly onto Server 4 ( Use this directory ->
> >> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
> >> > running
> >> > fine
> >> > (for 2 months). All of a sudden the jobs are failing. From the Job
> >> > History
> >> > I
> >> > see the following error ...
> >> >
> >> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
> >> > (Error 22029). The step failed.
> >> >
> >> > I see the following error from SQLServer Logs ...
> >> >
> >> > BackupDiskFile::CreateMedia: Backup device
> >> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
> >> > Operating system error = 1330(Logon failure: the specified account
> >> > password
> >> > has expired.).
> >> >
> >> > I verified the password for the user SQLAgent (the owner SQLServer
> >> > Agent
> >> > service) on all the servers and it is same. SQLAgent user is setup as
> >> > non
> >> > expiring account.
> >> >
> >> > When I tried to backup a single database (use this directory ->
> >> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> >> > verify backup file location. Do you want to use the backup file
> >> > location
> >> > anyway ?"
> >> >
> >> >
> >> > --
> >> > RK
> >>
> >>
> >>
>
>|||The fact that the target server is a SQL server does not matter. It is the
SQL service account from the originating server that must have file system
rights to the target server. If the target server also happens to run a SQL
instance it does not matter.
GNH
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:067D0A4E-5B38-401D-98D5-E4C988DDCC8F@.microsoft.com...
>I am backing up user databases on SQL04, but i am writing the backups to
> SQL03 directly (use this directory -> \\SQL03\D$...). How does user
> account
> on SQL03 expiring affect the backup job on SQL04 ?
> In my case SQL03 happened to be another SQL Server. What is if the
> destination server (SQL03) does not even have a SQL Serer running on it ?
> "Geoff N. Hiten" wrote:
>> The SQL Agent invokes the backup command. The SQL engine actually runs
>> the
>> command so you have to be concerned about the login account for the SQL
>> Server service, not the SQL Agent service.
>> GNH
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
>> > Thanks fo r the quick Reply Geoff. I figured what the problem was.
>> > SQLOwner
>> > account on SQL 03 server ( where all servers are writing their backups)
>> > was
>> > expired. I changed it a non-expiring account
>> > I thought for backup jobs we are only concerned about the user running
>> > the
>> > SQLServerAgent service, which is SQLAgent user in my case.
>> >
>> > "Geoff N. Hiten" wrote:
>> >
>> >> Look at
>> >> HowTo: Backup to UNC name using Database Maintenance Wizard
>> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
>> >>
>> >> Walk through the troubleshooting steps at the bottom. It is likely a
>> >> permissions issue or an expured account issue.
>> >>
>> >> Geoff N. Hiten
>> >> Microsoft SQL Server MVP
>> >> Senior Database Administrator
>> >>
>> >>
>> >>
>> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> >> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
>> >> > We have 4 servers in a workgroup environment. I have created 2 users
>> >> > (
>> >> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On
>> >> > all
>> >> > the
>> >> > servers SQLOwner runs MSSQLServer service and SQLAgent run
>> >> > SQLSERVERAgent
>> >> > Service.
>> >> >
>> >> > I have created a DBMaintenance Plan on 3 servers to backup the user
>> >> > databases directly onto Server 4 ( Use this directory ->
>> >> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
>> >> > running
>> >> > fine
>> >> > (for 2 months). All of a sudden the jobs are failing. From the Job
>> >> > History
>> >> > I
>> >> > see the following error ...
>> >> >
>> >> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE
>> >> > 42000]
>> >> > (Error 22029). The step failed.
>> >> >
>> >> > I see the following error from SQLServer Logs ...
>> >> >
>> >> > BackupDiskFile::CreateMedia: Backup device
>> >> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to
>> >> > create.
>> >> > Operating system error = 1330(Logon failure: the specified account
>> >> > password
>> >> > has expired.).
>> >> >
>> >> > I verified the password for the user SQLAgent (the owner SQLServer
>> >> > Agent
>> >> > service) on all the servers and it is same. SQLAgent user is setup
>> >> > as
>> >> > non
>> >> > expiring account.
>> >> >
>> >> > When I tried to backup a single database (use this directory ->
>> >> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to
>> >> > to
>> >> > verify backup file location. Do you want to use the backup file
>> >> > location
>> >> > anyway ?"
>> >> >
>> >> >
>> >> > --
>> >> > RK
>> >>
>> >>
>> >>
>>|||It makes sense now. Thank you Geoff.
"Geoff N. Hiten" wrote:
> The fact that the target server is a SQL server does not matter. It is the
> SQL service account from the originating server that must have file system
> rights to the target server. If the target server also happens to run a SQL
> instance it does not matter.
> GNH
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:067D0A4E-5B38-401D-98D5-E4C988DDCC8F@.microsoft.com...
> >I am backing up user databases on SQL04, but i am writing the backups to
> > SQL03 directly (use this directory -> \\SQL03\D$...). How does user
> > account
> > on SQL03 expiring affect the backup job on SQL04 ?
> >
> > In my case SQL03 happened to be another SQL Server. What is if the
> > destination server (SQL03) does not even have a SQL Serer running on it ?
> >
> > "Geoff N. Hiten" wrote:
> >
> >> The SQL Agent invokes the backup command. The SQL engine actually runs
> >> the
> >> command so you have to be concerned about the login account for the SQL
> >> Server service, not the SQL Agent service.
> >>
> >> GNH
> >>
> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
> >> > Thanks fo r the quick Reply Geoff. I figured what the problem was.
> >> > SQLOwner
> >> > account on SQL 03 server ( where all servers are writing their backups)
> >> > was
> >> > expired. I changed it a non-expiring account
> >> > I thought for backup jobs we are only concerned about the user running
> >> > the
> >> > SQLServerAgent service, which is SQLAgent user in my case.
> >> >
> >> > "Geoff N. Hiten" wrote:
> >> >
> >> >> Look at
> >> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
> >> >>
> >> >> Walk through the troubleshooting steps at the bottom. It is likely a
> >> >> permissions issue or an expured account issue.
> >> >>
> >> >> Geoff N. Hiten
> >> >> Microsoft SQL Server MVP
> >> >> Senior Database Administrator
> >> >>
> >> >>
> >> >>
> >> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> >> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> >> >> > We have 4 servers in a workgroup environment. I have created 2 users
> >> >> > (
> >> >> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On
> >> >> > all
> >> >> > the
> >> >> > servers SQLOwner runs MSSQLServer service and SQLAgent run
> >> >> > SQLSERVERAgent
> >> >> > Service.
> >> >> >
> >> >> > I have created a DBMaintenance Plan on 3 servers to backup the user
> >> >> > databases directly onto Server 4 ( Use this directory ->
> >> >> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
> >> >> > running
> >> >> > fine
> >> >> > (for 2 months). All of a sudden the jobs are failing. From the Job
> >> >> > History
> >> >> > I
> >> >> > see the following error ...
> >> >> >
> >> >> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE
> >> >> > 42000]
> >> >> > (Error 22029). The step failed.
> >> >> >
> >> >> > I see the following error from SQLServer Logs ...
> >> >> >
> >> >> > BackupDiskFile::CreateMedia: Backup device
> >> >> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to
> >> >> > create.
> >> >> > Operating system error = 1330(Logon failure: the specified account
> >> >> > password
> >> >> > has expired.).
> >> >> >
> >> >> > I verified the password for the user SQLAgent (the owner SQLServer
> >> >> > Agent
> >> >> > service) on all the servers and it is same. SQLAgent user is setup
> >> >> > as
> >> >> > non
> >> >> > expiring account.
> >> >> >
> >> >> > When I tried to backup a single database (use this directory ->
> >> >> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to
> >> >> > to
> >> >> > verify backup file location. Do you want to use the backup file
> >> >> > location
> >> >> > anyway ?"
> >> >> >
> >> >> >
> >> >> > --
> >> >> > RK
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
SQLOwner and SQLAgent) on all the four servers with Admin privs. On all the
servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
Service.
I have created a DBMaintenance Plan on 3 servers to backup the user
databases directly onto Server 4 ( Use this directory ->
\\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running fine
(for 2 months). All of a sudden the jobs are failing. From the Job History I
see the following error ...
Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
I see the following error from SQLServer Logs ...
BackupDiskFile::CreateMedia: Backup device
'\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
Operating system error = 1330(Logon failure: the specified account password
has expired.).
I verified the password for the user SQLAgent (the owner SQLServer Agent
service) on all the servers and it is same. SQLAgent user is setup as non
expiring account.
When I tried to backup a single database (use this directory ->
\\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
verify backup file location. Do you want to use the backup file location
anyway ?"
--
RKLook at
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
Walk through the troubleshooting steps at the bottom. It is likely a
permissions issue or an expured account issue.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> We have 4 servers in a workgroup environment. I have created 2 users (
> SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> the
> servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
> Service.
> I have created a DBMaintenance Plan on 3 servers to backup the user
> databases directly onto Server 4 ( Use this directory ->
> \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running
> fine
> (for 2 months). All of a sudden the jobs are failing. From the Job History
> I
> see the following error ...
> Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> I see the following error from SQLServer Logs ...
> BackupDiskFile::CreateMedia: Backup device
> '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
> Operating system error = 1330(Logon failure: the specified account
> password
> has expired.).
> I verified the password for the user SQLAgent (the owner SQLServer Agent
> service) on all the servers and it is same. SQLAgent user is setup as non
> expiring account.
> When I tried to backup a single database (use this directory ->
> \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> verify backup file location. Do you want to use the backup file location
> anyway ?"
>
> --
> RK|||Thanks fo r the quick Reply Geoff. I figured what the problem was. SQLOwner
account on SQL 03 server ( where all servers are writing their backups) was
expired. I changed it a non-expiring account
I thought for backup jobs we are only concerned about the user running the
SQLServerAgent service, which is SQLAgent user in my case.
"Geoff N. Hiten" wrote:
> Look at
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
> Walk through the troubleshooting steps at the bottom. It is likely a
> permissions issue or an expured account issue.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> > We have 4 servers in a workgroup environment. I have created 2 users (
> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> > the
> > servers SQLOwner runs MSSQLServer service and SQLAgent run SQLSERVERAgent
> > Service.
> >
> > I have created a DBMaintenance Plan on 3 servers to backup the user
> > databases directly onto Server 4 ( Use this directory ->
> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were running
> > fine
> > (for 2 months). All of a sudden the jobs are failing. From the Job History
> > I
> > see the following error ...
> >
> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
> > (Error 22029). The step failed.
> >
> > I see the following error from SQLServer Logs ...
> >
> > BackupDiskFile::CreateMedia: Backup device
> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
> > Operating system error = 1330(Logon failure: the specified account
> > password
> > has expired.).
> >
> > I verified the password for the user SQLAgent (the owner SQLServer Agent
> > service) on all the servers and it is same. SQLAgent user is setup as non
> > expiring account.
> >
> > When I tried to backup a single database (use this directory ->
> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> > verify backup file location. Do you want to use the backup file location
> > anyway ?"
> >
> >
> > --
> > RK
>
>|||The SQL Agent invokes the backup command. The SQL engine actually runs the
command so you have to be concerned about the login account for the SQL
Server service, not the SQL Agent service.
GNH
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
> Thanks fo r the quick Reply Geoff. I figured what the problem was.
> SQLOwner
> account on SQL 03 server ( where all servers are writing their backups)
> was
> expired. I changed it a non-expiring account
> I thought for backup jobs we are only concerned about the user running the
> SQLServerAgent service, which is SQLAgent user in my case.
> "Geoff N. Hiten" wrote:
>> Look at
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
>> Walk through the troubleshooting steps at the bottom. It is likely a
>> permissions issue or an expured account issue.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>>
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
>> > We have 4 servers in a workgroup environment. I have created 2 users (
>> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
>> > the
>> > servers SQLOwner runs MSSQLServer service and SQLAgent run
>> > SQLSERVERAgent
>> > Service.
>> >
>> > I have created a DBMaintenance Plan on 3 servers to backup the user
>> > databases directly onto Server 4 ( Use this directory ->
>> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
>> > running
>> > fine
>> > (for 2 months). All of a sudden the jobs are failing. From the Job
>> > History
>> > I
>> > see the following error ...
>> >
>> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
>> > (Error 22029). The step failed.
>> >
>> > I see the following error from SQLServer Logs ...
>> >
>> > BackupDiskFile::CreateMedia: Backup device
>> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
>> > Operating system error = 1330(Logon failure: the specified account
>> > password
>> > has expired.).
>> >
>> > I verified the password for the user SQLAgent (the owner SQLServer
>> > Agent
>> > service) on all the servers and it is same. SQLAgent user is setup as
>> > non
>> > expiring account.
>> >
>> > When I tried to backup a single database (use this directory ->
>> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
>> > verify backup file location. Do you want to use the backup file
>> > location
>> > anyway ?"
>> >
>> >
>> > --
>> > RK
>>|||Thank you
"Geoff N. Hiten" wrote:
> The SQL Agent invokes the backup command. The SQL engine actually runs the
> command so you have to be concerned about the login account for the SQL
> Server service, not the SQL Agent service.
> GNH
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
> > Thanks fo r the quick Reply Geoff. I figured what the problem was.
> > SQLOwner
> > account on SQL 03 server ( where all servers are writing their backups)
> > was
> > expired. I changed it a non-expiring account
> > I thought for backup jobs we are only concerned about the user running the
> > SQLServerAgent service, which is SQLAgent user in my case.
> >
> > "Geoff N. Hiten" wrote:
> >
> >> Look at
> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
> >>
> >> Walk through the troubleshooting steps at the bottom. It is likely a
> >> permissions issue or an expured account issue.
> >>
> >> Geoff N. Hiten
> >> Microsoft SQL Server MVP
> >> Senior Database Administrator
> >>
> >>
> >>
> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> >> > We have 4 servers in a workgroup environment. I have created 2 users (
> >> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> >> > the
> >> > servers SQLOwner runs MSSQLServer service and SQLAgent run
> >> > SQLSERVERAgent
> >> > Service.
> >> >
> >> > I have created a DBMaintenance Plan on 3 servers to backup the user
> >> > databases directly onto Server 4 ( Use this directory ->
> >> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
> >> > running
> >> > fine
> >> > (for 2 months). All of a sudden the jobs are failing. From the Job
> >> > History
> >> > I
> >> > see the following error ...
> >> >
> >> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
> >> > (Error 22029). The step failed.
> >> >
> >> > I see the following error from SQLServer Logs ...
> >> >
> >> > BackupDiskFile::CreateMedia: Backup device
> >> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
> >> > Operating system error = 1330(Logon failure: the specified account
> >> > password
> >> > has expired.).
> >> >
> >> > I verified the password for the user SQLAgent (the owner SQLServer
> >> > Agent
> >> > service) on all the servers and it is same. SQLAgent user is setup as
> >> > non
> >> > expiring account.
> >> >
> >> > When I tried to backup a single database (use this directory ->
> >> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> >> > verify backup file location. Do you want to use the backup file
> >> > location
> >> > anyway ?"
> >> >
> >> >
> >> > --
> >> > RK
> >>
> >>
> >>
>
>|||I am backing up user databases on SQL04, but i am writing the backups to
SQL03 directly (use this directory -> \\SQL03\D$...). How does user account
on SQL03 expiring affect the backup job on SQL04 ?
In my case SQL03 happened to be another SQL Server. What is if the
destination server (SQL03) does not even have a SQL Serer running on it ?
"Geoff N. Hiten" wrote:
> The SQL Agent invokes the backup command. The SQL engine actually runs the
> command so you have to be concerned about the login account for the SQL
> Server service, not the SQL Agent service.
> GNH
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
> > Thanks fo r the quick Reply Geoff. I figured what the problem was.
> > SQLOwner
> > account on SQL 03 server ( where all servers are writing their backups)
> > was
> > expired. I changed it a non-expiring account
> > I thought for backup jobs we are only concerned about the user running the
> > SQLServerAgent service, which is SQLAgent user in my case.
> >
> > "Geoff N. Hiten" wrote:
> >
> >> Look at
> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
> >>
> >> Walk through the troubleshooting steps at the bottom. It is likely a
> >> permissions issue or an expured account issue.
> >>
> >> Geoff N. Hiten
> >> Microsoft SQL Server MVP
> >> Senior Database Administrator
> >>
> >>
> >>
> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> >> > We have 4 servers in a workgroup environment. I have created 2 users (
> >> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On all
> >> > the
> >> > servers SQLOwner runs MSSQLServer service and SQLAgent run
> >> > SQLSERVERAgent
> >> > Service.
> >> >
> >> > I have created a DBMaintenance Plan on 3 servers to backup the user
> >> > databases directly onto Server 4 ( Use this directory ->
> >> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
> >> > running
> >> > fine
> >> > (for 2 months). All of a sudden the jobs are failing. From the Job
> >> > History
> >> > I
> >> > see the following error ...
> >> >
> >> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE 42000]
> >> > (Error 22029). The step failed.
> >> >
> >> > I see the following error from SQLServer Logs ...
> >> >
> >> > BackupDiskFile::CreateMedia: Backup device
> >> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to create.
> >> > Operating system error = 1330(Logon failure: the specified account
> >> > password
> >> > has expired.).
> >> >
> >> > I verified the password for the user SQLAgent (the owner SQLServer
> >> > Agent
> >> > service) on all the servers and it is same. SQLAgent user is setup as
> >> > non
> >> > expiring account.
> >> >
> >> > When I tried to backup a single database (use this directory ->
> >> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to to
> >> > verify backup file location. Do you want to use the backup file
> >> > location
> >> > anyway ?"
> >> >
> >> >
> >> > --
> >> > RK
> >>
> >>
> >>
>
>|||The fact that the target server is a SQL server does not matter. It is the
SQL service account from the originating server that must have file system
rights to the target server. If the target server also happens to run a SQL
instance it does not matter.
GNH
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:067D0A4E-5B38-401D-98D5-E4C988DDCC8F@.microsoft.com...
>I am backing up user databases on SQL04, but i am writing the backups to
> SQL03 directly (use this directory -> \\SQL03\D$...). How does user
> account
> on SQL03 expiring affect the backup job on SQL04 ?
> In my case SQL03 happened to be another SQL Server. What is if the
> destination server (SQL03) does not even have a SQL Serer running on it ?
> "Geoff N. Hiten" wrote:
>> The SQL Agent invokes the backup command. The SQL engine actually runs
>> the
>> command so you have to be concerned about the login account for the SQL
>> Server service, not the SQL Agent service.
>> GNH
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
>> > Thanks fo r the quick Reply Geoff. I figured what the problem was.
>> > SQLOwner
>> > account on SQL 03 server ( where all servers are writing their backups)
>> > was
>> > expired. I changed it a non-expiring account
>> > I thought for backup jobs we are only concerned about the user running
>> > the
>> > SQLServerAgent service, which is SQLAgent user in my case.
>> >
>> > "Geoff N. Hiten" wrote:
>> >
>> >> Look at
>> >> HowTo: Backup to UNC name using Database Maintenance Wizard
>> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
>> >>
>> >> Walk through the troubleshooting steps at the bottom. It is likely a
>> >> permissions issue or an expured account issue.
>> >>
>> >> Geoff N. Hiten
>> >> Microsoft SQL Server MVP
>> >> Senior Database Administrator
>> >>
>> >>
>> >>
>> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> >> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
>> >> > We have 4 servers in a workgroup environment. I have created 2 users
>> >> > (
>> >> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On
>> >> > all
>> >> > the
>> >> > servers SQLOwner runs MSSQLServer service and SQLAgent run
>> >> > SQLSERVERAgent
>> >> > Service.
>> >> >
>> >> > I have created a DBMaintenance Plan on 3 servers to backup the user
>> >> > databases directly onto Server 4 ( Use this directory ->
>> >> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
>> >> > running
>> >> > fine
>> >> > (for 2 months). All of a sudden the jobs are failing. From the Job
>> >> > History
>> >> > I
>> >> > see the following error ...
>> >> >
>> >> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE
>> >> > 42000]
>> >> > (Error 22029). The step failed.
>> >> >
>> >> > I see the following error from SQLServer Logs ...
>> >> >
>> >> > BackupDiskFile::CreateMedia: Backup device
>> >> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to
>> >> > create.
>> >> > Operating system error = 1330(Logon failure: the specified account
>> >> > password
>> >> > has expired.).
>> >> >
>> >> > I verified the password for the user SQLAgent (the owner SQLServer
>> >> > Agent
>> >> > service) on all the servers and it is same. SQLAgent user is setup
>> >> > as
>> >> > non
>> >> > expiring account.
>> >> >
>> >> > When I tried to backup a single database (use this directory ->
>> >> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to
>> >> > to
>> >> > verify backup file location. Do you want to use the backup file
>> >> > location
>> >> > anyway ?"
>> >> >
>> >> >
>> >> > --
>> >> > RK
>> >>
>> >>
>> >>
>>|||It makes sense now. Thank you Geoff.
"Geoff N. Hiten" wrote:
> The fact that the target server is a SQL server does not matter. It is the
> SQL service account from the originating server that must have file system
> rights to the target server. If the target server also happens to run a SQL
> instance it does not matter.
> GNH
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:067D0A4E-5B38-401D-98D5-E4C988DDCC8F@.microsoft.com...
> >I am backing up user databases on SQL04, but i am writing the backups to
> > SQL03 directly (use this directory -> \\SQL03\D$...). How does user
> > account
> > on SQL03 expiring affect the backup job on SQL04 ?
> >
> > In my case SQL03 happened to be another SQL Server. What is if the
> > destination server (SQL03) does not even have a SQL Serer running on it ?
> >
> > "Geoff N. Hiten" wrote:
> >
> >> The SQL Agent invokes the backup command. The SQL engine actually runs
> >> the
> >> command so you have to be concerned about the login account for the SQL
> >> Server service, not the SQL Agent service.
> >>
> >> GNH
> >>
> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> news:2A6987EE-85C1-4DA8-A12E-21FA8B4CB269@.microsoft.com...
> >> > Thanks fo r the quick Reply Geoff. I figured what the problem was.
> >> > SQLOwner
> >> > account on SQL 03 server ( where all servers are writing their backups)
> >> > was
> >> > expired. I changed it a non-expiring account
> >> > I thought for backup jobs we are only concerned about the user running
> >> > the
> >> > SQLServerAgent service, which is SQLAgent user in my case.
> >> >
> >> > "Geoff N. Hiten" wrote:
> >> >
> >> >> Look at
> >> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
> >> >>
> >> >> Walk through the troubleshooting steps at the bottom. It is likely a
> >> >> permissions issue or an expured account issue.
> >> >>
> >> >> Geoff N. Hiten
> >> >> Microsoft SQL Server MVP
> >> >> Senior Database Administrator
> >> >>
> >> >>
> >> >>
> >> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> >> news:92226F4B-E76A-42F5-8418-9DA19CCD314F@.microsoft.com...
> >> >> > We have 4 servers in a workgroup environment. I have created 2 users
> >> >> > (
> >> >> > SQLOwner and SQLAgent) on all the four servers with Admin privs. On
> >> >> > all
> >> >> > the
> >> >> > servers SQLOwner runs MSSQLServer service and SQLAgent run
> >> >> > SQLSERVERAgent
> >> >> > Service.
> >> >> >
> >> >> > I have created a DBMaintenance Plan on 3 servers to backup the user
> >> >> > databases directly onto Server 4 ( Use this directory ->
> >> >> > \\SQL03\D$\SQLData...). Until two days ago, the backup jobs were
> >> >> > running
> >> >> > fine
> >> >> > (for 2 months). All of a sudden the jobs are failing. From the Job
> >> >> > History
> >> >> > I
> >> >> > see the following error ...
> >> >> >
> >> >> > Executed as user: SQL04\SQLAgent. sqlmaint.exe failed. [SQLSTATE
> >> >> > 42000]
> >> >> > (Error 22029). The step failed.
> >> >> >
> >> >> > I see the following error from SQLServer Logs ...
> >> >> >
> >> >> > BackupDiskFile::CreateMedia: Backup device
> >> >> > '\\SQL03\D$\SQLData\SQL04\ExIc_db_200503150100.BAK' failed to
> >> >> > create.
> >> >> > Operating system error = 1330(Logon failure: the specified account
> >> >> > password
> >> >> > has expired.).
> >> >> >
> >> >> > I verified the password for the user SQLAgent (the owner SQLServer
> >> >> > Agent
> >> >> > service) on all the servers and it is same. SQLAgent user is setup
> >> >> > as
> >> >> > non
> >> >> > expiring account.
> >> >> >
> >> >> > When I tried to backup a single database (use this directory ->
> >> >> > \\sql03\d$\SQLData\SQL04\test.bak), I get a error message "Unable to
> >> >> > to
> >> >> > verify backup file location. Do you want to use the backup file
> >> >> > location
> >> >> > anyway ?"
> >> >> >
> >> >> >
> >> >> > --
> >> >> > RK
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
Sunday, February 12, 2012
Backup Transaction logs
Hi:
I have configured maintenace plan to do a full backup of sql server at 5:00 Am followed by transaction log backup every 15 mts.The transaction log backup basically appends to existing backups so there is only one file of transaction log backup.
I want to modify that the transaction logs backup only has transaction logs after 5:00 Am full backup.Right now the transaction backup has backup of all the transaction logs since i have scheduled.
Thanks for your help.
Ganesh
BOL refers
During a full database or differential backup, Microsoft? SQL Server? backs up enough of the transaction log to produce a consistent database for when the database is restored.
As you are appending the transaction log backups the device would have only transactions that are occurred after 5am as per the schedule.
I don't understand what is your problem in having the transaction log backup, if you can also perform the log backups to file as serial order based on date-time.
|||May be i did not put the question correct.
When i restore the database i first restore the 5AM full back up .
Then before applying the transaction logs if i see the content of the transaction backup i see all the files before 5 AM too.
I just want the backups to have transaction logs only after 5 am not the entire transactions logs before 5AM.
Is there a setting in maintenace plan that i am missing.
|||So, there are a couple of things here:
First there is no harm in a transaction log backup file having data fro before your full backup. You simply specify which datasets to restore out of the file and you should be good.
Secondly, if what you really want is a new file with a unique name which is changed after every day's 5 AM full backup, you'll probably need to do that scripting yourself. You may be able to use the Maintenance plan as a base and customize the scripts, or just write your own script for your backups. The Maintenance Plan generator is intended to create a simple, basic backup job, not to cover every possibility.
|||True as the backup of database also takes care of transaction logs and there is no problem of duplication in your case.|||>True as the backup of database also takes care of transaction logs and there is no problem of duplication in your case.
Note that the portion of the transaction log which is included in a full backup is NOT a substitute for a log backup, nor does it interact with the log chain in any way.
Its purpose is to enable us to restore a database to a consistent point in time, when we may have been copying blocks over a 4 hour period, for example. Thus, we save the contents of the log which cover the duration of the back up, so that at the end of a restore, we roll the database forward to the point in time of the end of the backup. This gives a consistent point in time for the restore.
Again, this does not interact with the chain of log backups in any way.
backup transaction log fail
'QDB Backup Maintenace'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database
state cannot be changed while other users are using the
database 'QDB'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption
command failed.
[1] Database QDB: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
I can do complete backup and integrity success in the plan
but the transaciton log backup and check data and index
link keep failing. Please help
Thanks
SteveIf you had not guessed/determined the problem, this error points to the =
issue:
Database state cannot be changed while other users are using the =
database 'QDB'
Are you using a maintenance plan to do whatever it is that you are =
trying to do? Maintenance plans can help, but they are also a pain =
because you have to know what each checkbox does and how all the options =
work together. It has been my experience that maintenance plans do not =
always work as expected. Because of this I use jobs that I create and =
schedule as needed. =20
Are you backing up your transaction log during the day, or only at night =
as part of the maintenance plan? If you are only backing it up during =
the night (when avtivity is low) and not during the day (when =
transaction activity is high) you are not really protecting yourself =
from a failure. You should look into a "proper" backup plan ASAP! =20
I would recommend performing the transaction log backup(s) as often as =
necessary within a separate job that you can schedule to run as often as =
you want during the day.
You can then put some DBCC statements (such as CHECKDB, CHECKCATALOG, =
UPDATEUSAGE) into a separate job that you can schedule for some time =
when activity on the server is low.
--=20
Keith
"Steve C." <anonymous@.discussions.microsoft.com> wrote in message =
news:22d501c44fd1$cf818e70$3501280a@.phx.gbl...
> 'QDB Backup Maintenace'=20
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:=20
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database=20
> state cannot be changed while other users are using the=20
> database 'QDB'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER=20
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption=20
> command failed.
> [1] Database QDB: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:=20
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair=20
> statement not processed. Database needs to be in single=20
> user mode.
>=20
> I can do complete backup and integrity success in the plan=20
> but the transaciton log backup and check data and index=20
> link keep failing. Please help=20
>=20
> Thanks
>=20
> Steve
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database
state cannot be changed while other users are using the
database 'QDB'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption
command failed.
[1] Database QDB: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
I can do complete backup and integrity success in the plan
but the transaciton log backup and check data and index
link keep failing. Please help
Thanks
SteveIf you had not guessed/determined the problem, this error points to the =
issue:
Database state cannot be changed while other users are using the =
database 'QDB'
Are you using a maintenance plan to do whatever it is that you are =
trying to do? Maintenance plans can help, but they are also a pain =
because you have to know what each checkbox does and how all the options =
work together. It has been my experience that maintenance plans do not =
always work as expected. Because of this I use jobs that I create and =
schedule as needed. =20
Are you backing up your transaction log during the day, or only at night =
as part of the maintenance plan? If you are only backing it up during =
the night (when avtivity is low) and not during the day (when =
transaction activity is high) you are not really protecting yourself =
from a failure. You should look into a "proper" backup plan ASAP! =20
I would recommend performing the transaction log backup(s) as often as =
necessary within a separate job that you can schedule to run as often as =
you want during the day.
You can then put some DBCC statements (such as CHECKDB, CHECKCATALOG, =
UPDATEUSAGE) into a separate job that you can schedule for some time =
when activity on the server is low.
--=20
Keith
"Steve C." <anonymous@.discussions.microsoft.com> wrote in message =
news:22d501c44fd1$cf818e70$3501280a@.phx.gbl...
> 'QDB Backup Maintenace'=20
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:=20
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database=20
> state cannot be changed while other users are using the=20
> database 'QDB'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER=20
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption=20
> command failed.
> [1] Database QDB: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:=20
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair=20
> statement not processed. Database needs to be in single=20
> user mode.
>=20
> I can do complete backup and integrity success in the plan=20
> but the transaciton log backup and check data and index=20
> link keep failing. Please help=20
>=20
> Thanks
>=20
> Steve
backup transaction log fail
'QDB Backup Maintenace'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database
state cannot be changed while other users are using the
database 'QDB'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption
command failed.
[1] Database QDB: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
I can do complete backup and integrity success in the plan
but the transaciton log backup and check data and index
link keep failing. Please help
Thanks
SteveIf you had not guessed/determined the problem, this error points to the =issue:
Database state cannot be changed while other users are using the =database 'QDB'
Are you using a maintenance plan to do whatever it is that you are =trying to do? Maintenance plans can help, but they are also a pain =because you have to know what each checkbox does and how all the options =work together. It has been my experience that maintenance plans do not =always work as expected. Because of this I use jobs that I create and =schedule as needed.
Are you backing up your transaction log during the day, or only at night =as part of the maintenance plan? If you are only backing it up during =the night (when avtivity is low) and not during the day (when =transaction activity is high) you are not really protecting yourself =from a failure. You should look into a "proper" backup plan ASAP!
I would recommend performing the transaction log backup(s) as often as =necessary within a separate job that you can schedule to run as often as =you want during the day.
You can then put some DBCC statements (such as CHECKDB, CHECKCATALOG, =UPDATEUSAGE) into a separate job that you can schedule for some time =when activity on the server is low.
-- Keith
"Steve C." <anonymous@.discussions.microsoft.com> wrote in message =news:22d501c44fd1$cf818e70$3501280a@.phx.gbl...
> 'QDB Backup Maintenace' > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: > [Microsoft][ODBC SQL Server Driver][SQL Server]Database > state cannot be changed while other users are using the > database 'QDB'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER > DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption > command failed.
> [1] Database QDB: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair > statement not processed. Database needs to be in single > user mode.
> > I can do complete backup and integrity success in the plan > but the transaciton log backup and check data and index > link keep failing. Please help > > Thanks
> > Steve
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database
state cannot be changed while other users are using the
database 'QDB'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption
command failed.
[1] Database QDB: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
I can do complete backup and integrity success in the plan
but the transaciton log backup and check data and index
link keep failing. Please help
Thanks
SteveIf you had not guessed/determined the problem, this error points to the =issue:
Database state cannot be changed while other users are using the =database 'QDB'
Are you using a maintenance plan to do whatever it is that you are =trying to do? Maintenance plans can help, but they are also a pain =because you have to know what each checkbox does and how all the options =work together. It has been my experience that maintenance plans do not =always work as expected. Because of this I use jobs that I create and =schedule as needed.
Are you backing up your transaction log during the day, or only at night =as part of the maintenance plan? If you are only backing it up during =the night (when avtivity is low) and not during the day (when =transaction activity is high) you are not really protecting yourself =from a failure. You should look into a "proper" backup plan ASAP!
I would recommend performing the transaction log backup(s) as often as =necessary within a separate job that you can schedule to run as often as =you want during the day.
You can then put some DBCC statements (such as CHECKDB, CHECKCATALOG, =UPDATEUSAGE) into a separate job that you can schedule for some time =when activity on the server is low.
-- Keith
"Steve C." <anonymous@.discussions.microsoft.com> wrote in message =news:22d501c44fd1$cf818e70$3501280a@.phx.gbl...
> 'QDB Backup Maintenace' > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: > [Microsoft][ODBC SQL Server Driver][SQL Server]Database > state cannot be changed while other users are using the > database 'QDB'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER > DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption > command failed.
> [1] Database QDB: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair > statement not processed. Database needs to be in single > user mode.
> > I can do complete backup and integrity success in the plan > but the transaciton log backup and check data and index > link keep failing. Please help > > Thanks
> > Steve
Subscribe to:
Posts (Atom)