Backup File I/o Error

Guys,

Apologies if this is in the wrong section. I have a SQL 2000 database which I backup to a folder on the hard disk using the following command in a Server Agent:

BACKUP DATABASE [Multilog] TO DISK = N'C:\MSSQL7\BACKUP\multilog_backup' WITH INIT , NOUNLOAD , NAME = N'Multilog backup', NOSKIP , STATS = 10, NOFORMAT

Recently it has been failing with the following error message:

Nonrecoverable I/O error occurred on file 'C:\Multilog Database Files\Data\MultiLog_data.MDF'. [SQLSTATE 42000] (Error 3271) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Any idea what could be causing this?

Any help would be greatly appreciated.

Sean.

Try some of the method(s) in the following kb:

http://support.microsoft.com/default.aspx/kb/904804

Is this an SP4 server? Looks like it may have been upgraded from SQL 7.0, is that correct?

|||

Chad,

Just noticed the following error on the logs:

I/O error 23(Data error (cyclic redundancy check).) detected during read of BUF pointer = 0x14243d80, page ptr = 0x1607c000, pageid = (0x1:0x1ae), dbid = 5, status = 0x801, file = C:\Multilog Database Files\Data\MultiLog_data.MDF..

Would this indicate a fault with the actual database file? Any idea what could be done to check and fix?

|||

Yeah, that would indicate a data integrity issue within your DB file. Try running a DBCC CHECKDB on the database in question and see what you get for output. Depending on the output of that, you could try using the FIX options with the CHECKDB command, rebuilding indexes, etc.

Backup Error

Hi Guys,
I am facing a problem with sql server 2000 sp3 backup to network.This backuP
is as part of scheduled backup to another server in the same network and was
working fine for the last 3 yrs But Recently the backUP is failing with
following errors
BackupMedium::ReportIoError: write failure on backup device
'\\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK'.
Operating system error 121(The semaphore timeout period has expired.).
Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
m_actualBytes: 0, m_errcode: 121, BackupFile:
\\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK
I have formateed the destination drive to where backup is taking place but
still same error.
the backup file size is 8.5 GB.
Pls Aedvice.Not to alarm you but I found something out on the web about this strange
error...
http://www.thescripts.com/forum/thread78842.html
might worth the look...
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> a écrit
dans le message de news:
281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.|||my daily backup failed one night after I applied one of monthly patches from
MS. Removing the patch fixed the problem. I called MS. At first they
asked me to update the drivers but I did everything before I called them.
Then they blamed the hardware but I threw back I was using a Compaq server
with compatible h/w to run the system per MS. Later they showed me to
generate a server dump and sent it to MS.
After more than a week with lot of calls, and 3 dumps sent to MS, they said
it was a bug but won't fix it since it just happen to this server with this
controller.
p.s. my server is Compaq DL380, windows 2000, sql 2000 sp4.
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30.231\dbbackups\rptserver\Revenues_db_200602280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.

Backup Error

Hi Guys,
I am facing a problem with sql server 2000 sp3 backup to network.This backuP
is as part of scheduled backup to another server in the same network and was
working fine for the last 3 yrs But Recently the backUP is failing with
following errors
BackupMedium::ReportIoError: write failure on backup device
'\\192.168.30.231\dbbackups\rptserver\Revenues_db_ 200602280100.BAK'.
Operating system error 121(The semaphore timeout period has expired.).
Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
m_actualBytes: 0, m_errcode: 121, BackupFile:
\\192.168.30.231\dbbackups\rptserver\Revenues_db_2 00602280100.BAK
I have formateed the destination drive to where backup is taking place but
still same error.
the backup file size is 8.5 GB.
Pls Aedvice.
Not to alarm you but I found something out on the web about this strange
error...
http://www.thescripts.com/forum/thread78842.html
might worth the look...
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> a crit
dans le message de news:
281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30.231\dbbackups\rptserver\Revenues_db_ 200602280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30.231\dbbackups\rptserver\Revenues_db_2 00602280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.
|||my daily backup failed one night after I applied one of monthly patches from
MS. Removing the patch fixed the problem. I called MS. At first they
asked me to update the drivers but I did everything before I called them.
Then they blamed the hardware but I threw back I was using a Compaq server
with compatible h/w to run the system per MS. Later they showed me to
generate a server dump and sent it to MS.
After more than a week with lot of calls, and 3 dumps sent to MS, they said
it was a bug but won't fix it since it just happen to this server with this
controller.
p.s. my server is Compaq DL380, windows 2000, sql 2000 sp4.
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30.231\dbbackups\rptserver\Revenues_db_ 200602280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30.231\dbbackups\rptserver\Revenues_db_2 00602280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.

