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:\DATA\sqlserverData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'G:\DATA\sqlserverLog\templog.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:\DataBase\SqlServer\SqlServerDataBase\'+@mName+'.mdf'
set @oldLogMovePath = 'D:\DataBase\SqlServer\SqlServerLog\'+@mlogName+'.ldf'
set @dataMovePath = 'F:\DataBase\SqlServer\SqlServerDataBase\'+@mName+'.mdf'
set @logMovePath = 'F:\DataBase\SqlServer\SqlServerLog\'+@mlogName+'.ldf'
set @str = 'alter database ['+@databaseName+'] set offline;
exec master.dbo.xp_cmdshell''move '+@oldDataMovePath+' F:\DataBase\SqlServer\SqlServerDataBase\'';
exec master.dbo.xp_cmdshell''move '+@oldLogMovePath+' F:\DataBase\SqlServer\SqlServerLog\'';
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:\test\'+@databaseName+'1.ndf'
set @logPath = 'F:\test\'+@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+''''',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 =''
) 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