Error while defining USB dribe as backup device in MSSQL

[Former Member]

Hi all, i am facing this error when i am creating a new backup device in MSSQL and while watching media contents.

System.Data.SqlClient.SqlError: Cannot open backup device 'SAPMSQLDB(E:\SAPBackup\SAPMSQLDB.bak)'. Operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Smo)

and i am getting this error in DB13

"Cannot open backup device 'SAPMSQLDB(E:\SAPBackup\SAPMSQLDB.bak)'. Oper"

when you are creating a new device what should be the file name ???? i specified a file name but its not in the folder i think this might be the cause but i dont know what is the name of file or do i need to prepare the folder i mean to create some temporary files?

appreiate any reply.

thanks

Mani

[clas_hortien · Accepted solution]

Hi,

the problem is, that the SQL Server service has to see the device, not the user that is performing the backup. I assume, that the USB device is not accessible for the startup account of the SQL Server and therefor not accessible.

Can you run

use master

exec xp_cmdshell 'dir e:'

go

Regards

Clas

[Former Member · Accepted solution]

this command is runnig successfully

use master

exec xp_cmdshell 'dir e:'

go

but i m still getting this error ( or information not sure ) in MSSQL management studio when defining new device

System.Data.SqlClient.SqlError: The volume on device 'test(E:\SAPMSQLbackup\test.bak)' is empty. (Microsoft.SqlServer.Smo)

and i am getting this in DB13 preview tab

DBCC execution completed. If DBCC printed error messages, contact your system

administrator.DBCC execution completed. If DBCC printed error messages, contact your system

administrator.The volume on device 'test(E:\SAPMSQLbackup\test.bak)' is empty.

is it an error or its fine.

Mani

[markus_doehr2 · Accepted solution]

- is the filesystem formatted as NTFS now?

- does the user under which the database server runs write permissions for that directory?

Markus

[Former Member · Accepted solution]

Well i executed Full database backup

i have few question

1. The output is only one file or more files as i have only one file called filename.bak aftre backup from SAP?

2. below is the detail of DB12 output, DB13 and error log

in DB12 it shows its successfully executed with code ok.

in DB13 PREVIEW tab here is the log it shows

I am not sure it executed succesfully, it created backup in one file called DBNOV.bak

exec msdb..sp_add_job @originating_server = '(local)',@job_name = "SAP CCMS Full DB Backup of

SID [20081112160619-3-173000]",@enabled = 1,@description = "SAP CCMS JOB",@start_step_id = 1,

@category_name = NULL,@category_id = NULL

exec msdb..sp_add_jobstep @job_id = null,@job_name = 'SAP CCMS Full DB Backup of SID

[20081112160619-3-173000]',@step_id = 1,@step_name = 'CCMS-step 1',@subsystem =

'TSQL',@command = 'declare @exeStmt nvarchar(2000) exec sid.sap_backup_databases

@dbList="SID",@r3Db="SID",@bDev="USBDB",@expDays= 27,@bkupChecksum="N",@jobName= "SAP CCMS

Full DB Backup of SID [20081112160619-3-173000]",@bkupType="F",@nativeBkup="N",@exeDate =

"20081112173000",@bkupSim = "Y",@format = 0,@init = 0,@bkupDb = "SID",@unload = 0,@exeStmt =

@exeStmt OUTPUT',@database_name = 'SID',@on_success_action = 3

BACKUP DATABASE SID TO USBDB WITH

DESCRIPTION = 'SAP CCMS Full DB Backup of SID [20081112160619-3-173000]',RETAINDAYS=27,

NOINIT,

NAME = 'Sap backup on 20081112 21:27:40',NOUNLOAD

exec msdb..sp_add_jobstep @job_id = null,@job_name = 'SAP CCMS Full DB Backup of SID

[20081112160619-3-173000]',@step_id = 2,@step_name = 'CCMS-step 2',@subsystem =

'TSQL',@command = 'declare @exeStmt nvarchar(2000) exec sid.sap_verify_backups @nativeBkup =

