分类目录归档：SQL Server

1、服务器ip地址和数据库版本
主机：192.168.1.1
备机：192.168.1.110
数据库：sql server 2005 sp4

2、主备实例互通
1）创建证书

--主机执行
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xifenfei';
GO
USE MASTER
GO
CREATE CERTIFICATE HOST_A_cert 
       WITH SUBJECT = 'A certificate for database mirroring',
       START_DATE = '11/25/2009',
       EXPIRY_DATE = '12/31/2199';
GO

--备机执行
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xifenfei';
GO
USE MASTER
GO
CREATE CERTIFICATE HOST_B_cert 
       WITH SUBJECT = 'A certificate for database mirroring',
       START_DATE = '11/25/2009',
       EXPIRY_DATE = '12/31/2199';
GO

2）创建连接的端点

--主机执行
create endpoint endpoint_mirroring
state=started
as
tcp (listener_port=5022,listener_ip=all)
for
database_mirroring
(authentication=certificate HOST_A_cert,encryption=supported,role=all);

--备机执行
create endpoint endpoint_mirroring
state=started
as
tcp (listener_port=5022,listener_ip=all)
for
database_mirroring
(authentication=certificate HOST_B_cert,encryption=supported,role=all);

3）备份证书以备建立互联

--主机执行
backup certificate HOST_A_cert to file='E:\database\key\HOST_A_cert.cer';
--备机执行
backup certificate HOST_B_cert to file='C:\database\mssql\key\HOST_B_cert.cer';

4）互换备份证书
拷贝证书文件到对象文件夹中

5）添加登录名、用户

--主机执行
create login HOST_B_login with password='20091125';
create user HOST_B_user for login HOST_B_login;
create certificate HOST_B_cert authorization HOST_B_user from file='C:\database\mssql\key\HOST_B_cert.cer';
grant connect on endpoint::endpoint_mirroring to [HOST_B_login];

--备机执行
create login HOST_A_login with password='20091125';
create user HOST_A_user for login HOST_A_login;
create certificate HOST_A_cert authorization HOST_A_user from file='E:\database\key\HOST_A_cert.cer';
grant connect on endpoint::endpoint_mirroring to [HOST_A_login];

3、建立镜像关系
1）备份还原数据库

/*尝试从刚刚使用的全备文件进行还原，在还原数据的时候需要使用”WITH NO RECOVERY“选项。*/
--主机执行
backup database test to disk='D:\sqlbackup\test091124full.bak'
backup log test to disk='D:\sqlbackup\test091124log.bak'

--备机执行
restore database acmr_10jqka from disk='D:\sqlbackup\test091124full.bak' with replace,norecovery
restore log acmr_10jqka from disk='D:\dbbackup\118_sql\acmr_10jqka091125log.bak' with norecovery

2）建立镜像

/*注意：先备机，后主机*/
--备机执行
alter database acmr_10jqka set partner='TCP://192.168.1.1:5022';
--主机执行
alter database test set partner='TCP://192.168.1.110:5022';

3）事务安全性设置

/*默认情况下，事务安全级别的设置为FULL，即“同步运行模式”，而且，SQL SERVER 2005标准版只支持同步模式。*/
/*如果关闭事务安全级别FULL模式，则可以切换到异步运行模式，该模式可使性能达到最佳。*/
--主机执行
use master
go
alter database test set partner safety full;   --事务安全，同步模式
alter database test set partner safety off;    --事务不安全，异步模式

4）切换

--主机执行下面命令后，即切换为备机角色
use master
go
alter database test set partner failover;
/*同理，也可以在备机（主体数据库）上执行上面命令，之后切换为备机（备数据库）*/

一、查询现有数据库恢复模式
1、直接查看
1).展开“数据库”，然后根据数据库的不同，选择用户数据库，或展开“系统数据库”，再选择系统数据库。
2).右键单击该数据库，再单击“属性”，这将打开“数据库属性”对话框。
3).在“选择页”窗格中，单击“选项”。
4).当前恢复模式显示在“恢复模式”列表框中。
5).也可以从列表中选择不同的模式来更改恢复模式。可以选择“完整”、“大容量日志”或“简单”。

2、通过sql语句查看

--sql server 2000及其以前版本
SELECT name, (SELECT DATABASEPROPERTYEX(name, 'RECOVERY')) RecoveryModel FROM master.sys.databases ORDER BY name

--sql server 2005及其以后版本
SELECT name, recovery_model_desc FROM master.sys.databases ORDER BY name

二、三种恢复模式比较
1、简单恢复模式
特点：无日志备份。自动回收日志空间以减少空间需求，实际上不再需要管理事务日志空间。
工作丢失的风险 ：最新备份之后的更改不受保护。在发生灾难时，这些更改必须重做。
能否恢复到时点：只能恢复到备份的结尾
降低工作丢失风险：不影响备份管理的前提下时常备份，以免丢失大量数据。
适用范围(符合下列所有要求)：
1.不需要故障点恢复。如果数据库丢失或损坏，则会丢失自上一次备份到故障发生之间的所有更新，但您愿意接受这个损失。
2.您愿意承担丢失日志中某些数据的风险。
3.您不希望备份和还原事务日志，希望只依靠完整备份和差异备份。