Backup Error

Hi Guys,
I am facing a problem with sql server 2000 sp3 backup to network.This backuP
is as part of scheduled backup to another server in the same network and was
working fine for the last 3 yrs But Recently the backUP is failing with
following errors
BackupMedium::ReportIoError: write failure on backup device
'\\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK'.
Operating system error 121(The semaphore timeout period has expired.).
Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
m_actualBytes: 0, m_errcode: 121, BackupFile:
\\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK
I have formateed the destination drive to where backup is taking place but
still same error.
the backup file size is 8.5 GB.
Pls Aedvice.Not to alarm you but I found something out on the web about this strange
error...
http://www.thescripts.com/forum/thread78842.html
might worth the look...
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> a crit
dans le message de news:
281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.|||my daily backup failed one night after I applied one of monthly patches from
MS. Removing the patch fixed the problem. I called MS. At first they
asked me to update the drivers but I did everything before I called them.
Then they blamed the hardware but I threw back I was using a Compaq server
with compatible h/w to run the system per MS. Later they showed me to
generate a server dump and sent it to MS.
After more than a week with lot of calls, and 3 dumps sent to MS, they said
it was a bug but won't fix it since it just happen to this server with this
controller.
p.s. my server is Compaq DL380, windows 2000, sql 2000 sp4.
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:281CE432-4B50-44CE-A299-19653E1AEF37@.microsoft.com...
> Hi Guys,
> I am facing a problem with sql server 2000 sp3 backup to network.This
> backuP
> is as part of scheduled backup to another server in the same network and
> was
> working fine for the last 3 yrs But Recently the backUP is failing with
> following errors
> BackupMedium::ReportIoError: write failure on backup device
> '\\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK'.
> Operating system error 121(The semaphore timeout period has expired.).
> Internal I/O request 0x3CF14628: Op: Write, pBuffer: 0x08260000, Size:
> 983040, Position: 12786176, UMS: Internal: 0x0, InternalHigh: 0xF0000,
> Offset: 0xC31A00, OffsetHigh: 0x0, m_buf: 0x08260000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 121, BackupFile:
> \\192.168.30. 231\dbbackups\rptserver\Revenues_db_2006
02280100.BAK
> I have formateed the destination drive to where backup is taking place but
> still same error.
> the backup file size is 8.5 GB.
> Pls Aedvice.

Backup and restore using ssis

Hi,

I am trying to take the backup of a database, and restore it over a diff database (with same schema).

i am new to SSIS can you guys give me some pointers.

also, i would like to do a daily update of data from one table in a db to a table in another db with same schema; only the rows with latest edit time must exist, i have a column in both tables which records the last editing time of a row....

Help....

Regards

Mave

From a 50,000 foot level. If you have a time stamp value, you could store the last timestamp value processed in a table some where. When the package runs it could grab the current max timestamp value, then get all the records with a time stamp value greater than the last time stamp value and less than or equal to the current max. For each record you would perform an update or insert as appropriate (note that deletes will need to be handled another way) and finally store the current max as the last max.

For deletes you could create a table of id's and timestamps. On the table you're auditing place a trigger that just inserts the ID of the deleted records. Grab the records deleted since last run and delete them in the destination table like we did above for the inserts and updates and then save the last deleted timestamp.

Hope this helps.

Back-Up and restore

