Hello,
I've configure "HP Omniback" to perform backups on=20
databases servers under the account "omni_acc", when i=20
configured this account i assign this account the "System=20
Administrators" Server Role because i can=B4t did backups if=20
the account wasn=B4t assign to this role.
Now i want to give to the omni_acc account other=20
previleges since we have the "db_backupoperator" database=20
role but... i cant do the backups if the user is only=20
assign to this role. Can anybody explain to me this=20
situation?
Is not supposed that a user assign to=20
the "db_backupoperator" database role perform backups and=20
restores to databases?
How can i give permissions to a user to make backups and=20
restores of databases?
Best regards
For RESTORE you cannot use db_backup operator if the database doesn't exist, as ... the database doesn't
exist! In this case, dbcreator server role should do.
Your problem is very likely that HP wrote their software so it requires this permissions. The place to start
is the documentation for the software. If there is no, ask the vendor of the software (HP).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message news:1919d01c44c8f$47eb8c30$a501280a@.phx.gbl...
Hello,
I've configure "HP Omniback" to perform backups on
databases servers under the account "omni_acc", when i
configured this account i assign this account the "System
Administrators" Server Role because i cant did backups if
the account wasnt assign to this role.
Now i want to give to the omni_acc account other
previleges since we have the "db_backupoperator" database
role but... i cant do the backups if the user is only
assign to this role. Can anybody explain to me this
situation?
Is not supposed that a user assign to
the "db_backupoperator" database role perform backups and
restores to databases?
How can i give permissions to a user to make backups and
restores of databases?
Best regards
Showing posts with label backup. Show all posts
Showing posts with label backup. Show all posts
Tuesday, March 27, 2012
Error executing Backup
Hello,
I've configure "HP Omniback" to perform backups on=20
databases servers under the account "omni_acc", when i=20
configured this account i assign this account the "System=20
Administrators" Server Role because i can=B4t did backups if=20
the account wasn=B4t assign to this role.
Now i want to give to the omni_acc account other=20
previleges since we have the "db_backupoperator" database=20
role but... i cant do the backups if the user is only=20
assign to this role. Can anybody explain to me this=20
situation?
Is not supposed that a user assign to=20
the "db_backupoperator" database role perform backups and=20
restores to databases?
How can i give permissions to a user to make backups and=20
restores of databases?
Best regardsFor RESTORE you cannot use db_backup operator if the database doesn't exist,
as ... the database doesn't
exist! In this case, dbcreator server role should do.
Your problem is very likely that HP wrote their software so it requires this
permissions. The place to start
is the documentation for the software. If there is no, ask the vendor of the
software (HP).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message news:1919d01c
44c8f$47eb8c30$a501280a@.phx.gbl...
Hello,
I've configure "HP Omniback" to perform backups on
databases servers under the account "omni_acc", when i
configured this account i assign this account the "System
Administrators" Server Role because i cant did backups if
the account wasnt assign to this role.
Now i want to give to the omni_acc account other
previleges since we have the "db_backupoperator" database
role but... i cant do the backups if the user is only
assign to this role. Can anybody explain to me this
situation?
Is not supposed that a user assign to
the "db_backupoperator" database role perform backups and
restores to databases?
How can i give permissions to a user to make backups and
restores of databases?
Best regards
I've configure "HP Omniback" to perform backups on=20
databases servers under the account "omni_acc", when i=20
configured this account i assign this account the "System=20
Administrators" Server Role because i can=B4t did backups if=20
the account wasn=B4t assign to this role.
Now i want to give to the omni_acc account other=20
previleges since we have the "db_backupoperator" database=20
role but... i cant do the backups if the user is only=20
assign to this role. Can anybody explain to me this=20
situation?
Is not supposed that a user assign to=20
the "db_backupoperator" database role perform backups and=20
restores to databases?
How can i give permissions to a user to make backups and=20
restores of databases?
Best regardsFor RESTORE you cannot use db_backup operator if the database doesn't exist,
as ... the database doesn't
exist! In this case, dbcreator server role should do.
Your problem is very likely that HP wrote their software so it requires this
permissions. The place to start
is the documentation for the software. If there is no, ask the vendor of the
software (HP).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message news:1919d01c
44c8f$47eb8c30$a501280a@.phx.gbl...
Hello,
I've configure "HP Omniback" to perform backups on
databases servers under the account "omni_acc", when i
configured this account i assign this account the "System
Administrators" Server Role because i cant did backups if
the account wasnt assign to this role.
Now i want to give to the omni_acc account other
previleges since we have the "db_backupoperator" database
role but... i cant do the backups if the user is only
assign to this role. Can anybody explain to me this
situation?
Is not supposed that a user assign to
the "db_backupoperator" database role perform backups and
restores to databases?
How can i give permissions to a user to make backups and
restores of databases?
Best regards
Error executing Backup
Hello,
I've configure "HP Omniback" to perform backups on databases servers under the account "omni_acc", when i configured this account i assign this account the "System Administrators" Server Role because i can=B4t did backups if the account wasn=B4t assign to this role.
Now i want to give to the omni_acc account other previleges since we have the "db_backupoperator" database role but... i cant do the backups if the user is only assign to this role. Can anybody explain to me this situation?
Is not supposed that a user assign to the "db_backupoperator" database role perform backups and restores to databases?
How can i give permissions to a user to make backups and restores of databases?
Best regardsFor RESTORE you cannot use db_backup operator if the database doesn't exist, as ... the database doesn't
exist! In this case, dbcreator server role should do.
Your problem is very likely that HP wrote their software so it requires this permissions. The place to start
is the documentation for the software. If there is no, ask the vendor of the software (HP).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message news:1919d01c44c8f$47eb8c30$a501280a@.phx.gbl...
Hello,
I've configure "HP Omniback" to perform backups on
databases servers under the account "omni_acc", when i
configured this account i assign this account the "System
Administrators" Server Role because i can´t did backups if
the account wasn´t assign to this role.
Now i want to give to the omni_acc account other
previleges since we have the "db_backupoperator" database
role but... i cant do the backups if the user is only
assign to this role. Can anybody explain to me this
situation?
Is not supposed that a user assign to
the "db_backupoperator" database role perform backups and
restores to databases?
How can i give permissions to a user to make backups and
restores of databases?
Best regards
I've configure "HP Omniback" to perform backups on databases servers under the account "omni_acc", when i configured this account i assign this account the "System Administrators" Server Role because i can=B4t did backups if the account wasn=B4t assign to this role.
Now i want to give to the omni_acc account other previleges since we have the "db_backupoperator" database role but... i cant do the backups if the user is only assign to this role. Can anybody explain to me this situation?
Is not supposed that a user assign to the "db_backupoperator" database role perform backups and restores to databases?
How can i give permissions to a user to make backups and restores of databases?
Best regardsFor RESTORE you cannot use db_backup operator if the database doesn't exist, as ... the database doesn't
exist! In this case, dbcreator server role should do.
Your problem is very likely that HP wrote their software so it requires this permissions. The place to start
is the documentation for the software. If there is no, ask the vendor of the software (HP).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message news:1919d01c44c8f$47eb8c30$a501280a@.phx.gbl...
Hello,
I've configure "HP Omniback" to perform backups on
databases servers under the account "omni_acc", when i
configured this account i assign this account the "System
Administrators" Server Role because i can´t did backups if
the account wasn´t assign to this role.
Now i want to give to the omni_acc account other
previleges since we have the "db_backupoperator" database
role but... i cant do the backups if the user is only
assign to this role. Can anybody explain to me this
situation?
Is not supposed that a user assign to
the "db_backupoperator" database role perform backups and
restores to databases?
How can i give permissions to a user to make backups and
restores of databases?
Best regards
Monday, March 26, 2012
Error during database restore
Hi,
I'm trying to restore a database backup but I get this error. What does it mean exactly?
Thanks!
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'WHIDBEY1'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1314.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The media set has 2 media families but only 1 are provided. All members must be provided.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3132)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1314&EvtSrc=MSSQLServer&EvtID=3132&LinkId=20476
BUTTONS:
OK
what this usually means is that you backed up your database to two different files, but are only supplying one file during your restore. Is this the case? Did you create a database backup to multiple files? If so, you need all of them to do the restore.|||I have backed up the database to a file in one server and trying to restore the backup file into another database in another server. While restoring i am getting the same error. Can you tell me what could be the issue.
Error during database restore
Hi,
I'm trying to restore a database backup but I get this error. What does it mean exactly?
Thanks!
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'WHIDBEY1'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1314.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The media set has 2 media families but only 1 are provided. All members must be provided.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3132)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1314&EvtSrc=MSSQLServer&EvtID=3132&LinkId=20476
BUTTONS:
OK
what this usually means is that you backed up your database to two different files, but are only supplying one file during your restore. Is this the case? Did you create a database backup to multiple files? If so, you need all of them to do the restore.|||I have backed up the database to a file in one server and trying to restore the backup file into another database in another server. While restoring i am getting the same error. Can you tell me what could be the issue.
Error during backup verify
SQL Server 2000.
Doing an SQL backup to a network shared drive on our file server.
Any help appreciated ...
During the verify on several different databases, I find the following
error in the Maintenance Plan log:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203:
[Microsoft][ODBC SQL Server Driver][SQL Server]Read on
'\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_200402102300.BAK' failed,
status = 64. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
I get the following info from the SQL Server Error Log:
BackupMedium::ReportIoError: read failure on backup device
'\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_200402162359.BAK'. Operating
system error 64(The specified network name is no longer available.).That is a common NT error. Could you test the network connections by copying
large files between the servers. Also have you checked the NT eventlogs on
the file server to see if you have any messages releated to network
problems.
Simon
This posting is provided "as is" with no warranties and confers no rights.
"SoCal Snapper" <snapperjackson@.juno.com> wrote in message
news:2d4f003f.0402251113.6c36d43d@.posting.google.com...
> SQL Server 2000.
> Doing an SQL backup to a network shared drive on our file server.
> Any help appreciated ...
> During the verify on several different databases, I find the following
> error in the Maintenance Plan log:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Read on
> '\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_200402102300.BAK' failed,
> status = 64. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> I get the following info from the SQL Server Error Log:
> BackupMedium::ReportIoError: read failure on backup device
> '\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_200402162359.BAK'. Operating
> system error 64(The specified network name is no longer available.).
Doing an SQL backup to a network shared drive on our file server.
Any help appreciated ...
During the verify on several different databases, I find the following
error in the Maintenance Plan log:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203:
[Microsoft][ODBC SQL Server Driver][SQL Server]Read on
'\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_200402102300.BAK' failed,
status = 64. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
I get the following info from the SQL Server Error Log:
BackupMedium::ReportIoError: read failure on backup device
'\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_200402162359.BAK'. Operating
system error 64(The specified network name is no longer available.).That is a common NT error. Could you test the network connections by copying
large files between the servers. Also have you checked the NT eventlogs on
the file server to see if you have any messages releated to network
problems.
Simon
This posting is provided "as is" with no warranties and confers no rights.
"SoCal Snapper" <snapperjackson@.juno.com> wrote in message
news:2d4f003f.0402251113.6c36d43d@.posting.google.com...
> SQL Server 2000.
> Doing an SQL backup to a network shared drive on our file server.
> Any help appreciated ...
> During the verify on several different databases, I find the following
> error in the Maintenance Plan log:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Read on
> '\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_200402102300.BAK' failed,
> status = 64. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> I get the following info from the SQL Server Error Log:
> BackupMedium::ReportIoError: read failure on backup device
> '\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_200402162359.BAK'. Operating
> system error 64(The specified network name is no longer available.).
Thursday, March 22, 2012
Error during backup verify
SQL Server 2000.
Doing an SQL backup to a network shared drive on our file server.
Any help appreciated ...
During the verify on several different databases, I find the following
error in the Maintenance Plan log:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203:
[Microsoft][ODBC SQL Server Driver][SQL Server]Read on
'\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_
200402102300.BAK' failed,
status = 64. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE i
s
terminating abnormally.
I get the following info from the SQL Server Error Log:
BackupMedium::ReportIoError: read failure on backup device
'\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_
200402162359.BAK'. Operating
system error 64(The specified network name is no longer available.).That is a common NT error. Could you test the network connections by copying
large files between the servers. Also have you checked the NT eventlogs on
the file server to see if you have any messages releated to network
problems.
Simon
This posting is provided "as is" with no warranties and confers no rights.
"SoCal Snapper" <snapperjackson@.juno.com> wrote in message
news:2d4f003f.0402251113.6c36d43d@.posting.google.com...
> SQL Server 2000.
> Doing an SQL backup to a network shared drive on our file server.
> Any help appreciated ...
> During the verify on several different databases, I find the following
> error in the Maintenance Plan log:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Read on
> '\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_
200402102300.BAK' failed,
> status = 64. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE
is
> terminating abnormally.
> I get the following info from the SQL Server Error Log:
> BackupMedium::ReportIoError: read failure on backup device
> '\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_
200402162359.BAK'. Operating
> system error 64(The specified network name is no longer available.).sql
Doing an SQL backup to a network shared drive on our file server.
Any help appreciated ...
During the verify on several different databases, I find the following
error in the Maintenance Plan log:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203:
[Microsoft][ODBC SQL Server Driver][SQL Server]Read on
'\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_
200402102300.BAK' failed,
status = 64. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE i
s
terminating abnormally.
I get the following info from the SQL Server Error Log:
BackupMedium::ReportIoError: read failure on backup device
'\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_
200402162359.BAK'. Operating
system error 64(The specified network name is no longer available.).That is a common NT error. Could you test the network connections by copying
large files between the servers. Also have you checked the NT eventlogs on
the file server to see if you have any messages releated to network
problems.
Simon
This posting is provided "as is" with no warranties and confers no rights.
"SoCal Snapper" <snapperjackson@.juno.com> wrote in message
news:2d4f003f.0402251113.6c36d43d@.posting.google.com...
> SQL Server 2000.
> Doing an SQL backup to a network shared drive on our file server.
> Any help appreciated ...
> During the verify on several different databases, I find the following
> error in the Maintenance Plan log:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Read on
> '\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_
200402102300.BAK' failed,
> status = 64. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE
is
> terminating abnormally.
> I get the following info from the SQL Server Error Log:
> BackupMedium::ReportIoError: read failure on backup device
> '\\Pfcdata\SQLBackup\PFCSQLT\pfcgold_db_
200402162359.BAK'. Operating
> system error 64(The specified network name is no longer available.).sql
error during backup
when a database backup is tried to be taken to the hard disk it gives a
error "Write on 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\****.bak'
failed, status =112". The path was changed several times and checked the
same error message was occurring.
Any Clues?
Regards
Sunil Pinto
Hi
Some entry in the ERROR LOG file?
"Sunil Pinto" <SunilPinto@.hotmail.com> wrote in message
news:elnTTAPtEHA.2688@.TK2MSFTNGP14.phx.gbl...
> when a database backup is tried to be taken to the hard disk it gives a
> error "Write on 'F:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\****.bak'
> failed, status =112". The path was changed several times and checked the
> same error message was occurring.
> Any Clues?
> Regards
> Sunil Pinto
>
|||Sunil,
You've run out of disk space. Check how much space is available on your
drives by executing
exec master..xp_fixeddrives
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Sunil Pinto wrote:
> when a database backup is tried to be taken to the hard disk it gives a
> error "Write on 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\****.bak'
> failed, status =112". The path was changed several times and checked the
> same error message was occurring.
> Any Clues?
> Regards
> Sunil Pinto
>
error "Write on 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\****.bak'
failed, status =112". The path was changed several times and checked the
same error message was occurring.
Any Clues?
Regards
Sunil Pinto
Hi
Some entry in the ERROR LOG file?
"Sunil Pinto" <SunilPinto@.hotmail.com> wrote in message
news:elnTTAPtEHA.2688@.TK2MSFTNGP14.phx.gbl...
> when a database backup is tried to be taken to the hard disk it gives a
> error "Write on 'F:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\****.bak'
> failed, status =112". The path was changed several times and checked the
> same error message was occurring.
> Any Clues?
> Regards
> Sunil Pinto
>
|||Sunil,
You've run out of disk space. Check how much space is available on your
drives by executing
exec master..xp_fixeddrives
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Sunil Pinto wrote:
> when a database backup is tried to be taken to the hard disk it gives a
> error "Write on 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\****.bak'
> failed, status =112". The path was changed several times and checked the
> same error message was occurring.
> Any Clues?
> Regards
> Sunil Pinto
>
error during backup
when a database backup is tried to be taken to the hard disk it gives a
error "Write on 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\****.bak
'
failed, status =112". The path was changed several times and checked the
same error message was occurring.
Any Clues?
Regards
Sunil PintoHi
Some entry in the ERROR LOG file?
"Sunil Pinto" <SunilPinto@.hotmail.com> wrote in message
news:elnTTAPtEHA.2688@.TK2MSFTNGP14.phx.gbl...
> when a database backup is tried to be taken to the hard disk it gives a
> error "Write on 'F:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\****.bak'
> failed, status =112". The path was changed several times and checked the
> same error message was occurring.
> Any Clues?
> Regards
> Sunil Pinto
>|||Sunil,
You've run out of disk space. Check how much space is available on your
drives by executing
exec master..xp_fixeddrives
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Sunil Pinto wrote:
> when a database backup is tried to be taken to the hard disk it gives a
> error "Write on 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\****.b
ak'
> failed, status =112". The path was changed several times and checked the
> same error message was occurring.
> Any Clues?
> Regards
> Sunil Pinto
>
error "Write on 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\****.bak
'
failed, status =112". The path was changed several times and checked the
same error message was occurring.
Any Clues?
Regards
Sunil PintoHi
Some entry in the ERROR LOG file?
"Sunil Pinto" <SunilPinto@.hotmail.com> wrote in message
news:elnTTAPtEHA.2688@.TK2MSFTNGP14.phx.gbl...
> when a database backup is tried to be taken to the hard disk it gives a
> error "Write on 'F:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\****.bak'
> failed, status =112". The path was changed several times and checked the
> same error message was occurring.
> Any Clues?
> Regards
> Sunil Pinto
>|||Sunil,
You've run out of disk space. Check how much space is available on your
drives by executing
exec master..xp_fixeddrives
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Sunil Pinto wrote:
> when a database backup is tried to be taken to the hard disk it gives a
> error "Write on 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\****.b
ak'
> failed, status =112". The path was changed several times and checked the
> same error message was occurring.
> Any Clues?
> Regards
> Sunil Pinto
>
error during backup
when a database backup is tried to be taken to the hard disk it gives a
error "Write on 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\****.bak'
failed, status =112". The path was changed several times and checked the
same error message was occurring.
Any Clues?
Regards
Sunil PintoHi
Some entry in the ERROR LOG file?
"Sunil Pinto" <SunilPinto@.hotmail.com> wrote in message
news:elnTTAPtEHA.2688@.TK2MSFTNGP14.phx.gbl...
> when a database backup is tried to be taken to the hard disk it gives a
> error "Write on 'F:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\****.bak'
> failed, status =112". The path was changed several times and checked the
> same error message was occurring.
> Any Clues?
> Regards
> Sunil Pinto
>|||Sunil,
You've run out of disk space. Check how much space is available on your
drives by executing
exec master..xp_fixeddrives
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Sunil Pinto wrote:
> when a database backup is tried to be taken to the hard disk it gives a
> error "Write on 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\****.bak'
> failed, status =112". The path was changed several times and checked the
> same error message was occurring.
> Any Clues?
> Regards
> Sunil Pinto
>
error "Write on 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\****.bak'
failed, status =112". The path was changed several times and checked the
same error message was occurring.
Any Clues?
Regards
Sunil PintoHi
Some entry in the ERROR LOG file?
"Sunil Pinto" <SunilPinto@.hotmail.com> wrote in message
news:elnTTAPtEHA.2688@.TK2MSFTNGP14.phx.gbl...
> when a database backup is tried to be taken to the hard disk it gives a
> error "Write on 'F:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\****.bak'
> failed, status =112". The path was changed several times and checked the
> same error message was occurring.
> Any Clues?
> Regards
> Sunil Pinto
>|||Sunil,
You've run out of disk space. Check how much space is available on your
drives by executing
exec master..xp_fixeddrives
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Sunil Pinto wrote:
> when a database backup is tried to be taken to the hard disk it gives a
> error "Write on 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\****.bak'
> failed, status =112". The path was changed several times and checked the
> same error message was occurring.
> Any Clues?
> Regards
> Sunil Pinto
>
Error doing manual backup
I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
get an error trying to backup to a local drive:
Cannot verify the existence of the backup file location.
I read how this usually refers to backing up to a network share or
something, but this is a local drive. I tried it logged in to the Management
Studio as trusted and SQL authentication.
Any ideas? Thanks!
Hi
You may want to check backing up to a different directory, if that works it
must be ralated to the directory in which you are backing up to, such as
permissions.
John
"ricky252525" wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Management
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!
|||Thanks, I tried that. Even tried to backup right to C, just for fun
--didn't work. Other suggestions?
"ricky252525" wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Management
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!
|||ricky252525 wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Management
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!
Is the SQL Server service running as a domain user or as Local System?
Which domain user? What rights does that domain user have on the local
machine?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi
If you have checked/changed the service accounts as Tracy has suggested then
what message do you get if you back this up using a T-SQL script?
John
"ricky252525" wrote:
[vbcol=seagreen]
> Thanks, I tried that. Even tried to backup right to C, just for fun
> --didn't work. Other suggestions?
> "ricky252525" wrote:
get an error trying to backup to a local drive:
Cannot verify the existence of the backup file location.
I read how this usually refers to backing up to a network share or
something, but this is a local drive. I tried it logged in to the Management
Studio as trusted and SQL authentication.
Any ideas? Thanks!
Hi
You may want to check backing up to a different directory, if that works it
must be ralated to the directory in which you are backing up to, such as
permissions.
John
"ricky252525" wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Management
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!
|||Thanks, I tried that. Even tried to backup right to C, just for fun
--didn't work. Other suggestions?
"ricky252525" wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Management
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!
|||ricky252525 wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Management
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!
Is the SQL Server service running as a domain user or as Local System?
Which domain user? What rights does that domain user have on the local
machine?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi
If you have checked/changed the service accounts as Tracy has suggested then
what message do you get if you back this up using a T-SQL script?
John
"ricky252525" wrote:
[vbcol=seagreen]
> Thanks, I tried that. Even tried to backup right to C, just for fun
> --didn't work. Other suggestions?
> "ricky252525" wrote:
Error doing manual backup
I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
get an error trying to backup to a local drive:
Cannot verify the existence of the backup file location.
I read how this usually refers to backing up to a network share or
something, but this is a local drive. I tried it logged in to the Managemen
t
Studio as trusted and SQL authentication.
Any ideas? Thanks!Hi
You may want to check backing up to a different directory, if that works it
must be ralated to the directory in which you are backing up to, such as
permissions.
John
"ricky252525" wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin a
nd
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Managem
ent
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!|||Thanks, I tried that. Even tried to backup right to C, just for fun
--didn't work. Other suggestions?
"ricky252525" wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin a
nd
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Managem
ent
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!|||ricky252525 wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin a
nd
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Managem
ent
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!
Is the SQL Server service running as a domain user or as Local System?
Which domain user? What rights does that domain user have on the local
machine?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi
If you have checked/changed the service accounts as Tracy has suggested then
what message do you get if you back this up using a T-SQL script?
John
"ricky252525" wrote:
[vbcol=seagreen]
> Thanks, I tried that. Even tried to backup right to C, just for fun
> --didn't work. Other suggestions?
> "ricky252525" wrote:
>
get an error trying to backup to a local drive:
Cannot verify the existence of the backup file location.
I read how this usually refers to backing up to a network share or
something, but this is a local drive. I tried it logged in to the Managemen
t
Studio as trusted and SQL authentication.
Any ideas? Thanks!Hi
You may want to check backing up to a different directory, if that works it
must be ralated to the directory in which you are backing up to, such as
permissions.
John
"ricky252525" wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin a
nd
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Managem
ent
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!|||Thanks, I tried that. Even tried to backup right to C, just for fun
--didn't work. Other suggestions?
"ricky252525" wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin a
nd
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Managem
ent
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!|||ricky252525 wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin a
nd
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Managem
ent
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!
Is the SQL Server service running as a domain user or as Local System?
Which domain user? What rights does that domain user have on the local
machine?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi
If you have checked/changed the service accounts as Tracy has suggested then
what message do you get if you back this up using a T-SQL script?
John
"ricky252525" wrote:
[vbcol=seagreen]
> Thanks, I tried that. Even tried to backup right to C, just for fun
> --didn't work. Other suggestions?
> "ricky252525" wrote:
>
Error doing manual backup
I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
get an error trying to backup to a local drive:
Cannot verify the existence of the backup file location.
I read how this usually refers to backing up to a network share or
something, but this is a local drive. I tried it logged in to the Management
Studio as trusted and SQL authentication.
Any ideas? Thanks!Hi
You may want to check backing up to a different directory, if that works it
must be ralated to the directory in which you are backing up to, such as
permissions.
John
"ricky252525" wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Management
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!|||Thanks, I tried that. Even tried to backup right to C, just for fun
--didn't work. Other suggestions?
"ricky252525" wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Management
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!|||ricky252525 wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Management
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!
Is the SQL Server service running as a domain user or as Local System?
Which domain user? What rights does that domain user have on the local
machine?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi
If you have checked/changed the service accounts as Tracy has suggested then
what message do you get if you back this up using a T-SQL script?
John
"ricky252525" wrote:
> Thanks, I tried that. Even tried to backup right to C, just for fun
> --didn't work. Other suggestions?
> "ricky252525" wrote:
> > I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
> > get an error trying to backup to a local drive:
> >
> > Cannot verify the existence of the backup file location.
> >
> > I read how this usually refers to backing up to a network share or
> > something, but this is a local drive. I tried it logged in to the Management
> > Studio as trusted and SQL authentication.
> >
> > Any ideas? Thanks!
get an error trying to backup to a local drive:
Cannot verify the existence of the backup file location.
I read how this usually refers to backing up to a network share or
something, but this is a local drive. I tried it logged in to the Management
Studio as trusted and SQL authentication.
Any ideas? Thanks!Hi
You may want to check backing up to a different directory, if that works it
must be ralated to the directory in which you are backing up to, such as
permissions.
John
"ricky252525" wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Management
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!|||Thanks, I tried that. Even tried to backup right to C, just for fun
--didn't work. Other suggestions?
"ricky252525" wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Management
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!|||ricky252525 wrote:
> I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
> get an error trying to backup to a local drive:
> Cannot verify the existence of the backup file location.
> I read how this usually refers to backing up to a network share or
> something, but this is a local drive. I tried it logged in to the Management
> Studio as trusted and SQL authentication.
> Any ideas? Thanks!
Is the SQL Server service running as a domain user or as Local System?
Which domain user? What rights does that domain user have on the local
machine?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi
If you have checked/changed the service accounts as Tracy has suggested then
what message do you get if you back this up using a T-SQL script?
John
"ricky252525" wrote:
> Thanks, I tried that. Even tried to backup right to C, just for fun
> --didn't work. Other suggestions?
> "ricky252525" wrote:
> > I'm sitting at the server itself (SQL 2005 Standard), logged in as admin and
> > get an error trying to backup to a local drive:
> >
> > Cannot verify the existence of the backup file location.
> >
> > I read how this usually refers to backing up to a network share or
> > something, but this is a local drive. I tried it logged in to the Management
> > Studio as trusted and SQL authentication.
> >
> > Any ideas? Thanks!
Error detaching sql express database using SMO
I am using SMO to provide a backup/restore feature. The restore code looks like this:
try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;
// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);
break;
}
}
// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));
// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}
This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.
Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.
Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.
How can I avoid/ workaround this to ensure my restore functionality is full proof.
Thanks,
Nick
Hello
I had exactly the same problem. It took some time to figure out but here is the solution:
You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.
The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.
I hope this helps you.
|||Hi,
I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.
Nick
|||You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:
server.KillAllProcesses(db.Name);
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
db.Alter(TerminationClause.RollbackTransactionsImmediately);
server.DetachDatabase(db.Name, true);
Hope this helps,
Steve
where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.
MusiMeli
Error detaching sql express database using SMO
I am using SMO to provide a backup/restore feature. The restore code looks like this:
try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;
// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);
break;
}
}
// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));
// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}
This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.
Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.
Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.
How can I avoid/ workaround this to ensure my restore functionality is full proof.
Thanks,
Nick
Hello
I had exactly the same problem. It took some time to figure out but here is the solution:
You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.
The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.
I hope this helps you.
|||Hi,
I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.
Nick
|||You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:
server.KillAllProcesses(db.Name);
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
db.Alter(TerminationClause.RollbackTransactionsImmediately);
server.DetachDatabase(db.Name, true);
Hope this helps,
Steve
where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.
MusiMeli
Error detaching sql express database using SMO
I am using SMO to provide a backup/restore feature. The restore code looks like this:
try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;
// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);
break;
}
}
// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));
// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}
This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.
Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.
Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.
How can I avoid/ workaround this to ensure my restore functionality is full proof.
Thanks,
Nick
Hello
I had exactly the same problem. It took some time to figure out but here is the solution:
You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.
The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.
I hope this helps you.
|||Hi,
I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.
Nick
|||You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:
server.KillAllProcesses(db.Name);
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
db.Alter(TerminationClause.RollbackTransactionsImmediately);
server.DetachDatabase(db.Name, true);
Hope this helps,
Steve
where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.
MusiMeli
sql
Error detaching sql express database using SMO
I am using SMO to provide a backup/restore feature. The restore code looks like this:
try
{
// Initialise server object.
Server server = new Server(serverName);
server.ConnectionContext.ConnectionString = connectionString;
// Check if database is current attached to sqlexpress.
foreach (Database db in server.Databases)
{
if (String.Compare(db.Name, destinationPath, true) == 0)
{
Console.WriteLine("Detaching existing database before restore ...");
server.DetachDatabase(db.Name, false);
break;
}
}
// Configure restore.
Restore restore = new Restore();
restore.Database = destinationPath;
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
restore.Devices.Add(new BackupDeviceItem(sourcePath, DeviceType.File));
// Perform restore.
restore.SqlRestore(server);
}
catch (FailedOperationException foe)
{
Console.WriteLine("Exception - SqlRestore of {0} failed with: {1}. Detail: {2}",
sourcePath, foe.Message, foe.InnerException.ToString());
}
This work 9/10 times. However occassional the DetachDatabase() call fails with the following error even though the database is not currently in use.
Exception - SqlRestore of D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.bak failed with: Detach database failed for Server 'NICKDEV\SQLEXPRESS'. . Detail: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot detach the database 'D:\ImlDev\Auction\Auction\bin\Debug\Databases\databaseTest.mdf' because it is currently in use.
Clearly it was in use previously and the application using it may have crashed and not closed connections etc; this is why I want to restore the database.
How can I avoid/ workaround this to ensure my restore functionality is full proof.
Thanks,
Nick
Hello
I had exactly the same problem. It took some time to figure out but here is the solution:
You need to insert the restore code somewhere in your program before you open any database associated with your program. I put a restore databases button on the main form before any databases were opened. This works fine. Backup works wherever you put it in the code.
The reason for the problem is that once your database has been opened SQL Server will report an open connection even if you close the database connection. A gift from microsoft!!! There is no way you can disconnect from the database once you have opened it except to completely exit the program! Microsoft seems to provide thousands of options but never the one you need.
I hope this helps you.
|||Hi,
I have just tackled the same problem, and I found using server.KillAllProcess(dbName) did the trick for me.
Nick
|||You need to make sure you have the only connection to the database. You can do this by setting the database into single-user mode before detaching it, like this:
server.KillAllProcesses(db.Name);
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
db.Alter(TerminationClause.RollbackTransactionsImmediately);
server.DetachDatabase(db.Name, true);
Hope this helps,
Steve
where exactly did you put your restore-code? On my MainForm, there's also a button, so I think it will be initialized before any connections are opened in MainForm_Load. In the button's click event I call the method that includes the "detach" code. But I receive the same error that the database is in use.
MusiMeli
Sunday, February 19, 2012
Error backuping up database to tape.
I am trying to backup a database to tape. I'm using Microsoft SQL Server 2000 Enterprise Edition.
When I try to do the backup using Enterprise Manager I see the following message:
Microsoft SQL-DMO (ODBC SQLState: 42000)
Cannot open backup device '\\.\Tape0'. Device error or device off-line. See the SQL Server error log for more details.
BACKUP DATABASE is terminating abnormally.
In the Event Viewer is logged the following message:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 14/1/2004
Time: 10:29:20
User: N/A
Computer: MySQLName
Description:
18204 :
BackupTapeFile::OpenMedia: Backup device '\\.\Tape0' failed to open. Operating system error = 5(Access is denied.).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I can do the backup to disk normally, but I need to do to tape.
Please, help me to solve this issue.I found the solution for my problem.
There is some pre-requisites to run/install SQL Server. If SQL Server services are running with a Domain User Account, this account must be member of Administrators or Power Users group. I put the account that are running the services in Administrators group and my problem was solved. This pre-requisite is documented in SQL Server Books On Line.|||Don't mean to "preach" to you or anything, but I would keep backups to disk, and add a step to the job to backup the backup onto the tape afterwards. Many obvious things can go wrong if you do a direct dump onto the tape. It is also easier to restore a file from a tape onto the share, and then do a native restore from it, in case the server you're restoring to doesn't have a local tape drive. Otherwise, along with recovering the server you'll have to attach a local tape device to it, before you even get to see the backup of your database.
When I try to do the backup using Enterprise Manager I see the following message:
Microsoft SQL-DMO (ODBC SQLState: 42000)
Cannot open backup device '\\.\Tape0'. Device error or device off-line. See the SQL Server error log for more details.
BACKUP DATABASE is terminating abnormally.
In the Event Viewer is logged the following message:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 14/1/2004
Time: 10:29:20
User: N/A
Computer: MySQLName
Description:
18204 :
BackupTapeFile::OpenMedia: Backup device '\\.\Tape0' failed to open. Operating system error = 5(Access is denied.).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I can do the backup to disk normally, but I need to do to tape.
Please, help me to solve this issue.I found the solution for my problem.
There is some pre-requisites to run/install SQL Server. If SQL Server services are running with a Domain User Account, this account must be member of Administrators or Power Users group. I put the account that are running the services in Administrators group and my problem was solved. This pre-requisite is documented in SQL Server Books On Line.|||Don't mean to "preach" to you or anything, but I would keep backups to disk, and add a step to the job to backup the backup onto the tape afterwards. Many obvious things can go wrong if you do a direct dump onto the tape. It is also easier to restore a file from a tape onto the share, and then do a native restore from it, in case the server you're restoring to doesn't have a local tape drive. Otherwise, along with recovering the server you'll have to attach a local tape device to it, before you even get to see the backup of your database.
Error backing up Transaction Logs
This is the error I get for the database named, "NoteServe4005". I get this error for all databases whether I attempt to backup manually or automatically.
[4] Database NoteServe4005: Transaction Log Backup...
Destination: [c:\backup\NoteServe4005\NoteServe4005_tlog_200405 041500.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft][ODBC SQL Server Driver][SQL Server]The media family on device 'c:\backup\NoteServe4005\NoteServe4005_tlog_200405 041500.TRN' is incorrectly formed. SQL Server cannot process this media fami
ly.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating abnormally.
I searched support for the error pulling up only KB:297104 which says that this can happen when transaction log files exceed 4GB. However, the T-Log in this example is only 50MB. It also says the error is fixed by SP1 and I have SP3a. There's also a worka
round which involves setting the trace flag to 3111, but that didn't seem to make a difference either.
Ideas?
What is the actual backup command your using?
Andrew J. Kelly SQL MVP
"Garrett Taylor" <gtaylor@.westerntitle.net> wrote in message
news:1049DD05-2DBF-499D-AE0F-C263FAB62C90@.microsoft.com...
> This is the error I get for the database named, "NoteServe4005". I get
this error for all databases whether I attempt to backup manually or
automatically.
> [4] Database NoteServe4005: Transaction Log Backup...
> Destination:
[c:\backup\NoteServe4005\NoteServe4005_tlog_200405 041500.TRN]
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft][ODBC
SQL Server Driver][SQL Server]The media family on device
'c:\backup\NoteServe4005\NoteServe4005_tlog_200405 041500.TRN' is incorrectly
formed. SQL Server cannot process this media family.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating
abnormally.
> I searched support for the error pulling up only KB:297104 which says that
this can happen when transaction log files exceed 4GB. However, the T-Log in
this example is only 50MB. It also says the error is fixed by SP1 and I have
SP3a. There's also a workaround which involves setting the trace flag to
3111, but that didn't seem to make a difference either.
> Ideas?
|||I'm running it from Enterprise Manager both as a scheduled
job and manually.
>--Original Message--
>What is the actual backup command your using?
>--
>Andrew J. Kelly SQL MVP
>
>"Garrett Taylor" <gtaylor@.westerntitle.net> wrote in
message[vbcol=seagreen]
>news:1049DD05-2DBF-499D-AE0F-C263FAB62C90@.microsoft.com...
named, "NoteServe4005". I get
>this error for all databases whether I attempt to backup
manually or
>automatically.
>[c:\backup\NoteServe4005
\NoteServe4005_tlog_200405041500.TRN][vbcol=seagreen]
[Microsoft][ODBC
>SQL Server Driver][SQL Server]The media family on device
>'c:\backup\NoteServe4005
\NoteServe4005_tlog_200405041500.TRN' is incorrectly[vbcol=seagreen]
>formed. SQL Server cannot process this media family.
LOG is terminating[vbcol=seagreen]
>abnormally.
KB:297104 which says that
>this can happen when transaction log files exceed 4GB.
However, the T-Log in
>this example is only 50MB. It also says the error is
fixed by SP1 and I have
>SP3a. There's also a workaround which involves setting
the trace flag to
>3111, but that didn't seem to make a difference either.
>
>.
>
|||You might want to give MS PSS a call.
http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"Garrett Taylor" <gtaylor@.westerntitle.net> wrote in message
news:95f101c43380$92fb9c10$a301280a@.phx.gbl...[vbcol=seagreen]
> I'm running it from Enterprise Manager both as a scheduled
> job and manually.
> message
> named, "NoteServe4005". I get
> manually or
> \NoteServe4005_tlog_200405041500.TRN]
> [Microsoft][ODBC
> \NoteServe4005_tlog_200405041500.TRN' is incorrectly
> LOG is terminating
> KB:297104 which says that
> However, the T-Log in
> fixed by SP1 and I have
> the trace flag to
[4] Database NoteServe4005: Transaction Log Backup...
Destination: [c:\backup\NoteServe4005\NoteServe4005_tlog_200405 041500.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft][ODBC SQL Server Driver][SQL Server]The media family on device 'c:\backup\NoteServe4005\NoteServe4005_tlog_200405 041500.TRN' is incorrectly formed. SQL Server cannot process this media fami
ly.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating abnormally.
I searched support for the error pulling up only KB:297104 which says that this can happen when transaction log files exceed 4GB. However, the T-Log in this example is only 50MB. It also says the error is fixed by SP1 and I have SP3a. There's also a worka
round which involves setting the trace flag to 3111, but that didn't seem to make a difference either.
Ideas?
What is the actual backup command your using?
Andrew J. Kelly SQL MVP
"Garrett Taylor" <gtaylor@.westerntitle.net> wrote in message
news:1049DD05-2DBF-499D-AE0F-C263FAB62C90@.microsoft.com...
> This is the error I get for the database named, "NoteServe4005". I get
this error for all databases whether I attempt to backup manually or
automatically.
> [4] Database NoteServe4005: Transaction Log Backup...
> Destination:
[c:\backup\NoteServe4005\NoteServe4005_tlog_200405 041500.TRN]
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft][ODBC
SQL Server Driver][SQL Server]The media family on device
'c:\backup\NoteServe4005\NoteServe4005_tlog_200405 041500.TRN' is incorrectly
formed. SQL Server cannot process this media family.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating
abnormally.
> I searched support for the error pulling up only KB:297104 which says that
this can happen when transaction log files exceed 4GB. However, the T-Log in
this example is only 50MB. It also says the error is fixed by SP1 and I have
SP3a. There's also a workaround which involves setting the trace flag to
3111, but that didn't seem to make a difference either.
> Ideas?
|||I'm running it from Enterprise Manager both as a scheduled
job and manually.
>--Original Message--
>What is the actual backup command your using?
>--
>Andrew J. Kelly SQL MVP
>
>"Garrett Taylor" <gtaylor@.westerntitle.net> wrote in
message[vbcol=seagreen]
>news:1049DD05-2DBF-499D-AE0F-C263FAB62C90@.microsoft.com...
named, "NoteServe4005". I get
>this error for all databases whether I attempt to backup
manually or
>automatically.
>[c:\backup\NoteServe4005
\NoteServe4005_tlog_200405041500.TRN][vbcol=seagreen]
[Microsoft][ODBC
>SQL Server Driver][SQL Server]The media family on device
>'c:\backup\NoteServe4005
\NoteServe4005_tlog_200405041500.TRN' is incorrectly[vbcol=seagreen]
>formed. SQL Server cannot process this media family.
LOG is terminating[vbcol=seagreen]
>abnormally.
KB:297104 which says that
>this can happen when transaction log files exceed 4GB.
However, the T-Log in
>this example is only 50MB. It also says the error is
fixed by SP1 and I have
>SP3a. There's also a workaround which involves setting
the trace flag to
>3111, but that didn't seem to make a difference either.
>
>.
>
|||You might want to give MS PSS a call.
http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"Garrett Taylor" <gtaylor@.westerntitle.net> wrote in message
news:95f101c43380$92fb9c10$a301280a@.phx.gbl...[vbcol=seagreen]
> I'm running it from Enterprise Manager both as a scheduled
> job and manually.
> message
> named, "NoteServe4005". I get
> manually or
> \NoteServe4005_tlog_200405041500.TRN]
> [Microsoft][ODBC
> \NoteServe4005_tlog_200405041500.TRN' is incorrectly
> LOG is terminating
> KB:297104 which says that
> However, the T-Log in
> fixed by SP1 and I have
> the trace flag to
Error backing up Transaction Logs
This is the error I get for the database named, "NoteServe4005". I get this error for all databases whether I attempt to backup manually or automatically.
[4] Database NoteServe4005: Transaction Log Backup..
Destination: [c:\backup\NoteServe4005\NoteServe4005_tlog_200405041500.TRN
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft][ODBC SQL Server Driver][SQL Server]The media family on device 'c:\backup\NoteServe4005\NoteServe4005_tlog_200405041500.TRN' is incorrectly formed. SQL Server cannot process this media family
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating abnormally
I searched support for the error pulling up only KB:297104 which says that this can happen when transaction log files exceed 4GB. However, the T-Log in this example is only 50MB. It also says the error is fixed by SP1 and I have SP3a. There's also a workaround which involves setting the trace flag to 3111, but that didn't seem to make a difference either
IdeasIf this file "c:\backup\NoteServe4005\NoteServe4005_tlog_200405041500.TRN" exists, delete it.Make sure that you also have sufficient disk space to store the new backup(s). HTH
-- Garrett Taylor wrote: --
This is the error I get for the database named, "NoteServe4005". I get this error for all databases whether I attempt to backup manually or automatically.
[4] Database NoteServe4005: Transaction Log Backup..
Destination: [c:\backup\NoteServe4005\NoteServe4005_tlog_200405041500.TRN
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft][ODBC SQL Server Driver][SQL Server]The media family on device 'c:\backup\NoteServe4005\NoteServe4005_tlog_200405041500.TRN' is incorrectly formed. SQL Server cannot process this media family
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating abnormally
I searched support for the error pulling up only KB:297104 which says that this can happen when transaction log files exceed 4GB. However, the T-Log in this example is only 50MB. It also says the error is fixed by SP1 and I have SP3a. There's also a workaround which involves setting the trace flag to 3111, but that didn't seem to make a difference either
Ideas|||The file doesn't already exist and I have more than 50GB
free where my transaction file is only 50MB.
>--Original Message--
>If this file "c:\backup\NoteServe4005
\NoteServe4005_tlog_200405041500.TRN" exists, delete
it.Make sure that you also have sufficient disk space to
store the new backup(s). HTH.
> -- Garrett Taylor wrote: --
> This is the error I get for the database
named, "NoteServe4005". I get this error for all databases
whether I attempt to backup manually or automatically.
> [4] Database NoteServe4005: Transaction Log Backup...
> Destination: [c:\backup\NoteServe4005
\NoteServe4005_tlog_200405041500.TRN]
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
3241: [Microsoft][ODBC SQL Server Driver][SQL Server]The
media family on device 'c:\backup\NoteServe4005
\NoteServe4005_tlog_200405041500.TRN' is incorrectly
formed. SQL Server cannot process this media family.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
BACKUP LOG is terminating abnormally.
> I searched support for the error pulling up only
KB:297104 which says that this can happen when transaction
log files exceed 4GB. However, the T-Log in this example
is only 50MB. It also says the error is fixed by SP1 and I
have SP3a. There's also a workaround which involves
setting the trace flag to 3111, but that didn't seem to
make a difference either.
> Ideas?
>.
>
[4] Database NoteServe4005: Transaction Log Backup..
Destination: [c:\backup\NoteServe4005\NoteServe4005_tlog_200405041500.TRN
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft][ODBC SQL Server Driver][SQL Server]The media family on device 'c:\backup\NoteServe4005\NoteServe4005_tlog_200405041500.TRN' is incorrectly formed. SQL Server cannot process this media family
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating abnormally
I searched support for the error pulling up only KB:297104 which says that this can happen when transaction log files exceed 4GB. However, the T-Log in this example is only 50MB. It also says the error is fixed by SP1 and I have SP3a. There's also a workaround which involves setting the trace flag to 3111, but that didn't seem to make a difference either
IdeasIf this file "c:\backup\NoteServe4005\NoteServe4005_tlog_200405041500.TRN" exists, delete it.Make sure that you also have sufficient disk space to store the new backup(s). HTH
-- Garrett Taylor wrote: --
This is the error I get for the database named, "NoteServe4005". I get this error for all databases whether I attempt to backup manually or automatically.
[4] Database NoteServe4005: Transaction Log Backup..
Destination: [c:\backup\NoteServe4005\NoteServe4005_tlog_200405041500.TRN
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft][ODBC SQL Server Driver][SQL Server]The media family on device 'c:\backup\NoteServe4005\NoteServe4005_tlog_200405041500.TRN' is incorrectly formed. SQL Server cannot process this media family
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating abnormally
I searched support for the error pulling up only KB:297104 which says that this can happen when transaction log files exceed 4GB. However, the T-Log in this example is only 50MB. It also says the error is fixed by SP1 and I have SP3a. There's also a workaround which involves setting the trace flag to 3111, but that didn't seem to make a difference either
Ideas|||The file doesn't already exist and I have more than 50GB
free where my transaction file is only 50MB.
>--Original Message--
>If this file "c:\backup\NoteServe4005
\NoteServe4005_tlog_200405041500.TRN" exists, delete
it.Make sure that you also have sufficient disk space to
store the new backup(s). HTH.
> -- Garrett Taylor wrote: --
> This is the error I get for the database
named, "NoteServe4005". I get this error for all databases
whether I attempt to backup manually or automatically.
> [4] Database NoteServe4005: Transaction Log Backup...
> Destination: [c:\backup\NoteServe4005
\NoteServe4005_tlog_200405041500.TRN]
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error
3241: [Microsoft][ODBC SQL Server Driver][SQL Server]The
media family on device 'c:\backup\NoteServe4005
\NoteServe4005_tlog_200405041500.TRN' is incorrectly
formed. SQL Server cannot process this media family.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
BACKUP LOG is terminating abnormally.
> I searched support for the error pulling up only
KB:297104 which says that this can happen when transaction
log files exceed 4GB. However, the T-Log in this example
is only 50MB. It also says the error is fixed by SP1 and I
have SP3a. There's also a workaround which involves
setting the trace flag to 3111, but that didn't seem to
make a difference either.
> Ideas?
>.
>
Subscribe to:
Posts (Atom)