|
数据库备份策略在维护系统数据安全起着非同小可的作用，好的备份策略应该考虑保证数据的安全，并且操作较为方便。
基本过程很简单，如下：
1．备份到本地硬盘：
dump transaction with truncate_only
dump database … to …
dump transaction
。。。
2．当装载数据库和事务日志时，为防止其他用户对数据库的操作，须把数据库设置为 dbo use only。
进行装载时的顺序为：
dump transaction with no_truncate
load database database_name from ...
load transaction database_name from ...
。。。
online database
也可以用until指定恢复到某个时间
使用阈值管理
可以使用阈值管理，在阈值管理中安排当超过某个阈值时自动转储事务日志。当超过阀值以后，SQL Serve中断或挂起试图写这个日志的用户事务。对每一个挂起的事务 向errorlog 发一条消息；然后执行sp_thresholdaction
sp_thresholdaction用户自己编写
create procedure sp_thresholdaction
@dbname varchar（30）,
@segmentname varchar（30）,
as
dump transaction @dbname to DEVICE
print LOG DUMP %1! for %2! dumped, @segmentname, @dbname
其中参数 ：
@dbname 为达到阀值的数据库名；
@segmentname 为达到阀值的段名；
用户数据库损坏的处理
如果数据库处于suspect状态，无法用drop database 删除时：
dbcc dbrepair (db_name, dropdb)
create database db_name on dev_name for load
load database db_name from dump_device
master库损坏的处理
使用 buildmaster -m 重建一个新的 master 数据库；
buildmaster 建立 master 设备并在这个设备上建立 master， model， tempdb 库。
-m 选项只重新写 master 库， 而不修改配置块或初始化 master 设备。
以单用户方式重启动服务器， 如果需要的话， 则需增加转储设备；
从备份装载 master 数据库；
用 startserver 重启 SQL Server；
检查一致性： 对每一个数据库运行 dbcc checkalloc，并对重要的表进行检查；
但是，当我们问及sybase的技术支持是否建议使用threshold 时，他们并不积极建议这样做，理由是自动化操作往往会出现一些难于预料的结果。当然，要是有那么负责的dba，天天定时手工备份，当然是再好不过了。
基本的备份操作是简单，但是我们在实际实施备策略时，往往会考虑这样那样的问题，也会出现一些意想不到的问题，比如：
1、是整库备份还是增量备份
2、每天什么时候备份，备份时间怎么安排
3、万一需要恢复数据库，当前的备份能恢复到一个什么程度
4、数据库在恢复时可能出现哪些紧急情况
等等...
欢迎大家就这个主题进行一下讨论，以激发出一些好的想法和经验，以共同增强系统数据的安全性！
Error 2540
Severity Level
16
Error Message Text
Table Corrupt Page is allocated but not linked; 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.
NoteThe 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 dbcc in the SQL Server Reference Manual and Checking Database Consistency in the System Administration Guide for information about dbcc tablealloc and dbcc indexalloc.
Action for System Tables
If the object id on extent 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 How to Start SQL Server in Single-User Mode, 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 indid on extent 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 object name or object id on extent 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 Returning SQL Server to Multiuser Mode.
- 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 dbcc in the SQL Server Reference Manual and Checking Database Consistency in the System Administration Guide for information about dbcc tablealloc and dbcc indexalloc.
Releases in Which This Error Is Raised
11.0 and later
没有相关教程
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
