工作日记:存储过程备份数据库中指定表数据、表结构、视图、函数、存储过程

时间:2021-03-19 14:00:56
USE [TestDataBase]
GO
/****** Object:  StoredProcedure [dbo].[PT_CreateMineDatabaseBak]    Script Date: 2018/7/27 16:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:
-- Create date: 2017-4-24
-- Edit time:2017-5-16
-- Edit time:2017-11-2 新增字段默认值、字段顺序复制
-- Description:	 实现集成平台数据库复制存储功能
-- 使用方法:在导入目标服务器任意数据库中执行
-- =============================================
ALTER PROCEDURE [dbo].[PT_CreateMineDatabaseBak]
    @mineName NVARCHAR(50)='煤矿',--源矿井名称
   @meg NVARCHAR(500) = '' OUTPUT 
AS
DECLARE     
   @mineDesc VARCHAR(50),--矿井描述
   @sql NVARCHAR(max),--sql语句
   @tbName varchar(50),--正在生成表的表名
   @allCount int,--所有(用户表/视图)个数
   @realNum int,--正在生成第*个(表/视图)
   @failNum int,--失败*个(表/视图)
   @keyName varchar(50),--正在生成表主键名称
   @blCon int = 1, --1远程连接服务器成功,2失败
   @path VARCHAR(8000), --得到当前数据库的数据文件路径
   @bsl varchar(100),--标识列
   @bszz varchar(50),--标识种子
   @bsdz varchar(50),--标识递增量
   
   @tbCol varchar(5000)
BEGIN
	set @realNum = 1
	set @failNum = 0
	set @keyName = ''

	--得到当前数据库的数据文件路径
	SELECT @path = RTRIM(REVERSE(FILENAME))	FROM   sysfiles
	SELECT @path = REVERSE(SUBSTRING(@path, CHARINDEX('\', @path), 8000))	
	
	if (@mineName is null or @mineName = '')
		begin
			set @meg = '数据库名称不能为空'
			print (@meg)
			return 0
		end
	else 
		begin
			if Exists(select 1 From master.dbo.sysdatabases where name='TestDataBase')--存在数据库'TestDataBase'
			begin
				if Exists(select 1 From [TestDataBase].dbo.sysobjects where name='Sys_Mine')--存在表'Sys_Mine'
				begin
					if Exists(select 1 from [TestDataBase].dbo.Sys_Mine where MineName = @mineName)--源数据库中是否存在该矿井
					begin
					update [TestDataBase].dbo.Sys_Mine set CurrentMine = 1 where MineName = @mineName--设置矿井为当前矿井
					--2018年6月23日14:25:43 Doyle 注释掉,采用统一数据库名称
					--select @mineDesc = MineDesc from [TestDataBase].dbo.Sys_Mine where MineName = @mineName--获取矿井描述
					set @mineDesc='M';
					if exists(select 1 from master.dbo.sysdatabases where name='Platform_'+@mineDesc+'_v3.0')--目标数据库已存在
					begin
						set @sql = 'drop database [Platform_'+@mineDesc+'_v3.0]'--删除目标数据库(先清空正在使用,再删除)
						BEGIN TRY
							DECLARE @ks NVARCHAR(1000)
							DECLARE tb CURSOR LOCAL  
							FOR  
								SELECT ks = 'kill   ' + CAST(spid AS VARCHAR)  
								FROM   MASTER..sysprocesses  
								WHERE  dbid = DB_ID('Platform_'+@mineDesc+'_v3.0')    
					     
							OPEN   tb      
							FETCH   NEXT   FROM   tb   INTO   @ks    
							WHILE @@fetch_status = 0  
							BEGIN  
								EXEC (@ks)   
								FETCH NEXT FROM tb INTO @ks  
							END    
							CLOSE   tb    
							DEALLOCATE   tb    
							exec (@sql)
						end try
						begin catch
							set @meg = '删除目标数据库失败:'+ERROR_MESSAGE()
							print (@meg)
							return 0
						end catch
					end

					set @sql = 'CREATE DATABASE [Platform_'+@mineDesc+'_v3.0] ON  PRIMARY 
					(NAME = ''Platform_'+@mineDesc+'_v3.0'', FILENAME = '''+@path+'Platform_'+ @mineDesc+'_v3.0.mdf'' , SIZE = 5000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
					 LOG ON 
					( NAME = ''Platform_'+@mineDesc+'_v3.0_Log'', FILENAME = '''+@path+'Platform_'+ @mineDesc+'_v3.0_log.LDF'' , SIZE = 1000KB , MAXSIZE = 1000000KB , FILEGROWTH = 5%)'
					exec  (@sql)--新建目标数据库

					print ('创建数据库成功:'+ @path+'Platform_'+ @mineDesc+'_v3.0.mdf')
			
					select @allCount = COUNT(1) from [TestDataBase].dbo.sysobjects where xtype='U'
					DECLARE cursor_fkeyCols CURSOR FOR select name from [TestDataBase].dbo.sysobjects where xtype='U'
					OPEN cursor_fkeyCols
					FETCH NEXT FROM cursor_fkeyCols INTO @tbName 
					WHILE @@FETCH_STATUS = 0     
					   BEGIN  
						   print( '复制进度:总数/当前:'+convert(varchar,@allCount)+','+convert(varchar,@realNum)) + ' 表名称:' + @tbName--复制进度
						   set @sql = 'select * into [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+' from [TestDataBase].dbo.'+@tbName + '  where 1<>1'
						   BEGIN TRY
								exec (@sql)--复制表结构
								select @keyName = stuff((SELECT ','+a.name
								  FROM   [TestDataBase].dbo.syscolumns a 
								  inner  join [TestDataBase].dbo.sysobjects d on a.id=d.id       
								  where  d.name=@tbName and exists(SELECT 1 FROM [TestDataBase].dbo.sysobjects where xtype='PK' and  parent_obj=a.id and name in (  
								  SELECT name  FROM [TestDataBase].dbo.sysindexes   WHERE indid in(  
								  SELECT indid FROM [TestDataBase].dbo.sysindexkeys WHERE id = a.id AND colid=a.colid  
								))) FOR XML PATH('')),1,1,'')
								begin
									--标识列
									set @bsl =null
									set @sql = 'use [TestDataBase] SELECT @a=COLUMN_NAME FROM [TestDataBase].INFORMATION_SCHEMA.columns WHERE TABLE_NAME='''+@tbName+''' AND COLUMNPROPERTY(      
									OBJECT_ID('''+@tbName+'''),COLUMN_NAME,''IsIdentity'')=1'
									exec sp_executesql @sql, N'@a varchar(100) OUTPUT', @bsl OUTPUT   
									
									--SELECT @bsl = COLUMN_NAME FROM [TestDataBase].INFORMATION_SCHEMA.columns as a,[TestDataBase].dbo.syscolumns as b
									-- WHERE TABLE_NAME=@tbName AND  b.name = a.COLUMN_NAME and b.id = (select id from [TestDataBase].dbo.sysobjects where name = @tbName)
									-- and b.[status] = 128
									if (@bsl is not null)--标识列不为空
									begin
										set @sql = 'alter table [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+' drop column ['+@bsl+']'
										exec (@sql)
										
										set @sql = 'use [TestDataBase] SELECT @a = IDENT_SEED ('''+@tbName+''')'
										exec sp_executesql @sql, N'@a int OUTPUT', @bszz OUTPUT 
										
										set @sql = 'use [TestDataBase] SELECT @a = IDENT_INCR ('''+@tbName+''')'
										exec sp_executesql @sql, N'@a int OUTPUT', @bsdz OUTPUT 
										
										set @sql = 'Alter table [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+' Add ['+@bsl+'] int identity('+@bszz+','+@bsdz+')'
										exec (@sql)
									end
									
									select @tbCol = stuff((select ','+name from [TestDataBase].dbo.syscolumns where  id = (select id from [TestDataBase].dbo.sysobjects where name = @tbName)
										 FOR XML PATH('')),1,1,'')
									if Exists(select * From [TestDataBase].dbo.SYSCOLUMNS as a,[TestDataBase].dbo.sysobjects as b where a.id=b.id and b.name=@tbName and LOWER(a.name)='minename')--表存在矿井名称字段
									   begin
											if (@bsl is not null)--带标识列
											begin
											set @sql = 'set IDENTITY_INSERT  [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+'  on;
											insert into [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+'('+@tbCol+') select '+@tbCol+' from [TestDataBase].dbo.'+@tbName + ' where minename = ''' + @mineName+''''--复制当前矿井数据
											+';set IDENTITY_INSERT  [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+'  off'
											end
											else--不带标识列
											begin
											set @sql = 'insert into [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+'('+@tbCol+') select '+@tbCol+' from [TestDataBase].dbo.'+@tbName + ' where minename = ''' + @mineName+''''--复制当前矿井数据
											end
									   end
									   else
									   begin
											if (@bsl is not null)--带标识列
											begin
											set @sql = 'set IDENTITY_INSERT  [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+'  on;
											insert into [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+'('+@tbCol+') select '+@tbCol+' from [TestDataBase].dbo.'+@tbName--复制所有数据
											+';set IDENTITY_INSERT  [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+'  off'
											end
											else--不带标识列
											begin
											set @sql = 'insert into [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+'('+@tbCol+') select '+@tbCol+' from [TestDataBase].dbo.'+@tbName--复制所有数据
											end
									   end
									   exec (@sql)
									
									--主键
									set @sql = 'alter table [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+' add constraint PK_'+@tbName+' primary key('+@keyName+')'
									exec (@sql)
								end
								set @keyName = ''
						   end try
						   begin catch
							   set @keyName = ''
							   print (@tbName+'表同步失败:'+ERROR_MESSAGE())
							   set @realNum = @realNum+1
							   set @failNum = @failNum+1
							   FETCH NEXT FROM cursor_fkeyCols INTO @tbName
							   CONTINUE
						   end catch
						   set @realNum = @realNum+1
						   FETCH NEXT FROM cursor_fkeyCols INTO @tbName
					   END    
				  CLOSE cursor_fkeyCols     
				  DEALLOCATE cursor_fkeyCols 
				  
				  DECLARE @colName varchar(100)  
				  DECLARE @defVal varchar(300)
				  DECLARE @conName varchar(100)
				  --获取数据库中带默认值的字段信息(表名、字段名、默认值、约束名)
				  DECLARE cursor_fkeyCols CURSOR FOR SELECT ST.[name] AS tbName, SC.[name] AS colName, SD.definition AS defVal, SD.[name] AS conName
FROM [TestDataBase].sys.tables ST INNER JOIN [TestDataBase].sys.syscolumns SC ON ST.[object_id] = SC.[id]
INNER JOIN [TestDataBase].sys.default_constraints SD ON ST.[object_id] = SD.[parent_object_id] AND SC.colid = SD.parent_column_id
ORDER BY ST.[name], SC.colid
				  OPEN cursor_fkeyCols
				  FETCH NEXT FROM cursor_fkeyCols INTO @tbName,@colName,@defVal,@conName
				  WHILE @@FETCH_STATUS = 0     
					   BEGIN  
						   BEGIN TRY
								set @sql = 'alter table [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+' add constraint '+@conName+' default '+@defVal+' for '+@colName
								exec (@sql)
						   end try
						   begin catch
							   FETCH NEXT FROM cursor_fkeyCols INTO @tbName,@colName,@defVal,@conName
							   CONTINUE
						   end catch
						   FETCH NEXT FROM cursor_fkeyCols INTO @tbName,@colName,@defVal,@conName
					   END    
				  CLOSE cursor_fkeyCols     
				  DEALLOCATE cursor_fkeyCols 

				  print ('表同步完成!成功'+convert(varchar,@allCount-@failNum)+',失败'+convert(varchar,@failNum))
				  print ('-------------------------------------------------------------------')
				  update [TestDataBase].dbo.Sys_Mine set CurrentMine = 0 where MineName = @mineName--设置矿井为当前矿井
		  
				  /*同步视图*/
				  --set @realNum = 1--当前正在复制第1个视图
				  --set @failNum = 0--复制失败视图个数
				  --select @allCount = COUNT(1) FROM [TestDataBase].sys.sql_modules WHERE object_id IN (SELECT object_id FROM [TestDataBase].sys.views )
				  --DECLARE cursor_fkeyCols CURSOR FOR SELECT definition FROM [TestDataBase].sys.sql_modules WHERE object_id IN (SELECT object_id FROM [TestDataBase].sys.views )
				  --OPEN cursor_fkeyCols
				  --FETCH NEXT FROM cursor_fkeyCols INTO @sql 
				  --WHILE @@FETCH_STATUS = 0     
					 --  BEGIN
						--BEGIN TRY
						--    DECLARE @sql1 NVARCHAR(4000)
						--	print('视图 总/当前:'+convert(varchar,@allCount)+','+convert(varchar,@realNum))--复制进度
						--	set @sql1 = 'use [Platform_'+ @mineDesc+'_v3.0]'
						--	set @sql = replace(@sql,'VIEW dbo.','VIEW ')
						--	set @sql = replace(@sql,'dbo.','[Platform_'+ @mineDesc+'_v3.0].dbo.')
						--	exec(@sql1 + 'exec (''' + @sql + ''')')
					 --   end try
					 --   begin catch
						--   print ('同步失败:'+ERROR_MESSAGE())
						--   set @realNum = @realNum+1
						--   set @failNum = @failNum+1
						--   FETCH NEXT FROM cursor_fkeyCols INTO @sql
						--   CONTINUE
					 --   end catch
					 --   set @realNum = @realNum+1
						--FETCH NEXT FROM cursor_fkeyCols INTO @sql
					 --  END    
				  --CLOSE cursor_fkeyCols     
				  --DEALLOCATE cursor_fkeyCols 
				  --print ('视图同步完成!成功'+convert(varchar,@allCount-@failNum)+',失败'+convert(varchar,@failNum))
				  print ('-------------------------------------------------------------------')
		  
			
					/*同步存储过程*/
					DECLARE @objName varchar(80)
					DECLARE @objType varchar(80)
					set @realNum = 1--当前正在复制第1个视图
					set @failNum = 0--复制失败视图个数
					select @allCount = COUNT(1) 
						FROM [TestDataBase].sys.sql_modules AS sm  
						JOIN [TestDataBase].sys.objects AS o ON sm.object_id = o.object_id  
						where definition is not null  and is_ms_shipped = 0 
					DECLARE cursor_fkeyCols CURSOR FOR 
					SELECT sm.definition,o.name as objName,o.type as objType
						FROM [TestDataBase].sys.sql_modules AS sm  
						JOIN [TestDataBase].sys.objects AS o ON sm.object_id = o.object_id  
						where definition is not null  and is_ms_shipped = 0  order by sm.uses_database_collation desc,o.type,O.create_date
			
					OPEN cursor_fkeyCols
					FETCH NEXT FROM cursor_fkeyCols INTO @sql,@objName,@objType
					WHILE @@FETCH_STATUS = 0  
					begin
						BEGIN TRY
							print('函数、视图和存储过程 总/当前:'+convert(varchar,@allCount)+','+convert(varchar,@realNum))--复制进度
							set @sql = replace(@sql,'''','''''')
							exec('use [Platform_'+ @mineDesc+'_v3.0] exec (''' + @sql + ''')')
						end try
						begin catch
							if(@objType = 'TF')
							begin
								print ('表函数 '+@objName+'同步失败:'+ERROR_MESSAGE())
							end
							else if(@objType = 'FN')
							begin
								print ('标量函数 '+@objName+'同步失败:'+ERROR_MESSAGE())
							end
							else if(@objType = 'P')
							begin
								print ('存储过程'+@objName+'同步失败:'+ERROR_MESSAGE())
							end
							else if(@objType = 'V')
							begin
								print ('视图'+@objName+'同步失败:'+ERROR_MESSAGE())
							end
							else
							begin
								print (@objName+'同步失败:'+ERROR_MESSAGE())
							end
						   set @realNum = @realNum+1
						   set @failNum = @failNum+1
						   FETCH NEXT FROM cursor_fkeyCols INTO @sql,@objName,@objType
						   CONTINUE
						end catch
						set @realNum = @realNum+1
						FETCH NEXT FROM cursor_fkeyCols INTO @sql,@objName,@objType
					end    
					CLOSE cursor_fkeyCols     
					DEALLOCATE cursor_fkeyCols 
					print ('函数、视图和存储过程同步完成!成功'+convert(varchar,@allCount-@failNum)+',失败'+convert(varchar,@failNum))
					--备份目标数据库到master所在文件下
					set @sql = 'BACKUP DATABASE [Platform_'+ @mineDesc+'_v3.0] TO DISK='''+@path+'Platform_'+ @mineDesc+'_v3.0.bak'' With INIT'
					PRINT @sql
					EXEC(@sql)
					PRINT ('备份数据库成功:'+@path+'Platform_'+ @mineDesc+'_v3.0.bak')
					
					set @sql = 'drop database [Platform_'+@mineDesc+'_v3.0]'--删除目标数据库(先清空正在使用,再删除)
					BEGIN TRY
						DECLARE sptb CURSOR LOCAL  
						FOR  
							SELECT ks = 'kill   ' + CAST(spid AS VARCHAR)  
							FROM   MASTER..sysprocesses  
							WHERE  dbid = DB_ID('Platform_'+@mineDesc+'_v3.0')    
				     
						OPEN   sptb      
						FETCH   NEXT   FROM   sptb   INTO   @ks    
						WHILE @@fetch_status = 0  
						BEGIN  
							EXEC (@ks)   
							FETCH NEXT FROM sptb INTO @ks  
						END    
						CLOSE   sptb    
						DEALLOCATE   sptb    
						exec (@sql)
						print ('删除目标数据库成功')
					end try
					begin catch
						print ('删除目标数据库失败:'+ERROR_MESSAGE())
						return
					end catch
					set @meg = @path+'Platform_'+ @mineDesc+'_v3.0.bak'
					return 1
					end 
					else 
					begin
						set @meg = '源数据库中不存在矿井'''+@mineName+''''
						print (@meg)
					end
				end
				else 
				begin
					set @meg = '源数据库中不存在表''Sys_Mine'''
					print (@meg)
				end
			end
			else 
			begin
				set @meg = '源服务器中不存在数据库''TestDataBase'''
				print (@meg)
			end
			return 0
	end
END