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

最新推荐文章于 2023-07-13 13:24:50 发布

成长长青

最新推荐文章于 2023-07-13 13:24:50 发布

阅读量156

点赞数

文章标签： mysql 备份表视图函数

本文链接：https://blog.csdn.net/weixin_35379520/article/details/113640191

版权

USE [TestDataBase]

/****** Object: StoredProcedure [dbo].[PT_CreateMineDatabaseBak] Script Date: 2018/7/27 16:49:27 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- 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

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

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

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

else

begin

set @meg = '源数据库中不存在表''Sys_Mine'''

print (@meg)

end

else

begin

set @meg = '源服务器中不存在数据库''TestDataBase'''

print (@meg)

end

return 0

end

END

成长长青

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 备份表视图函数_工作日记：存储过程备份数据库中指定表数据、表结构、视图、函数、存储过程...

USE [TestDataBase]GO/****** Object: StoredProcedure [dbo].[PT_CreateMineDatabaseBak] Script Date: 2018/7/27 16:49:27 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- ========================...
复制链接

扫一扫