[讨论]大家讨论一下在使用sybase过程中，数据库备份策略

jackhoo72

syslog错我还不知道如何处理，我dbcc checkalloc(xxx,fix)也不行。不过好像还能用。

jazy

不过这还得sybase方面确认没有什么隐患才能放心阿！

另外，想了解一些，你在实际整库备份的时候是否每次都耐心的取做dbcc呢？

你们的系统是24小时运行，还是说可以停部分时间啊？

chuxu

to jackhoo72   　  

你在做dbcc时数据库有访问吗？看你的情况是通过crontab处理的，如果不能保证没有用户使用，那么报syslog错应该没什么问题。

jackhoo72

由于我的数据库晚上都不对外用了，所以我dbcc的时候已经置为单用户状态了，用sp_dboption db,"single user",true。但仍然报syslog错，不过我这个库不太重要，当然，如果那位大侠能指点一下非常感谢。

xwing

可否用“dbcc checkcatalog()”试一下？

jazy

用“dbcc checkcatalog()” 的确很快！
但只是对系统表的检验，还是不能发现别的库的隐患阿！


几个dbcc 操作的具体意义列举如下：

（1）dbcc checkdb：检验当前数据库中所有表的一致性；
（2）dbcc checktable(table_name)：检验当前数据库中指定表的一致性；
（3）dbcc checkcatalog：检验当前数据库中所有系统表的一致性；
（4）dbcc cgeckalloc：检验当前数据库中所有页空间分配的一致性；
（5）dbcc tablealloc(table_name)：检验当前数据库中指定表的页空间分配的一致性；
（6）dbcc indexaloc(index_name)：检验当前数据库中指定索引的页空间分配的一致性；

molin

Error 2540
Severity Level
16
Error Message Text
Table Corrupt: Page is allocated but not linked&#59; check the following pages and ids: allocation pg#=%ld extent id=%ld logical pg#=%ld object id on extent=%ld (object name = %S_OBJID) indid on extent=%ld
Explanation
This error occurs when dbcc checkalloc determines that a page is marked as allocated to an object but that page is not being used. There is no corruption or data loss associated with this error.
Each 2540 error means the loss of one blank data page. A few 2540 errors are no cause for concern. However, if many of these errors occur, the amount of "lost" disk space could be significant.
Note:The instructions below are for fixing 2540 errors once they have occurred. Two easy-to-use strategies exist for detecting this error sooner in the future. Refer to "Detecting Allocation Errors as Early as Possible" for information about these strategies.
Action
Occasionally dbcc checkalloc reports this error when no real error condition exists. You can either check to determine whether the error is real, or continue with this section and take action to correct it, whether or not it reflects a real allocation error.
Because the process used to discover whether or not the error is real can be time-consuming, you may want to go directly to "Error Resolution" now.
Verifying That the Error Is Real
Run dbcc checkalloc in single-user mode if you suspect the 2540 error messages are incorrect. If the error is in master, use the section "How to Start SQL Server in Single-User Mode" for instructions about how to invoke SQL Server in single-user mode. Refer to "dbcc" in the SQL Server Reference Manual for information about dbcc checkalloc.
Error Resolution
If many of these errors are occurring, it is possible to clear them all at once by using the dbcc checkalloc and dbcc checkalloc with fix option commands. Refer to "How to Fix and Prevent Allocation Errors" for information about using dbcc checkalloc.
If the text of the error message includes a real object name, not a number, then the error is on an existing object which the system catalog has correct references to, and you should continue now to "Identify Table: User or System Table".
If a number appears instead of the object name, then that object only partially exists and the error must be corrected using the procedure described in "How to Fix and Prevent Allocation Errors".
Identify Table: User or System Table
Look at the value for "object id on extent" in the error message. If it is 100 or greater, continue with "Action for User Tables". If the "object id on extent" is below 100, it is a system table and requires a different procedure as described in the section "Action for System Tables".
Action for User Tables
If the "object id on extent" in the error message is 100 or greater, follow these steps to correct the error:
1.Check the value of the "indid on extent" in the error message to determine whether it is a table (value = 0) or an index (value >; 0).
2.Run dbcc tablealloc or dbcc indexalloc, depending on whether the object named in the 2540 error message is a table or an index. Before you run the appropriate command, keep the following in mind:
- dbcc tablealloc corrects this problem on a table or an index, but if the problem is on an index, you can avoid affecting the entire table by using dbcc indexalloc. If the table is large or heavily used, it may be more practical to use dbcc indexalloc.
- These commands can correct the error only when run in the full or optimized mode, and with the nofix option not specified, the default for user tables.
- You can use the object name or object ID in the following commands where the argument "object_name" appears.
Use the command appropriate for your situation:
For Tables (index id in extent = 0)
For Indexes (0 < index id in extent < 255)
1>; dbcc tablealloc (object_name)
2>; go
1>; dbcc indexalloc (object_name,
2>; indid_on_extent)
3>; go
Refer to &quot;dbcc&quot; in the SQL Server Reference Manual and &quot;Checking Database Consistency&quot; in the System Administration Guide for information about dbcc tablealloc and dbcc indexalloc.
Action for System Tables
If the &quot;object id on extent&quot; in the error message is less than 100, follow these steps to correct the error:
1.Put the affected database in single-user mode:
- If the database is master, use the procedure in &quot;How to Start SQL Server in Single-User Mode&quot;, and then go to step 2.
- If the database is not master, use the sp_dboption stored procedure to put the affected database in single-user mode:
  1>; use master
  2>; go
  1>; sp_dboption database_name, single, true
  2>; go
  1>; use database_name
  2>; go
  1>; checkpoint
  2>; go
2.Check the value of the &quot;indid on extent&quot; in the error message to determine whether it is a table (value is 0) or an index (value is greater than 0).
3.Run dbcc tablealloc or dbcc indexalloc, depending on whether the object named in the 2540 error message is a table or an index. Then execute the appropriate command. Before you run the appropriate command, keep the following in mind:
- dbcc tablealloc corrects either a table or an index, but if the problem is on an index, you can avoid affecting the entire table by using dbcc indexalloc. If you need to minimize the amount of time the table is unavailable, it may be most practical to use dbcc indexalloc.
- These commands correct the error only when run in the full or optimized mode, with the fix option specified, because the default value is nofix on system tables.
- You can use the &quot;object name&quot; or &quot;object id on extent&quot; in the commands above where the argument object_name appears.Use the command appropriate for your situation:
For Tables (index id in extent = 0)
For Indexes (0 < index id in extent < 255)
1>; dbcc tablealloc (object_name,
2>; full, fix)
3>; go
1>; dbcc indexalloc (object_name,
2>; indid_on_extent, full, fix)
3>; go
4.Turn off single-user mode in the database:
- If the database is master, refer to &quot;Returning SQL Server to Multiuser Mode&quot;.
- If the database is not master, use the following procedure:
  1>; use master
  2>; go
  1>; sp_dboption database_name, single, false
  2>; go
  1>; use database_name
  2>; go
  1>; checkpoint
  2>; go
Refer to &quot;dbcc&quot; in the SQL Server Reference Manual and &quot;Checking Database Consistency&quot; in the System Administration Guide for information about dbcc tablealloc and dbcc indexalloc.
Releases in Which This Error Is Raised
11.0 and later

jazy

谢谢molin提供的资料!

aladdin

小小感受:
增量备份是个理论概念
全备的同时最好也来个bcp数据备份,这在生产环境中是最实用的. bcp可做在crontab中.

jazy

同意，这样当然是最保险的！但是做bcp对于大量数据也是需要很长时间的，而且这些数据的存储也是个问题！