"N",@bkupSim = "Y",@bDev = "USBDB",@bkupChecksum = "N",@exeDate = "20081112173000",@unload =

0,@dbCnt =1,@exeStmt = @exeStmt OUTPUT',@database_name = 'SID'

RESTORE VERIFYONLY FROM USBDB WITH FILE = X ,NOUNLOAD

exec msdb..sp_add_jobschedule @job_id = null,@job_name = 'SAP CCMS Full DB Backup of SID

[20081112160619-3-173000]',@name = 'CCMS-sche1',@enabled = 1,@freq_type =1,@freq_interval =

1,@freq_subday_type = 0,@freq_subday_interval = 0,@active_start_date =

20081112,@active_start_time = 173000,@freq_recurrence_factor = 0

here is the log file snapshot

2008-11-12 17:30:01.42 spid83 DBCC TRACEON 3605, server process ID (SPID) 83. This is an informational message only; no user action is required.

2008-11-12 18:21:59.35 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:21:59.35 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:21:59.62 spid85 Error: 18204, Severity: 16, State: 1.

2008-11-12 18:21:59.62 spid85 BackupDiskFile::OpenMedia: Backup device 'E:\SAPMSQLbackup\DBNOV.bak' failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.).

2008-11-12 18:22:09.73 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:22:09.73 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:22:09.85 spid85 Error: 18204, Severity: 16, State: 1.

2008-11-12 18:22:09.85 spid85 BackupDiskFile::OpenMedia: Backup device 'E:\SAPMSQLbackup\DBNOV.bak' failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.).

2008-11-12 18:24:05.09 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:24:05.09 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:24:05.18 spid85 Error: 18204, Severity: 16, State: 1.

2008-11-12 18:24:05.18 spid85 BackupDiskFile::OpenMedia: Backup device 'E:\SAPMSQLbackup\DBNOV.bak' failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.).

2008-11-12 18:24:07.40 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:24:07.40 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:24:07.56 spid85 Error: 18204, Severity: 16, State: 1.

2008-11-12 18:24:07.56 spid85 BackupDiskFile::OpenMedia: Backup device 'E:\SAPMSQLbackup\DBNOV.bak' failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.).

2008-11-12 18:24:09.03 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:24:09.03 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:24:09.15 spid85 Error: 18204, Severity: 16, State: 1.

2008-11-12 18:24:09.15 spid85 BackupDiskFile::OpenMedia: Backup device 'E:\SAPMSQLbackup\DBNOV.bak' failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.).

2008-11-12 18:24:11.51 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:24:11.51 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:24:11.65 spid85 Error: 18204, Severity: 16, State: 1.

2008-11-12 18:24:11.65 spid85 BackupDiskFile::OpenMedia: Backup device 'E:\SAPMSQLbackup\DBNOV.bak' failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.).

2008-11-12 18:25:08.59 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:25:08.61 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:25:20.51 spid89 DBCC TRACEON 3605, server process ID (SPID) 89. This is an informational message only; no user action is required.

2008-11-12 18:25:27.00 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:25:27.00 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:32:26.91 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:32:26.91 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:32:27.05 spid85 Error: 18204, Severity: 16, State: 1.

2008-11-12 18:32:27.05 spid85 BackupDiskFile::OpenMedia: Backup device 'E:\SAPMSQLbackup\DBNOV.bak' failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.).

2008-11-12 18:36:51.51 spid91 DBCC TRACEON 3605, server process ID (SPID) 91. This is an informational message only; no user action is required.

2008-11-12 18:36:51.51 spid91 DBCC TRACEON 3605, server process ID (SPID) 91. This is an informational message only; no user action is required.

2008-11-12 18:36:51.65 spid91 Error: 18204, Severity: 16, State: 1.

2008-11-12 18:36:51.65 spid91 BackupDiskFile::OpenMedia: Backup device 'E:\SAPMSQLbackup\DBNOV.bak' failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.).

