Backing up a large '78 GB' database to a compressed folder
gives this error: BackupMedium::ReportIoError: write
failure on backup device '*replaced file name here*.BAK'.
Operating system error 33(The process cannot access the
file because another process has locked a portion of the
file.). The BAK file size should be about 66 GB. This
backup works if the target is an uncompressed folder on
the same drive, and it did work the first time it was used
to the compressed drive. The system reports over 100 GB
free space on this drive.Have a look at the KB article:
INF: SQL Server Databases Not Supported on Compressed Volumes
# 231347
http://support.microsoft.com/?id=231347
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Warren" <bauerwa@.wsdot.wa.gov> wrote in message
news:00ef01c3d947$a43136e0$a601280a@.phx.gbl...
> Backing up a large '78 GB' database to a compressed folder
> gives this error: BackupMedium::ReportIoError: write
> failure on backup device '*replaced file name here*.BAK'.
> Operating system error 33(The process cannot access the
> file because another process has locked a portion of the
> file.). The BAK file size should be about 66 GB. This
> backup works if the target is an uncompressed folder on
> the same drive, and it did work the first time it was used
> to the compressed drive. The system reports over 100 GB
> free space on this drive.sql
Showing posts with label write. Show all posts
Showing posts with label write. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Backup Failed
SQL 2K
We are having an issue with our data warehouse database failing on backup
with
2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write failure
on backup device
'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_d
b_200501230900.BAK'. Operating
system error 33(The process cannot access the file because another process
has locked a portion of the file.).
2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op: Write,
pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db
_200501230900.BAK
2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
BACKUP DATABASE [INFO_PROD] TO DISK =
N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_
db_200501230900.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
Last w
we had the same issue and rebooting the server did helped.
Any thoughts on this lock on the backup file.
Thanks
RobertAre you backing up the file to tape? Could that process be running while
you are trying to backup the database to disk?
Keith
"MS User" <sqlman@.sql.com> wrote in message
news:%23q5TNsiAFHA.3860@.TK2MSFTNGP11.phx.gbl...
> SQL 2K
> We are having an issue with our data warehouse database failing on backup
> with
>
> 2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write
failure
> on backup device
> 'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_d
b_200501230900.BAK'. Operating
> system error 33(The process cannot access the file because another process
> has locked a portion of the file.).
> 2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op:
Write,
> pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
> 0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
> 0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
> D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db
_200501230900.BAK
> 2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
> BACKUP DATABASE [INFO_PROD] TO DISK =
> N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_
db_200501230900.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>
> Last w we had the same issue and rebooting the server did helped.
> Any thoughts on this lock on the backup file.
> Thanks
> Robert
>|||Hi
Anti-Virus and Backup software can hold locks on a file. Check that AV
excludes the SQL files (.MDF, LDF, NDF and BAK)
Check if your backup software is still running it's backup (some badly
behaved backup agents don't release locks if the backup aborts).
Regards
Mike
"MS User" wrote:
> SQL 2K
> We are having an issue with our data warehouse database failing on backup
> with
>
> 2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write failur
e
> on backup device
> 'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_d
b_200501230900.BAK'. Operating
> system error 33(The process cannot access the file because another process
> has locked a portion of the file.).
> 2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op: Writ
e,
> pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
> 0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
> 0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
> D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db
_200501230900.BAK
> 2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
> BACKUP DATABASE [INFO_PROD] TO DISK =
> N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_
db_200501230900.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>
> Last w we had the same issue and rebooting the server did helped.
> Any thoughts on this lock on the backup file.
> Thanks
> Robert
>
>
We are having an issue with our data warehouse database failing on backup
with
2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write failure
on backup device
'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_d
b_200501230900.BAK'. Operating
system error 33(The process cannot access the file because another process
has locked a portion of the file.).
2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op: Write,
pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db
_200501230900.BAK
2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
BACKUP DATABASE [INFO_PROD] TO DISK =
N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_
db_200501230900.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
Last w
we had the same issue and rebooting the server did helped.Any thoughts on this lock on the backup file.
Thanks
RobertAre you backing up the file to tape? Could that process be running while
you are trying to backup the database to disk?
Keith
"MS User" <sqlman@.sql.com> wrote in message
news:%23q5TNsiAFHA.3860@.TK2MSFTNGP11.phx.gbl...
> SQL 2K
> We are having an issue with our data warehouse database failing on backup
> with
>
> 2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write
failure
> on backup device
> 'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_d
b_200501230900.BAK'. Operating
> system error 33(The process cannot access the file because another process
> has locked a portion of the file.).
> 2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op:
Write,
> pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
> 0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
> 0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
> D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db
_200501230900.BAK
> 2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
> BACKUP DATABASE [INFO_PROD] TO DISK =
> N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_
db_200501230900.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>
> Last w
we had the same issue and rebooting the server did helped.> Any thoughts on this lock on the backup file.
> Thanks
> Robert
>|||Hi
Anti-Virus and Backup software can hold locks on a file. Check that AV
excludes the SQL files (.MDF, LDF, NDF and BAK)
Check if your backup software is still running it's backup (some badly
behaved backup agents don't release locks if the backup aborts).
Regards
Mike
"MS User" wrote:
> SQL 2K
> We are having an issue with our data warehouse database failing on backup
> with
>
> 2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write failur
e
> on backup device
> 'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_d
b_200501230900.BAK'. Operating
> system error 33(The process cannot access the file because another process
> has locked a portion of the file.).
> 2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op: Writ
e,
> pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
> 0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
> 0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
> D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db
_200501230900.BAK
> 2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
> BACKUP DATABASE [INFO_PROD] TO DISK =
> N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_
db_200501230900.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>
> Last w
we had the same issue and rebooting the server did helped.> Any thoughts on this lock on the backup file.
> Thanks
> Robert
>
>
Backup Failed
SQL 2K
We are having an issue with our data warehouse database failing on backup
with
2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write failure
on backup device
'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_d
b_200501230900.BAK'. Operating
system error 33(The process cannot access the file because another process
has locked a portion of the file.).
2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op: Write,
pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db
_200501230900.BAK
2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
BACKUP DATABASE [INFO_PROD] TO DISK =
N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_
db_200501230900.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
Last week we had the same issue and rebooting the server did helped.
Any thoughts on this lock on the backup file.
Thanks
RobertAre you backing up the file to tape? Could that process be running while
you are trying to backup the database to disk?
Keith
"MS User" <sqlman@.sql.com> wrote in message
news:%23q5TNsiAFHA.3860@.TK2MSFTNGP11.phx.gbl...
> SQL 2K
> We are having an issue with our data warehouse database failing on backup
> with
>
> 2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write
failure
> on backup device
> 'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_d
b_200501230900.BAK'. Operating
> system error 33(The process cannot access the file because another process
> has locked a portion of the file.).
> 2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op:
Write,
> pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
> 0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
> 0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
> D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db
_200501230900.BAK
> 2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
> BACKUP DATABASE [INFO_PROD] TO DISK =
> N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_
db_200501230900.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>
> Last week we had the same issue and rebooting the server did helped.
> Any thoughts on this lock on the backup file.
> Thanks
> Robert
>|||Hi
Anti-Virus and Backup software can hold locks on a file. Check that AV
excludes the SQL files (.MDF, LDF, NDF and BAK)
Check if your backup software is still running it's backup (some badly
behaved backup agents don't release locks if the backup aborts).
Regards
Mike
"MS User" wrote:
> SQL 2K
> We are having an issue with our data warehouse database failing on backup
> with
>
> 2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write failur
e
> on backup device
> 'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_d
b_200501230900.BAK'. Operating
> system error 33(The process cannot access the file because another process
> has locked a portion of the file.).
> 2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op: Writ
e,
> pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
> 0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
> 0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
> D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db
_200501230900.BAK
> 2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
> BACKUP DATABASE [INFO_PROD] TO DISK =
> N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_
db_200501230900.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>
> Last week we had the same issue and rebooting the server did helped.
> Any thoughts on this lock on the backup file.
> Thanks
> Robert
>
>
We are having an issue with our data warehouse database failing on backup
with
2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write failure
on backup device
'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_d
b_200501230900.BAK'. Operating
system error 33(The process cannot access the file because another process
has locked a portion of the file.).
2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op: Write,
pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db
_200501230900.BAK
2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
BACKUP DATABASE [INFO_PROD] TO DISK =
N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_
db_200501230900.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
Last week we had the same issue and rebooting the server did helped.
Any thoughts on this lock on the backup file.
Thanks
RobertAre you backing up the file to tape? Could that process be running while
you are trying to backup the database to disk?
Keith
"MS User" <sqlman@.sql.com> wrote in message
news:%23q5TNsiAFHA.3860@.TK2MSFTNGP11.phx.gbl...
> SQL 2K
> We are having an issue with our data warehouse database failing on backup
> with
>
> 2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write
failure
> on backup device
> 'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_d
b_200501230900.BAK'. Operating
> system error 33(The process cannot access the file because another process
> has locked a portion of the file.).
> 2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op:
Write,
> pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
> 0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
> 0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
> D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db
_200501230900.BAK
> 2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
> BACKUP DATABASE [INFO_PROD] TO DISK =
> N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_
db_200501230900.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>
> Last week we had the same issue and rebooting the server did helped.
> Any thoughts on this lock on the backup file.
> Thanks
> Robert
>|||Hi
Anti-Virus and Backup software can hold locks on a file. Check that AV
excludes the SQL files (.MDF, LDF, NDF and BAK)
Check if your backup software is still running it's backup (some badly
behaved backup agents don't release locks if the backup aborts).
Regards
Mike
"MS User" wrote:
> SQL 2K
> We are having an issue with our data warehouse database failing on backup
> with
>
> 2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write failur
e
> on backup device
> 'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_d
b_200501230900.BAK'. Operating
> system error 33(The process cannot access the file because another process
> has locked a portion of the file.).
> 2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op: Writ
e,
> pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
> 0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
> 0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
> D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db
_200501230900.BAK
> 2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
> BACKUP DATABASE [INFO_PROD] TO DISK =
> N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_
db_200501230900.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>
> Last week we had the same issue and rebooting the server did helped.
> Any thoughts on this lock on the backup file.
> Thanks
> Robert
>
>
Backup Failed
SQL 2K
We are having an issue with our data warehouse database failing on backup
with
2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write failure
on backup device
'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_20050123 0900.BAK'. Operating
system error 33(The process cannot access the file because another process
has locked a portion of the file.).
2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op: Write,
pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_200501230 900.BAK
2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
BACKUP DATABASE [INFO_PROD] TO DISK =
N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_2005012 30900.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
Last week we had the same issue and rebooting the server did helped.
Any thoughts on this lock on the backup file.
Thanks
Robert
Are you backing up the file to tape? Could that process be running while
you are trying to backup the database to disk?
Keith
"MS User" <sqlman@.sql.com> wrote in message
news:%23q5TNsiAFHA.3860@.TK2MSFTNGP11.phx.gbl...
> SQL 2K
> We are having an issue with our data warehouse database failing on backup
> with
>
> 2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write
failure
> on backup device
> 'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_20050123 0900.BAK'. Operating
> system error 33(The process cannot access the file because another process
> has locked a portion of the file.).
> 2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op:
Write,
> pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
> 0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
> 0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
> D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_200501230 900.BAK
> 2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
> BACKUP DATABASE [INFO_PROD] TO DISK =
> N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_2005012 30900.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>
> Last week we had the same issue and rebooting the server did helped.
> Any thoughts on this lock on the backup file.
> Thanks
> Robert
>
|||Hi
Anti-Virus and Backup software can hold locks on a file. Check that AV
excludes the SQL files (.MDF, LDF, NDF and BAK)
Check if your backup software is still running it's backup (some badly
behaved backup agents don't release locks if the backup aborts).
Regards
Mike
"MS User" wrote:
> SQL 2K
> We are having an issue with our data warehouse database failing on backup
> with
>
> 2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write failure
> on backup device
> 'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_20050123 0900.BAK'. Operating
> system error 33(The process cannot access the file because another process
> has locked a portion of the file.).
> 2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op: Write,
> pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
> 0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
> 0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
> D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_200501230 900.BAK
> 2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
> BACKUP DATABASE [INFO_PROD] TO DISK =
> N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_2005012 30900.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>
> Last week we had the same issue and rebooting the server did helped.
> Any thoughts on this lock on the backup file.
> Thanks
> Robert
>
>
We are having an issue with our data warehouse database failing on backup
with
2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write failure
on backup device
'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_20050123 0900.BAK'. Operating
system error 33(The process cannot access the file because another process
has locked a portion of the file.).
2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op: Write,
pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_200501230 900.BAK
2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
BACKUP DATABASE [INFO_PROD] TO DISK =
N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_2005012 30900.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
Last week we had the same issue and rebooting the server did helped.
Any thoughts on this lock on the backup file.
Thanks
Robert
Are you backing up the file to tape? Could that process be running while
you are trying to backup the database to disk?
Keith
"MS User" <sqlman@.sql.com> wrote in message
news:%23q5TNsiAFHA.3860@.TK2MSFTNGP11.phx.gbl...
> SQL 2K
> We are having an issue with our data warehouse database failing on backup
> with
>
> 2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write
failure
> on backup device
> 'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_20050123 0900.BAK'. Operating
> system error 33(The process cannot access the file because another process
> has locked a portion of the file.).
> 2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op:
Write,
> pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
> 0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
> 0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
> D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_200501230 900.BAK
> 2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
> BACKUP DATABASE [INFO_PROD] TO DISK =
> N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_2005012 30900.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>
> Last week we had the same issue and rebooting the server did helped.
> Any thoughts on this lock on the backup file.
> Thanks
> Robert
>
|||Hi
Anti-Virus and Backup software can hold locks on a file. Check that AV
excludes the SQL files (.MDF, LDF, NDF and BAK)
Check if your backup software is still running it's backup (some badly
behaved backup agents don't release locks if the backup aborts).
Regards
Mike
"MS User" wrote:
> SQL 2K
> We are having an issue with our data warehouse database failing on backup
> with
>
> 2005-01-23 09:53:25.64 spid55 BackupMedium::ReportIoError: write failure
> on backup device
> 'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_20050123 0900.BAK'. Operating
> system error 33(The process cannot access the file because another process
> has locked a portion of the file.).
> 2005-01-23 09:53:25.67 spid55 Internal I/O request 0x3D80DAE8: Op: Write,
> pBuffer: 0x153C0000, Size: 983040, Position: 47255394816, UMS: Internal:
> 0x103, InternalHigh: 0x0, Offset: 0xA41A00, OffsetHigh: 0xB, m_buf:
> 0x153C0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile:
> D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_200501230 900.BAK
> 2005-01-23 09:53:25.69 backup BACKUP failed to complete the command
> BACKUP DATABASE [INFO_PROD] TO DISK =
> N'D:\SqlData\MSSQL$WAREHOUSE1\BACKUP\My_db_2005012 30900.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>
> Last week we had the same issue and rebooting the server did helped.
> Any thoughts on this lock on the backup file.
> Thanks
> Robert
>
>
Sunday, March 25, 2012
backup error - error 112
Hi,
When trying to do a full backup, I get write on
c\xxxxxxx\shipmatedb_20050715xxx - failed status 112.
It allow however to do the backup is I select "overwrite existing media"
The only thing that has changed today was that I shrunk the log file....
regards
g"Myth of Sisyphus)." <teller_117@.hotmail.com> wrote in message
news:jiWBe.11504$6e3.772236@.news20.bellglobal.com. ..
> Hi,
> When trying to do a full backup, I get write on
> c\xxxxxxx\shipmatedb_20050715xxx - failed status 112.
> It allow however to do the backup is I select "overwrite existing media"
>
> The only thing that has changed today was that I shrunk the log file....
>
> regards
>
> g
Error 112 seems to be a space issue - run "NET HELPMSG 112" in a Windows
command prompt to see that. If this doesn't help, I suggest you give some
more details about your MSSQL version, your backup strategy, where you're
backing up to, how much free space you have etc.
Simon|||Simon,
Thank you for the explanation, it turns out my disk space was near
zero...Cleared some large files and all is well.
regards
g
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:42d86323_1@.news.bluewin.ch...
> "Myth of Sisyphus)." <teller_117@.hotmail.com> wrote in message
> news:jiWBe.11504$6e3.772236@.news20.bellglobal.com. ..
> > Hi,
> > When trying to do a full backup, I get write on
> > c\xxxxxxx\shipmatedb_20050715xxx - failed status 112.
> > It allow however to do the backup is I select "overwrite existing media"
> > The only thing that has changed today was that I shrunk the log
file....
> > regards
> > g
> Error 112 seems to be a space issue - run "NET HELPMSG 112" in a Windows
> command prompt to see that. If this doesn't help, I suggest you give some
> more details about your MSSQL version, your backup strategy, where you're
> backing up to, how much free space you have etc.
> Simon
Backup Error
I am getting this error:
Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size: 851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968, m_actualBytes: 0, m_errcode: 112, BackupFile: i:\backups\warehouse_data.bak
BackupMedium::ReportIoError: write failure on backup device 'i:\backups\warehouse_data.bak'. Operating system error 112(There is not enough space on the disk.).
The command (t-sql) causing the error is:
backup database @.dbname to disk=@.backupTo with init
The available space on drive i: is approx 120Gb and the size of the database "warehouse" is apprx: 8Gb, so there is plenty of room on the target drive.
The source drive is rather short of space.
Can someone explain this ?Hi,
Is this I drive a local drive? Can you execute the below comand to check the
space free in I drive.
xp_fixedrives
If you have enogh space try executing the below command to backup from query
analyzer.
backup database <dbname> to disk='i:\dbname.bak' with init
Thanks
Hari
MCDBA
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:A033AA30-FEFB-47BF-A545-95638FC770F8@.microsoft.com...
> I am getting this error:
> Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size:
851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968,
m_actualBytes: 0, m_errcode: 112, BackupFile: i:\backups\warehouse_data.bak
> BackupMedium::ReportIoError: write failure on backup device
'i:\backups\warehouse_data.bak'. Operating system error 112(There is not
enough space on the disk.).
> The command (t-sql) causing the error is:
> backup database @.dbname to disk=@.backupTo with init
> The available space on drive i: is approx 120Gb and the size of the
database "warehouse" is apprx: 8Gb, so there is plenty of room on the target
drive.
> The source drive is rather short of space.
> Can someone explain this ?
>sql
Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size: 851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968, m_actualBytes: 0, m_errcode: 112, BackupFile: i:\backups\warehouse_data.bak
BackupMedium::ReportIoError: write failure on backup device 'i:\backups\warehouse_data.bak'. Operating system error 112(There is not enough space on the disk.).
The command (t-sql) causing the error is:
backup database @.dbname to disk=@.backupTo with init
The available space on drive i: is approx 120Gb and the size of the database "warehouse" is apprx: 8Gb, so there is plenty of room on the target drive.
The source drive is rather short of space.
Can someone explain this ?Hi,
Is this I drive a local drive? Can you execute the below comand to check the
space free in I drive.
xp_fixedrives
If you have enogh space try executing the below command to backup from query
analyzer.
backup database <dbname> to disk='i:\dbname.bak' with init
Thanks
Hari
MCDBA
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:A033AA30-FEFB-47BF-A545-95638FC770F8@.microsoft.com...
> I am getting this error:
> Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size:
851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968,
m_actualBytes: 0, m_errcode: 112, BackupFile: i:\backups\warehouse_data.bak
> BackupMedium::ReportIoError: write failure on backup device
'i:\backups\warehouse_data.bak'. Operating system error 112(There is not
enough space on the disk.).
> The command (t-sql) causing the error is:
> backup database @.dbname to disk=@.backupTo with init
> The available space on drive i: is approx 120Gb and the size of the
database "warehouse" is apprx: 8Gb, so there is plenty of room on the target
drive.
> The source drive is rather short of space.
> Can someone explain this ?
>sql
Backup Error
After applying Win2K SP3 earlier this month, our Veritas
tape backups started failing with the following errors:
BackupMedium::ReportIoError: write failure on backup
device 'VNBU0-2000-2912'. Operating system error 995(The
I/O operation has been aborted because of either a thread
exit or an application request.).
Internal I/O request 0x4431FAE8: Op: Write, pBuffer:
0x28A70200, Size: 512, Position: 0, UMS: Internal:
0x65E5A030, InternalHigh: 0x0, Offset: 0x1, OffsetHigh:
0x4862F628, m_buf: 0x00000012, m_len: 1, m_actualBytes: 0,
m_errcode: 995, BackupFile: VNBU0-2000-2912
I also started getting the following error reported around
the same time of day:
The SQL Server cannot obtain a LOCK resource at this time.
Rerun your statement when there are fewer active users or
ask the system administrator to check the SQL Server lock
and memory configuration..
I am able to do a regular dump to disk with no errors.
Any ideas on resolution here?
Thanks for your time.
MilesI think veritas.com will have more helpful info.
Are you using BackupExec to backup SQL databases?
"Miles" <miles.schor@.echostar.com> wrote in message
news:016b01c36742$ecf70520$a401280a@.phx.gbl...
> After applying Win2K SP3 earlier this month, our Veritas
> tape backups started failing with the following errors:
> BackupMedium::ReportIoError: write failure on backup
> device 'VNBU0-2000-2912'. Operating system error 995(The
> I/O operation has been aborted because of either a thread
> exit or an application request.).
> Internal I/O request 0x4431FAE8: Op: Write, pBuffer:
> 0x28A70200, Size: 512, Position: 0, UMS: Internal:
> 0x65E5A030, InternalHigh: 0x0, Offset: 0x1, OffsetHigh:
> 0x4862F628, m_buf: 0x00000012, m_len: 1, m_actualBytes: 0,
> m_errcode: 995, BackupFile: VNBU0-2000-2912
> I also started getting the following error reported around
> the same time of day:
> The SQL Server cannot obtain a LOCK resource at this time.
> Rerun your statement when there are fewer active users or
> ask the system administrator to check the SQL Server lock
> and memory configuration..
> I am able to do a regular dump to disk with no errors.
> Any ideas on resolution here?
> Thanks for your time.
> Miles
tape backups started failing with the following errors:
BackupMedium::ReportIoError: write failure on backup
device 'VNBU0-2000-2912'. Operating system error 995(The
I/O operation has been aborted because of either a thread
exit or an application request.).
Internal I/O request 0x4431FAE8: Op: Write, pBuffer:
0x28A70200, Size: 512, Position: 0, UMS: Internal:
0x65E5A030, InternalHigh: 0x0, Offset: 0x1, OffsetHigh:
0x4862F628, m_buf: 0x00000012, m_len: 1, m_actualBytes: 0,
m_errcode: 995, BackupFile: VNBU0-2000-2912
I also started getting the following error reported around
the same time of day:
The SQL Server cannot obtain a LOCK resource at this time.
Rerun your statement when there are fewer active users or
ask the system administrator to check the SQL Server lock
and memory configuration..
I am able to do a regular dump to disk with no errors.
Any ideas on resolution here?
Thanks for your time.
MilesI think veritas.com will have more helpful info.
Are you using BackupExec to backup SQL databases?
"Miles" <miles.schor@.echostar.com> wrote in message
news:016b01c36742$ecf70520$a401280a@.phx.gbl...
> After applying Win2K SP3 earlier this month, our Veritas
> tape backups started failing with the following errors:
> BackupMedium::ReportIoError: write failure on backup
> device 'VNBU0-2000-2912'. Operating system error 995(The
> I/O operation has been aborted because of either a thread
> exit or an application request.).
> Internal I/O request 0x4431FAE8: Op: Write, pBuffer:
> 0x28A70200, Size: 512, Position: 0, UMS: Internal:
> 0x65E5A030, InternalHigh: 0x0, Offset: 0x1, OffsetHigh:
> 0x4862F628, m_buf: 0x00000012, m_len: 1, m_actualBytes: 0,
> m_errcode: 995, BackupFile: VNBU0-2000-2912
> I also started getting the following error reported around
> the same time of day:
> The SQL Server cannot obtain a LOCK resource at this time.
> Rerun your statement when there are fewer active users or
> ask the system administrator to check the SQL Server lock
> and memory configuration..
> I am able to do a regular dump to disk with no errors.
> Any ideas on resolution here?
> Thanks for your time.
> Miles
Thursday, March 22, 2012
Backup Error
I am getting this error:
Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size: 851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968, m_actualBytes: 0, m_errcode: 112, BackupFi
le: i:\backups\warehouse_data.bak
BackupMedium::ReportIoError: write failure on backup device 'i:\backups\warehouse_data.bak'. Operating system error 112(There is not enough space on the disk.).
The command (t-sql) causing the error is:
backup database @.dbname to disk=@.backupTo with init
The available space on drive i: is approx 120Gb and the size of the database "warehouse" is apprx: 8Gb, so there is plenty of room on the target drive.
The source drive is rather short of space.
Can someone explain this ?
Hi,
Is this I drive a local drive? Can you execute the below comand to check the
space free in I drive.
xp_fixedrives
If you have enogh space try executing the below command to backup from query
analyzer.
backup database <dbname> to disk='i:\dbname.bak' with init
Thanks
Hari
MCDBA
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:A033AA30-FEFB-47BF-A545-95638FC770F8@.microsoft.com...
> I am getting this error:
> Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size:
851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968,
m_actualBytes: 0, m_errcode: 112, BackupFile: i:\backups\warehouse_data.bak
> BackupMedium::ReportIoError: write failure on backup device
'i:\backups\warehouse_data.bak'. Operating system error 112(There is not
enough space on the disk.).
> The command (t-sql) causing the error is:
> backup database @.dbname to disk=@.backupTo with init
> The available space on drive i: is approx 120Gb and the size of the
database "warehouse" is apprx: 8Gb, so there is plenty of room on the target
drive.
> The source drive is rather short of space.
> Can someone explain this ?
>
Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size: 851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968, m_actualBytes: 0, m_errcode: 112, BackupFi
le: i:\backups\warehouse_data.bak
BackupMedium::ReportIoError: write failure on backup device 'i:\backups\warehouse_data.bak'. Operating system error 112(There is not enough space on the disk.).
The command (t-sql) causing the error is:
backup database @.dbname to disk=@.backupTo with init
The available space on drive i: is approx 120Gb and the size of the database "warehouse" is apprx: 8Gb, so there is plenty of room on the target drive.
The source drive is rather short of space.
Can someone explain this ?
Hi,
Is this I drive a local drive? Can you execute the below comand to check the
space free in I drive.
xp_fixedrives
If you have enogh space try executing the below command to backup from query
analyzer.
backup database <dbname> to disk='i:\dbname.bak' with init
Thanks
Hari
MCDBA
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:A033AA30-FEFB-47BF-A545-95638FC770F8@.microsoft.com...
> I am getting this error:
> Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size:
851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968,
m_actualBytes: 0, m_errcode: 112, BackupFile: i:\backups\warehouse_data.bak
> BackupMedium::ReportIoError: write failure on backup device
'i:\backups\warehouse_data.bak'. Operating system error 112(There is not
enough space on the disk.).
> The command (t-sql) causing the error is:
> backup database @.dbname to disk=@.backupTo with init
> The available space on drive i: is approx 120Gb and the size of the
database "warehouse" is apprx: 8Gb, so there is plenty of room on the target
drive.
> The source drive is rather short of space.
> Can someone explain this ?
>
Backup Error
I am getting this error:
Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size: 85196
8, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x
FFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968, m_actualBytes:
0, m_errcode: 112, BackupFi
le: i:\backups\warehouse_data.bak
BackupMedium::ReportIoError: write failure on backup device 'i:\backups\ware
house_data.bak'. Operating system error 112(There is not enough space on the
disk.).
The command (t-sql) causing the error is:
backup database @.dbname to disk=@.backupTo with init
The available space on drive i: is approx 120Gb and the size of the database
"warehouse" is apprx: 8Gb, so there is plenty of room on the target drive.
The source drive is rather short of space.
Can someone explain this ?Hi,
Is this I drive a local drive? Can you execute the below comand to check the
space free in I drive.
xp_fixedrives
If you have enogh space try executing the below command to backup from query
analyzer.
backup database <dbname> to disk='i:\dbname.bak' with init
Thanks
Hari
MCDBA
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:A033AA30-FEFB-47BF-A545-95638FC770F8@.microsoft.com...
> I am getting this error:
> Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size:
851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968,
m_actualBytes: 0, m_errcode: 112, BackupFile: i:\backups\warehouse_data.bak
> BackupMedium::ReportIoError: write failure on backup device
'i:\backups\warehouse_data.bak'. Operating system error 112(There is not
enough space on the disk.).
> The command (t-sql) causing the error is:
> backup database @.dbname to disk=@.backupTo with init
> The available space on drive i: is approx 120Gb and the size of the
database "warehouse" is apprx: 8Gb, so there is plenty of room on the target
drive.
> The source drive is rather short of space.
> Can someone explain this ?
>
Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size: 85196
8, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x
FFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968, m_actualBytes:
0, m_errcode: 112, BackupFi
le: i:\backups\warehouse_data.bak
BackupMedium::ReportIoError: write failure on backup device 'i:\backups\ware
house_data.bak'. Operating system error 112(There is not enough space on the
disk.).
The command (t-sql) causing the error is:
backup database @.dbname to disk=@.backupTo with init
The available space on drive i: is approx 120Gb and the size of the database
"warehouse" is apprx: 8Gb, so there is plenty of room on the target drive.
The source drive is rather short of space.
Can someone explain this ?Hi,
Is this I drive a local drive? Can you execute the below comand to check the
space free in I drive.
xp_fixedrives
If you have enogh space try executing the below command to backup from query
analyzer.
backup database <dbname> to disk='i:\dbname.bak' with init
Thanks
Hari
MCDBA
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:A033AA30-FEFB-47BF-A545-95638FC770F8@.microsoft.com...
> I am getting this error:
> Internal I/O request 0x05097428: Op: Write, pBuffer: 0x07400000, Size:
851968, Position: 4294580736, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0xFFFA1A00, OffsetHigh: 0x0, m_buf: 0x07400000, m_len: 851968,
m_actualBytes: 0, m_errcode: 112, BackupFile: i:\backups\warehouse_data.bak
> BackupMedium::ReportIoError: write failure on backup device
'i:\backups\warehouse_data.bak'. Operating system error 112(There is not
enough space on the disk.).
> The command (t-sql) causing the error is:
> backup database @.dbname to disk=@.backupTo with init
> The available space on drive i: is approx 120Gb and the size of the
database "warehouse" is apprx: 8Gb, so there is plenty of room on the target
drive.
> The source drive is rather short of space.
> Can someone explain this ?
>
Monday, March 19, 2012
Backup database with SQL to backup directory
I had write out a sql command to backup the database.
BACKUP DATABASE NorthWind
to disk = 'c:\NorthWind_20031113.bak'
with name = 'NorthWind_backup'
However, I intend to backup the database to the SQL server default backup directory, "\MSSQL7\Backup". How can I do that? Given that I don't know the SQL server were installed in C drive or D drive.
Another question was, can I do a integrity check using the sql command on the backup copy that i had created??
I'll be appreciate on the help that you offered.
Thanks.I tried this and it works, ie put the backup into the MSSQL/Backup folder
BACKUP DATABASE NorthWind
to disk = 'NorthWind_20031113.bak'
with name = 'NorthWind_backup'
Trick seems to be to not to specify a location and by default it used the backup folder
Mark|||Thank for your reply. I had try on the solution you given, however, the backup copy was placed to my "winnt\system32" directory. I had try both on my own pc and to backup remotely on the database server. Both of the trial was same where the backup copy being placed to the window system directory.|||The Holy book says :
If a relative path name is entered for a backup to disk, the backup file is placed in the default backup directory. This directory is set during installation and stored in the BackupDirectory registry key under
KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer.
Reffering again from the Holy Book for your second question
RESTORE VERIFYONLY
Verifies the backup but does not restore the backup. Checks to see that the backup set is complete and that all volumes are readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. If the backup is valid, Microsoft SQL Server 2000 returns the message: "The backup set is valid."
Syntax
RESTORE VERIFYONLY
FROM < backup_device > [ ,...n ]
[ WITH
[ FILE = file_number ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] LOADHISTORY ]
[ [ , ] PASSWORD = { password | @.password_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @.mediapassword_variable } ]
[ [ , ] { NOREWIND | REWIND } ]
]
< backup_device > ::=
{
{ 'logical_backup_device_name' | @.logical_backup_device_name_var }
| { DISK | TAPE } =
{ 'physical_backup_device_name' | @.physical_backup_device_name_var }
}|||If a relative path name is entered for a backup to disk, the backup file is placed in the default backup directory. This directory is set during installation and stored in the BackupDirectory registry key under
KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer.
I also found the statement above at the "Holy Book", however, i can't really understand what it means by relative path name. If I had to go to registry to read for the path, then as I know I can't do it.
Actually now I'm writing a simple program to do the database backup at the client pc and the database server only have MSDE but no enterprise manager. So, I can't check for the backup path from the registry value cause my program was running at the client pc.
I'm really grateful for the help you offered. Thanks for the answer for the second question.
:D|||Relative path means ... specifying no drive or directory .. just the filename|||I had tried it before, but the backup copy were being create at the system directory, "c:\winnt\system32" insteed of the SQL Backup folder.|||what does the BackupDirectory registry key under
KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer point to ?
You can see it by opening regedit and navigating to the key|||I check on it already, is pointing to the sql path. D:\MSSQL7\Backup\|||Originally posted by coffytan
I check on it already, is pointing to the sql path. D:\MSSQL7\Backup\
Still backup gos to systerm Winnt ,, wierd !!!!!!!!
BACKUP DATABASE NorthWind
to disk = 'c:\NorthWind_20031113.bak'
with name = 'NorthWind_backup'
However, I intend to backup the database to the SQL server default backup directory, "\MSSQL7\Backup". How can I do that? Given that I don't know the SQL server were installed in C drive or D drive.
Another question was, can I do a integrity check using the sql command on the backup copy that i had created??
I'll be appreciate on the help that you offered.
Thanks.I tried this and it works, ie put the backup into the MSSQL/Backup folder
BACKUP DATABASE NorthWind
to disk = 'NorthWind_20031113.bak'
with name = 'NorthWind_backup'
Trick seems to be to not to specify a location and by default it used the backup folder
Mark|||Thank for your reply. I had try on the solution you given, however, the backup copy was placed to my "winnt\system32" directory. I had try both on my own pc and to backup remotely on the database server. Both of the trial was same where the backup copy being placed to the window system directory.|||The Holy book says :
If a relative path name is entered for a backup to disk, the backup file is placed in the default backup directory. This directory is set during installation and stored in the BackupDirectory registry key under
KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer.
Reffering again from the Holy Book for your second question
RESTORE VERIFYONLY
Verifies the backup but does not restore the backup. Checks to see that the backup set is complete and that all volumes are readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. If the backup is valid, Microsoft SQL Server 2000 returns the message: "The backup set is valid."
Syntax
RESTORE VERIFYONLY
FROM < backup_device > [ ,...n ]
[ WITH
[ FILE = file_number ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] LOADHISTORY ]
[ [ , ] PASSWORD = { password | @.password_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @.mediapassword_variable } ]
[ [ , ] { NOREWIND | REWIND } ]
]
< backup_device > ::=
{
{ 'logical_backup_device_name' | @.logical_backup_device_name_var }
| { DISK | TAPE } =
{ 'physical_backup_device_name' | @.physical_backup_device_name_var }
}|||If a relative path name is entered for a backup to disk, the backup file is placed in the default backup directory. This directory is set during installation and stored in the BackupDirectory registry key under
KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer.
I also found the statement above at the "Holy Book", however, i can't really understand what it means by relative path name. If I had to go to registry to read for the path, then as I know I can't do it.
Actually now I'm writing a simple program to do the database backup at the client pc and the database server only have MSDE but no enterprise manager. So, I can't check for the backup path from the registry value cause my program was running at the client pc.
I'm really grateful for the help you offered. Thanks for the answer for the second question.
:D|||Relative path means ... specifying no drive or directory .. just the filename|||I had tried it before, but the backup copy were being create at the system directory, "c:\winnt\system32" insteed of the SQL Backup folder.|||what does the BackupDirectory registry key under
KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer point to ?
You can see it by opening regedit and navigating to the key|||I check on it already, is pointing to the sql path. D:\MSSQL7\Backup\|||Originally posted by coffytan
I check on it already, is pointing to the sql path. D:\MSSQL7\Backup\
Still backup gos to systerm Winnt ,, wierd !!!!!!!!
Sunday, March 11, 2012
BackUp database
Hi all
I use this code to bak up database
and its working fine but i want run this code via ASP
can anyone write it for me in ASP format?
USE master
EXEC sp_addumpdevice 'disk', 'test_2',
'C:\test_2.dat'
USE master
EXEC sp_addumpdevice 'disk', 'testlog',
'C:\testlog.dat'
BACKUP DATABASE test TO test_2
BACKUP LOG test TO testlog
thanksAll you need to do is put each TSQL command as relevant ASP command.
In your case, you need to execute 4 commands so you would need
SQLStmt, SQLstmt1,2,3 and 4 lots of RS = Connection.Execute(SQLStmt)
e.g. - example of one connection & execution :
Dim UID
Dim SQLStmt, SQLStmt1, SQLStmt2, SQLStmt3
Dim Connection,
Dim RS, RS1, RS2, RS3
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "PROVIDER=SQLOLEDB;DATA SOURCE=server1;UID=test;PWD=password;DATABASE=data base1"
SQLStmt = "SELECT * FROM TBL_Test where server_name = 'SERVER1' and job_run_time > (getdate() - 7)"
Set RS = Connection.Execute(SQLStmt)
I use this code to bak up database
and its working fine but i want run this code via ASP
can anyone write it for me in ASP format?
USE master
EXEC sp_addumpdevice 'disk', 'test_2',
'C:\test_2.dat'
USE master
EXEC sp_addumpdevice 'disk', 'testlog',
'C:\testlog.dat'
BACKUP DATABASE test TO test_2
BACKUP LOG test TO testlog
thanksAll you need to do is put each TSQL command as relevant ASP command.
In your case, you need to execute 4 commands so you would need
SQLStmt, SQLstmt1,2,3 and 4 lots of RS = Connection.Execute(SQLStmt)
e.g. - example of one connection & execution :
Dim UID
Dim SQLStmt, SQLStmt1, SQLStmt2, SQLStmt3
Dim Connection,
Dim RS, RS1, RS2, RS3
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "PROVIDER=SQLOLEDB;DATA SOURCE=server1;UID=test;PWD=password;DATABASE=data base1"
SQLStmt = "SELECT * FROM TBL_Test where server_name = 'SERVER1' and job_run_time > (getdate() - 7)"
Set RS = Connection.Execute(SQLStmt)
Subscribe to:
Posts (Atom)