Hi Guys,
I have a database, which I recently backed up. Now I would like to
restore this back-up on another server. So basically I m trying to
create a copy of my database on the other server.
How can I restore this back-up file on the other server. When I try
doing that from the Enterprise Manager, it says that I have to choose
from an existing backup file. I m using SQL Server 2000 on Win 2K.
So basically what I want is to create the database from scratch on the
new server using that back up file from the other database. Can somebody
tell me the procedure for this. I m a newbie at SQL Server.
Thanks.
Ricky
--
Posted via http://dbforums.comRicky, it should be fairly straight forward. Use Enterprise Manager and
fire off the restore wizard. Just restore database from device and navigate
to the backup file. If you have problems, I highly recommend going to the
Books online and lookup the topic "RESTORE DATABASE" and do the restore from
Query Analyzer in T-SQL. You may find T-SQL a little more difficult, but
it's more rewarding and more flexible that the GUI for all database
functions. The only thing that comed to mind that I've never done with QA
is to get a table in design view and rearrange the column order... and that
is just cosmetic anyway.
hth,
Eric
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3082099.1057594899@.dbforums.com...
> Hi Guys,
> I have a database, which I recently backed up. Now I would like to
> restore this back-up on another server. So basically I m trying to
> create a copy of my database on the other server.
> How can I restore this back-up file on the other server. When I try
> doing that from the Enterprise Manager, it says that I have to choose
> from an existing backup file. I m using SQL Server 2000 on Win 2K.
> So basically what I want is to create the database from scratch on the
> new server using that back up file from the other database. Can somebody
> tell me the procedure for this. I m a newbie at SQL Server.
> Thanks.
> Ricky
> --
> Posted via http://dbforums.com|||Hi Ricky,
You said :
>>'I have a database, which I recently backed up. Now I would like torestore
this back-up on another server. So basically I m trying to create a copy of
my database on the other server'. <<
It seems to me that you already have a backup available that you want to
restore...do the following :
1) Copy the backup file across to the new server.
2) On the new server, create a database device ( Expand: Management > Right
click on 'Backup' > New backup device, note the path and give it a
descrpiptive name (somedbname.bak)
3) Right click on the database you want to restore, All Tasks > Restore
Database
4) Click on 'From Device' and click 'Select Devices' on device name > click
Add.
5) click on filename and browse to the location where you copied the old
backup file and click ok.
6) Now, don't close all the windows, come back to the restore database
window and click on 'Options' tab and select the 'Force restore over
existing database'
7) in the Logical filename, check the old server for the logica filename
(somedbname) and enter it there, do the same for both log and mdf files.
8) In the 'Move to Physical filename' make sure it is pointing to the data
and log files of this new database.
Note that, the logical filename is not the path, but just a name, you can
get it by going to the old server, right click the database > properties --
the 'Filename' here is the logical filename -- do the same for the
Transaction log tab as well.
After you hit restore...it will complete the restore and give u a message
saying that restore is complete...
Hope this helps.
Regards,
Andy.
PS: This information is Ad-hoc and i cannot be held responsible for any
action you might take, I recommend that you consult a few people and then go
about it.
Thanks
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3082099.1057594899@.dbforums.com...
> Hi Guys,
> I have a database, which I recently backed up. Now I would like to
> restore this back-up on another server. So basically I m trying to
> create a copy of my database on the other server.
> How can I restore this back-up file on the other server. When I try
> doing that from the Enterprise Manager, it says that I have to choose
> from an existing backup file. I m using SQL Server 2000 on Win 2K.
> So basically what I want is to create the database from scratch on the
> new server using that back up file from the other database. Can somebody
> tell me the procedure for this. I m a newbie at SQL Server.
> Thanks.
> Ricky
> --
> Posted via http://dbforums.com

Backup And Recovery

Hi Guys,
Well. I'm new to SQL server and appreciate your help on a problem...
Is the database mirroring possible in SQL server 2000. as in Oracle.

ie. I've the data file and a old log file (dated 3-04-2004), and ofource the backup file of the same date.

Now if my hard disk is creashed yesterday, will I be able to restore the database with the help of the data file which is updated till yesterday.

I can fo this in oracle. I create another database and then swap it's control files and data file that of the old one( the one which I need to restore). then i change the password file and boom i get everything upto date.

Can i do some thing like this in SQL Server?

thanking in anticipation

santosh kambleIf you have both data and log files still available you can use the sp_attach_db stored procedure to recreate the database. If you have only a data file, you can use sp_attach_single_file_db. See in books online for more info on these procs.

Backing up trans log of system dbs

Guys,

Should the transaction log of the system databases be backed up and if so, how often should this be done, generally?

Currently, we are backing up the system databases daily. The transaction log of the system databases, however, is not backed up.

Recently, I was wondering what exactly could happen, which would imply that we need to back up the transaction log of the system databases.

As far as I understand, 'msdb' contains things like jobs, dts. As such, if no jobs/dts are added in the middle of the day, there is no need to back up the trans log of this db - there could even be no reason, in such case, to back this db up at all!

'master' db would change if we add/modify objects/properties and so on ... so unless many changes are made, no need for backing up its trans log.

Having said that, could anyone enlighten me as to whether it is necessary to back up the transaction log of the system dbs and/or what it depends on and/or when it is suggested to do so?

Thanks youBy default, master and msdb are set to simple recovery, so the transaction log backups would error out. I would strongly suggest not tampering with these databases. For myself, I back them up in full once a week to disk, and every night in full to tape. They are small by today's standards.|||Thanks Mcrowley