2008-11-12 18:37:12.04 spid91 DBCC TRACEON 3605, server process ID (SPID) 91. This is an informational message only; no user action is required.

2008-11-12 18:37:12.04 spid91 DBCC TRACEON 3605, server process ID (SPID) 91. This is an informational message only; no user action is required.

2008-11-12 18:37:12.16 spid91 Error: 18204, Severity: 16, State: 1.

2008-11-12 18:37:12.16 spid91 BackupDiskFile::OpenMedia: Backup device 'E:\SAPMSQLbackup\DBNOV.bak' failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.).

2008-11-12 18:37:21.02 spid91 DBCC TRACEON 3605, server process ID (SPID) 91. This is an informational message only; no user action is required.

2008-11-12 18:37:21.04 spid91 DBCC TRACEON 3605, server process ID (SPID) 91. This is an informational message only; no user action is required.

2008-11-12 18:37:21.18 spid91 Error: 18204, Severity: 16, State: 1.

2008-11-12 18:37:21.18 spid91 BackupDiskFile::OpenMedia: Backup device 'E:\SAPMSQLbackup\DBNOV.bak' failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.).

2008-11-12 18:44:44.23 spid92 DBCC TRACEON 3605, server process ID (SPID) 92. This is an informational message only; no user action is required.

2008-11-12 18:45:24.42 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:45:24.42 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:45:24.42 spid85 Error: 18204, Severity: 16, State: 1.

2008-11-12 18:45:24.42 spid85 BackupDiskFile::OpenMedia: Backup device 'E:\SAPMSQLbackup\test.bak' failed to open. Operating system error 2(The system cannot find the file specified.).

2008-11-12 18:45:32.78 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:45:32.78 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:45:32.78 spid85 Error: 18204, Severity: 16, State: 1.

2008-11-12 18:45:32.78 spid85 BackupDiskFile::OpenMedia: Backup device 'E:\SAPMSQLbackup\test.bak' failed to open. Operating system error 2(The system cannot find the file specified.).

2008-11-12 18:45:39.70 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:45:39.70 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 18:45:39.84 spid85 Error: 18204, Severity: 16, State: 1.

2008-11-12 18:45:39.84 spid85 BackupDiskFile::OpenMedia: Backup device 'E:\SAPMSQLbackup\DBNOV.bak' failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.).

2008-11-12 19:50:27.41 Backup Database backed up. Database: SID, creation date(time): 2008/05/07(18:32:21), pages dumped: 26905751, first LSN: 9854:10543:202, last LSN: 9854:18731:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'USBDB'}). This is an informational message only. No user action is required.

2008-11-12 19:50:27.55 spid83 DBCC TRACEON 3605, server process ID (SPID) 83. This is an informational message only; no user action is required.

2008-11-12 21:25:21.51 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 21:25:21.60 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 21:27:40.86 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

2008-11-12 21:27:40.86 spid85 DBCC TRACEON 3605, server process ID (SPID) 85. This is an informational message only; no user action is required.

exec msdb..sp_add_jobserver @job_name = 'SAP CCMS Full DB Backup of SID

[20081112160619-3-173000]',@server_name = '(local)'

thanks a lot for your time

Mani

[markus_doehr2]

Is the USB drive formatted as NTFS?

Markus

[Former Member]

No its Fat32, do i need to format it to NTFS??

and do i need to create file of type .bak too on the destination folder?

[markus_doehr2]

I would format (or convert) the drive to NTFS, it MUCH faster with bigger files.

Markus

[Former Member]

Does it have anything to do with my error in configuring device?? i hav't got answer on my second question for file name and without file name it is not going to finish device configuration.

Mani

[Former Member]

Another reason to format as NTFS is that on Fat32 you can not create files larger than 4GB minus one byte

http://en.wikipedia.org/wiki/File_Allocation_Table

I know this because I can't install IDES on MaxDB in my iPod ....... what a shame of useless device

good luck