LiCongMingDeShujuku

use master;
set nocount on
 
---------------------------
--BACKUP ALL DATABASES LOCOALLY.  SINGLE FULL BACKUP AND SINGLE TRANSACTION LOG BACKUP PER EACH DATABASE
--备份所有本地数据库。备份每个数据库的单个全备份和单个事务日志备份
 
declare
        @sao    int = (select cast([value] as int) from master.sys.configurations where [name] = 'show advanced options')
,       @bcd    int = (select cast([value] as int) from master.sys.configurations where [name] = 'backup compression default')

LiCongMingDeShujuku

use master;
set nocount on
 
declare @last_boot table ([os_boot] datetime, [first_session] datetime, [default_trace_start] datetime, [tempdb_created] datetime)
insert into @last_boot
select
    (select sqlserver_start_time from sys.dm_os_sys_info)
,   (select login_time from sys.dm_exec_sessions where session_id = 1)
,   (select start_time from sys.traces where is_default = 1)
,   (select create_date from sys.databases where name = 'tempdb')

LiCongMingDeShujuku

use master;
set nocount on
declare @backup_all_databases varchar(max)
set @backup_all_databases = ''
select @backup_all_databases = @backup_all_databases +
'backup database [' + name + '] to disk = ''\\MyShareName\' + replace(name, '', '''') + '.bak'' with format;' + char(10)
from sys.databases where name not in ('tempdb') 
order by name asc
exec (@backup_all_databases)

LiCongMingDeShujuku

use [compliance];
set nocount on
 
create table [STANDARD_RULES_00]
(
    [lineid]    	int identity(1,1)
,   [createdate]    datetime null
,   [statements]    varchar(max)
,   [modifieddate]  datetime null
,   [description]   varchar(max)

LiCongMingDeShujuku

with last_restored as
(
select
databasename = sd.[name]
,   sd.[create_date]
,   sd.[compatibility_level]
,   sd.[collation_name]
,   rh.*
,   rownum = row_number() over (partition by sd.name order by rh.[restore_date] desc) from
master.sys.databases sd left outer join msdb.dbo.[restorehistory] rh on rh.[destination_database_name] = sd.name join master.sys.database_mirroring sdm on sd.database_id = sdm.database_id --where

LiCongMingDeShujuku

use master;
set nocount on
 
-- Create ADSI Linked Server for Active Directory queries
exec master.dbo.sp_addlinkedserver 
    @server     = N'ADSI_LINK'
,   @srvproduct = N'Active Directory Services Interfaces'
,   @provider   = N'ADSDSOObject'
,   @datasrc    = N'MyDomainController.domain.com'
go

LiCongMingDeShujuku

use master;
set nocount on
select
     'physical_cpu' = cpu_count / hyperthread_ratio
,    'cores' =
         case
            when hyperthread_ratio = cpu_count then cpu_count
            else (cpu_count / hyperthread_ratio) * ((cpu_count - hyperthread_ratio) / (cpu_count / hyperthread_ratio))
         end
,     'logical_cpu' =

LiCongMingDeShujuku

use [WSS_Content];
set nocount on
go
-- 	look for specific document files in sharepoint
-- 	查找SharePoint里的具体文件夹
select
    'database'  = db_name()
,   'time_created'  = left(alldocs.timecreated, 19)
,   'kb'        = (convert(bigint,alldocstreams.size))/1024
,   'mb'        = (convert(bigint,alldocstreams.size))/1024/1024

LiCongMingDeShujuku

use master;
set nocount on
 
declare     @fqdn_server_name   varchar(255)
declare     @tomorrow       varchar(255)
declare     @30_days        varchar(255)
declare     @file_removal       table ([del_statement] varchar(555))
set     @fqdn_server_name   = (select cast(serverproperty('machinename') as varchar) + '.' + lower(default_domain()) + '.com')
set     @tomorrow       = (select convert(varchar, getdate() + 1, 101)  + ' 00:00:00')
set     @30_days        = (select convert(varchar, getdate() - 30, 101) + ' 00:00:00')