1.数据备份
DECLARE @databaseName varchar(600) DECLARE @str varchar(600) DECLARE @savePath VARCHAR(600) DECLARE @date VARCHAR(60)=REPLACE(CONVERT(VARCHAR,GETDATE(),23),'-','') DECLARE @savename VARCHAR(600) SET @savePath = 'f:/DatabaseBackup/' DECLARE My_Cursor CURSOR FOR ( select name from sys.databases where name not like '%tmp%' and name not like '%temp%' and name not like '%master%' and name not like '%model%' and name not like '%msdb%' and name not like '%wqb_upgrade%' and name not like '%ReportServer%' and name not like '%ReportServerTempDB%' and name not like '%spagobietllog%' and name not like '%spagobietl%' ) OPEN My_Cursor; FETCH NEXT FROM My_Cursor INTO @databaseName WHILE @@FETCH_STATUS = 0 BEGIN --restore headeronly from disk = 'f:/doc/document_20180702.bak' 查看备份位置 set @savename = @savePath+@databaseName+'_'+@date+'.bak'; declare @weekday varchar(50) declare @date2 varchar(100) declare @temppath varchar(100) select @temppath= @savePath+@databaseName+'_'+REPLACE(CONVERT(VARCHAR, DATEADD(WK,DATEDIFF(wk,0,getdate()),0),23),'-','')+'.bak' select @weekday = DATEname(weekday, getdate()) if @weekday='星期一' begin set @temppath = @savename INSERT INTO T_BACKUP_DATABASE_TIME(completeTime) values (getdate()) backup database @databaseName to disk = @savename with FORMAT; end else begin INSERT INTO T_BACKUP_DATABASE_TIME(diffTime) values (getdate()) backup database @databaseName to disk = @temppath with differential; end FETCH NEXT FROM My_Cursor INTO @databaseName END CLOSE My_Cursor; DEALLOCATE My_Cursor;
2.数据文件迁移
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb'); USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'G:DATAsqlserverData empdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'G:DATAsqlserverLog emplog.ldf'); GO
DECLARE @databaseName varchar(600) DECLARE @str varchar(6000) DECLARE @tempstr varchar(6000) DECLARE @mName varchar(600) DECLARE @mlogName varchar(600) DECLARE @oldDataMovePath VARCHAR(600) DECLARE @oldLogMovePath VARCHAR(600) DECLARE @logMovePath VARCHAR(600) DECLARE @dataMovePath VARCHAR(600) DECLARE My_Cursor CURSOR FOR ( select distinct name from sys.databases where name in ( select '410000_yw_'+UNIT_CODE from dbo.T_UNIT_AUDIT where INDUSTRY_NAME ! = '重点国企' ) and name like '%410000_yw_115211%' and name not like '%tmp%' and name not like '%410000_yw_204101%' and name not like '%master%' and name not like '%model%' and name not like '%msdb%' and name not like '%NETWORKING_AUDIT%' and name not like '%ReportServer%' and name not like '%ReportServerTempDB%' and name not like '%wqb_upgrade%' and name not like '%temp%' and name not like '%spagobietllog%' and name not like '%Spagobietl%' and name not like '%李亚坤测试库%' ) OPEN My_Cursor; FETCH NEXT FROM My_Cursor INTO @databaseName WHILE @@FETCH_STATUS = 0 BEGIN create table #tempt(name varchar(60)); set @tempstr = ' DECLARE @mName varchar(600) DECLARE @mlogName varchar(600) select @mName= name from ['+@databaseName+']..sysfiles where name not like ''%log%''; select @mlogName = name from ['+@databaseName+']..sysfiles where name like ''%log%''; insert into #tempt(name)values(''''+@mName+''''); insert into #tempt(name)values(''''+@mlogName+''''); ' exec (@tempstr); select @mName= name from #tempt where name not like '%log%'; select @mlogName= name from #tempt where name like '%log%'; set @oldDataMovePath = 'D:DataBaseSqlServerSqlServerDataBase'+@mName+'.mdf' set @oldLogMovePath = 'D:DataBaseSqlServerSqlServerLog'+@mlogName+'.ldf' set @dataMovePath = 'F:DataBaseSqlServerSqlServerDataBase'+@mName+'.mdf' set @logMovePath = 'F:DataBaseSqlServerSqlServerLog'+@mlogName+'.ldf' set @str = 'alter database ['+@databaseName+'] set offline; exec master.dbo.xp_cmdshell''move '+@oldDataMovePath+' F:DataBaseSqlServerSqlServerDataBase''; exec master.dbo.xp_cmdshell''move '+@oldLogMovePath+' F:DataBaseSqlServerSqlServerLog''; alter database ['+@databaseName+'] modify file (name='''+@mName+''',filename='''+@dataMovePath+'''); alter database ['+@databaseName+'] modify file (name='''+@mlogName+'_1og'',filename='''+@logMovePath+'''); alter database ['+@databaseName+'] set online; ' select (@str); FETCH NEXT FROM My_Cursor INTO @databaseName drop table #tempt; END CLOSE My_Cursor; DEALLOCATE My_Cursor;
3.增加数据库文件组
DECLARE @databaseName varchar(600) DECLARE @str varchar(6000) DECLARE @dataPath VARCHAR(600) DECLARE @logPath VARCHAR(600) DECLARE My_Cursor CURSOR FOR ( select name from sys.databases where name like '%tco%' ) OPEN My_Cursor; FETCH NEXT FROM My_Cursor INTO @databaseName WHILE @@FETCH_STATUS = 0 BEGIN set @dataPath = 'F: est'+@databaseName+'1.ndf' set @logPath = 'F: est'+@databaseName+'_log1.ldf' set @str = ' DECLARE @dataSize varchar(50) DECLARE @logSize varchar(50) DECLARE @innerstr varchar(6000) select @dataSize = size*10 from ['+@databaseName+'].[dbo].[sysfiles] where name not like ''%log%''; select @logSize = size*10 from ['+@databaseName+'].[dbo].[sysfiles] where name like ''%log%''; set @innerstr = '' USE [master] ALTER DATABASE ['+@databaseName+'] MODIFY FILE ( NAME =N'''''+@databaseName+''''',MAXSIZE = ''+@dataSize+''KB , FILEGROWTH = 1KB ); ALTER DATABASE ['+@databaseName+'] MODIFY FILE ( NAME = N'''''+@databaseName+'_log'''',MAXSIZE = ''+@logSize+''KB , FILEGROWTH = 1KB ); Alter database '+@databaseName+' add file(NAME = '''''+@databaseName+'1'''',FILENAME ='''''+@dataPath+'''''); Alter database '+@databaseName+' add log file ( name='''''+@databaseName+'_log1'''', filename='''''+@logPath+''''', size=2MB, maxsize=UNLIMITED, filegrowth=10% ); '' exec(@innerstr) ' exec (@str) FETCH NEXT FROM My_Cursor INTO @databaseName END CLOSE My_Cursor; DEALLOCATE My_Cursor;
4.查询一周报送情况
WITH cte(t) AS ( select DATEADD(DAY,-6,CAST(@date as date))AS t UNION ALL SELECT DATEADD(DAY,1,t) FROM cte WHERE t<CAST(@date as date) ) SELECT case when t.RECEIVE_TIME IS null then 0 else 1 end as isReport, t1.RECEIVE_TIME, t1.UNIT_NAME,t1.UNIT_CODE FROM ( select b.UNIT_CODE,b.UNIT_NAME,convert(varchar,a.t,23) RECEIVE_TIME from cte a cross join [NETWORKING_AUDIT].dbo.T_UNIT_AUDIT b ) as t1 LEFT JOIN ( select distinct a.UNIT_CODE,CONVERT(varchar,RECEIVE_TIME,23) RECEIVE_TIME from [NETWORKING_AUDIT].dbo.T_UNIT_AUDIT a join [NETWORKING_AUDIT].dbo.T_FILE_RECEIVE_RECORD b on a.UNIT_CODE = b.UNIT_CODE and convert(varchar,RECEIVE_TIME,23) > DATEADD(DAY,-7,CAST(@date as date)) and convert(varchar,RECEIVE_TIME,23) <= CAST(@date as date) ) t ON t1.RECEIVE_TIME=t.RECEIVE_TIME and t1.UNIT_CODE = t.UNIT_CODE order by t1.UNIT_CODE,t1.RECEIVE_TIME
5.查询近X天未报送单位
declare @date int; set @date = 3; WITH cte(t) AS ( SELECT CAST(GETDATE()-@date AS DATE) AS t UNION ALL SELECT DATEADD(DAY,1,t) FROM cte WHERE t<GETDATE()-2 ) select * into T_TEMPTABLE from ( SELECT cte.t DATES,aa.* from cte cross join ( select UNIT_CODE,UNIT_NAME from T_UNIT_AUDIT where LEN(UNIT_CODE)=6 or UNIT_CODE ='800002014' ) aa )zz ; WITH cte2(t) AS ( SELECT CAST(GETDATE()-2 AS DATE) AS t UNION ALL SELECT DATEADD(DAY,1,t) FROM cte2 WHERE t<GETDATE()-2 ) select * into T_TEMPTABLE2 from ( SELECT distinct cte2.t,t.UNIT_CODE,t.UNIT_NAME FROM cte2 LEFT JOIN ( SELECT * FROM dbo.T_FILE_UNZIP_RECORD ) t ON cte2.t=BACKUP_TIME ) cc; ---查询展示语句 select distinct a.DATES,a.UNIT_CODE,a.UNIT_NAME UnReportUnitName from T_TEMPTABLE a left join T_TEMPTABLE2 b on a.DATES =b.t and a.UNIT_CODE = b.UNIT_CODE where b.UNIT_NAME is null order by a.UNIT_CODE,a.DATES desc; -------------删除临时表 drop table T_TEMPTABLE; drop table T_TEMPTABLE2;
6.截断数据库日志
DECLARE @databaseName varchar(600) DECLARE @str varchar(6000) DECLARE My_Cursor CURSOR FOR ( select distinct name from sys.databases where name not like '%tmp%' and name not like '%model%' and name not like '%msdb%' and name not like '%master%' and name not like '%tempdb%' ) OPEN My_Cursor; FETCH NEXT FROM My_Cursor INTO @databaseName WHILE @@FETCH_STATUS = 0 BEGIN set @str = ' use ['+@databaseName+']; DECLARE @sysfileLogName varchar(6000); select @sysfileLogName = name from ['+@databaseName+']..sysfiles where name like ''%log%''; ALTER DATABASE ['+@databaseName+'] SET RECOVERY SIMPLE; DBCC SHRINKFILE (@sysfileLogName, 1); ' select (@str); FETCH NEXT FROM My_Cursor INTO @databaseName END CLOSE My_Cursor; DEALLOCATE My_Cursor;
7.复制单个或多个数据库表到另一个数据库
ALTER PROCEDURE [dbo].[DataBaseToDataBase] @sourceDatabaseName VARCHAR(100), @targetDatabaseName VARCHAR(100) AS BEGIN DECLARE @DatabaseName VARCHAR(255) DECLARE @sql VARCHAR(8000) DECLARE cur CURSOR FOR SELECT name FROM sys.databases WHERE name = ''+@sourceDatabaseName+'' --SELECT name FROM sys.databases --WHERE name like '%test%' OPEN cur; FETCH NEXT FROM cur INTO @DatabaseName; WHILE @@FETCH_STATUS =0 BEGIN SET @sql = 'USE ['+@DatabaseName+']; DECLARE @TableName VARCHAR(255) DECLARE @sql2 VARCHAR(8000); DECLARE cur2 CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES OPEN cur2 FETCH NEXT FROM cur2 INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql2 = '' USE ['+@targetDatabaseName+']; DECLARE @inssql1 VARCHAR(8000); DECLARE @inssql2 VARCHAR(8000); set @inssql1 = ''''insert into ['+@targetDatabaseName+'].dbo.''+@TableName+'' select * from ['+@DatabaseName+'].dbo.''+@TableName+'' '''' set @inssql2 = ''''select * into ['+@targetDatabaseName+'].dbo.''+@TableName+'' from ['+@DatabaseName+'].dbo.''+@TableName+'' '''' IF EXISTS( select * from sysobjects where name =''''''+@TableName+'''''' and type = ''''U'''' ) begin exec (@inssql1); end; else begin exec (@inssql2); end; ''; EXEC (@sql2); FETCH NEXT FROM cur2 INTO @TableName END CLOSE cur2; DEALLOCATE cur2; '; EXEC (@sql) FETCH NEXT FROM cur INTO @DatabaseName; END; CLOSE cur; DEALLOCATE cur; END