SQL Server FAQs and Tips

1.Master數據庫只允許全備,不能備份事務日誌,否則會出現以下錯誤(採用進行maintenance plan備份):
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]

Backup can not be performed on database 'master'. This sub task is ignored.

2.在同一個maintenance plan中如果某個數據庫的recover模式為simple,會造成事務日誌備份job出現以下錯誤
The job failed. The Job was invoked by Schedule 3 (Schedule 1). The last step to run was step 1 (Step 1).
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
解決方法:將同一個maintenance plan的所有database的recover模式設為full或者bulk-logged
將不通recover模式的database分在不同的maintenance plan,以採取不同的策略.

將query的結果插入一個新表中
select * into db1..newtable from db2..oldtable

將query的結果插入一個已經存在的表中
insert into db1..table select * from db2..ohtertable

變更數據庫的dbo
user dbname
sp_changedbowner 'login'

變更object的owner
sp_changeobjectowner 'object','login'

 SqlServer中如何按姓氏笔画排序
Select * 
From TableName
Order By CustomerName
Collate Chinese_PRC_Stroke_ci_as