2、完整恢复模式
特点：需要日志备份。数据文件丢失或损坏不会导致丢失工作。可以恢复到任意时点（例如应用程序或用户错误之前）。
工作丢失的风险:正常情况下没有。如果日志尾部损坏，则必须重做自最新日志备份之后所做的更改。
能否恢复到时点：果备份在接近特定的时点完成，则可以恢复到该时点。
时点恢复：出现故障后，可以尝试备份“日志尾部”（尚未备份的日志）。如果结尾日志备份成功，则可以通过将数据库还原到故障点来避免任何工作丢失。
缺点：使用日志备份的缺点是它们需要使用存储空间并会增加还原时间和复杂性。
一般的备份策略：
1.首先完整备份数据库以及日志备份.
2.在日志备份后的某个时间,数据库发生错误.接下来 先备份活动日志
3.然后还原完整数据库备份和日志备份,但是不恢复数据库；
4.还原并恢复结尾日志备份。这样就完成了恢复待故障点,恢复了所有数据.
降低工作丢失风险:建议经常执行日志备份，以将工作丢失的风险限定在业务要求所允许的范围内。
适用范围(符合下列任一要求)：
1.您必须能够恢复所有数据
2.数据库包含多个文件组，并且您希望逐段还原读/写辅助文件组（以及可选地还原只读文件组）。
3.您必须能够恢复到故障点
4.您希望可以还原单个页
5.您愿意承担事务日志备份的管理开销。

3、大容量日志会恢复
特点：需要日志备份。是完整恢复模式的附加模式，允许执行高性能的大容量复制操作。通过使用最小方式记录大多数大容量操作，减少日志空间使用量。
工作丢失的风险:如果在最新日志备份后发生日志损坏或执行大容量日志记录操作，则必须重做自该上次备份之后所做的更改,否则不丢失任何工作。
能否恢复到时点：可以恢复到任何备份的结尾。不支持时点恢复。
切换到该模式的必要性：对于某些大规模大容量操作（如大容量导入或索引创建）,暂时切换到大容量日志恢复模式可提高性能并减少日志空间使用量。仍需要日志备份。
何时使用大容量日志恢复模式：仅在运行大规模大容量操作期间以及在不需要数据库的时点恢复时使用该模式。

三、修改当前数据库恢复模式

--简单恢复模型:
USE master;
ALTER DATABASE dbname SET RECOVERY SIMPLE

--完整恢复模型:
USE master;
ALTER DATABASE dbname  SET RECOVERY FULL

--批量日志恢复模型:
USE master;
ALTER DATABASE dbname  SET RECOVERY BULK_LOGGED

CONVERT
将某种数据类型的表达式显式转换为另一种数据类型。由于某些需求经常用到取日期格式的不同.现以下可在SQL Server中　将日期格式化.

SQL Server 支持使用科威特算法的阿拉伯样式中的数据格式。

在表中，左侧的两列表示将 datetime 或 smalldatetime 转换为字符数据的 style 值。
给 style 值加 100，可获得包括世纪数位的四位年份 (yyyy)。

*     默认值（style 0 或 100、9 或 109、13 或 113、20 或 120、21 或 121）始终返回世纪数位 (yyyy)。
** 当转换为 datetime 时输入；当转换为字符数据时输出。
*** 专门用于 XML。对于从 datetime 或 smalldatetime 到 character 数据的转换，输出格式如表中所示。对于从 float、money 或 smallmoney 到 character 数据的转换，输出等同于 style 2。对于从 real 到 character 数据的转换，输出等同于 style 1。

重要 默认情况下，SQL Server 根据截止年份 2049 解释两位数字的年份。即，两位数字的年份 49 被解释为 2049，而两位数字的年份 50 被解释为 1950。许多客户端应用程序（例如那些基于 OLE 自动化对象的客户端应用程序）都使用 2030 作为截止年份。SQL Server 提供一个配置选项（”两位数字的截止年份”），借以更改 SQL Server 所使用的截止年份并对日期进行一致性处理。然而最安全的办法是指定四位数字年份。

当从 smalldatetime 转换为字符数据时，包含秒或毫秒的样式将在这些位置上显示零。当从 datetime 或 smalldatetime 值进行转换时，可以通过使用适当的 char 或 varchar 数据类型长度来截断不需要的日期部分。

下表显示了从 float 或 real 转换为字符数据时的 style 值。

在下表中，左列表示从 money 或 smallmoney 转换为字符数据时的 style 值。

使用 CONVERT：

CONVERT (data_type[(length)], expression [, style])
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08

select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),\’-\’,\’\’),\’ \’,\’\’),\’:\’,\’\’)
20040912110608

select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12

select CONVERT(varchar(12) , getdate(), 112 )
20040912

select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12

select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004

select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004

select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004

select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004

select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004

select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004

select CONVERT(varchar(12) , getdate(), 108 )
11:06:08

select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1

select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004

select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1

select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177