【最佳实践】bat实现自动化运行sql
需求
（1）需要把现有test库备份，修改成test_当前日期，加入现在是 20200716，就改成 test_200716。
（2）然后重新创建数据库test，把原来的表结构和存储过程函数一类的全部构建到新建的 test 库上。
（3）把现有用户对新建的这个 test 库授权读写访问
（4）把 test_200716 的一些数据清理掉，并且把一部分数据比如某些表的三天内数据插入到新建的 test 库中
（5）然后以为有多个操作，每次我们新建一个 test_200716 这样的，我们就把最老的 比如 test_200602库 给删掉（删之前脚本里有操作先备份避免误删和无法找回）
（6）操作完之后对线上数据库备份，并且删除4个月前的备份文件
【0】bat架构结构
【1】bat脚本
@echo off set today=%date:~0,4%%date:~5,2%%date:~8,2% set logfile=log_%today%.log set errorfile=error_%today%.log echo --------------------- >>%logfile% echo %date%%time%>>%logfile% echo 测试引擎服务是否启动...... set /p="测试引擎服务是否启动......"<nul >>%logfile% sqlcmd -S 127.0.0.1,1433 -Q "declare @i int;select @i=1;" 2>%errorfile% IF ERRORLEVEL 1 echo fail>>%logfile% IF ERRORLEVEL 0 echo ok>>%logfile% echo 开始重命名数据库/创建数据库...... set /p="开始重命名数据库/创建数据库......"<nul >>%logfile% sqlcmd -S 127.0.0.1,1433 -i 1_DB_renameAndCreate.sql -E -b 2>>%errorfile% 1>db_log_%today%.log IF ERRORLEVEL 1 echo fail......请检查db_log_%today%.log 文件>>%logfile% IF ERRORLEVEL 0 echo ok>>%logfile% echo 新建表和存储过程_修改存储过程...... set /p="新建表和存储过程_修改存储过程......"<nul >>%logfile% sqlcmd -S 127.0.0.1,1433 -i 2_新建表和存储过程_修改存储过程.sql -E -b 2>>%errorfile% 1>>db_log_%today%.log IF ERRORLEVEL 1 echo fail......请检查db_log_%today%.log 文件>>%logfile% IF ERRORLEVEL 0 echo ok>>%logfile% echo 授权_收缩日志...... set /p="授权_收缩日志......"<nul >>%logfile% sqlcmd -S 127.0.0.1,1433 -i 3_授权_收缩日志.sql -E -b 2>>%errorfile% 1>>db_log_%today%.log IF ERRORLEVEL 1 echo fail......请检查db_log_%today%.log 文件>>%logfile% IF ERRORLEVEL 0 echo ok>>%logfile% echo 维护...... set /p="维护......"<nul >>%logfile% sqlcmd -S 127.0.0.1,1433 -i 4_维护.sql -E -b 2>>%errorfile% 1>>db_log_%today%.log IF ERRORLEVEL 1 echo fail......请检查db_log_%today%.log 文件>>%logfile% IF ERRORLEVEL 0 echo ok>>%logfile% echo 删除最早的库_删除4个月前的备份文件...... set /p="删除最早的库_删除4个月前的备份文件......"<nul >>%logfile% sqlcmd -S 127.0.0.1,1433 -i 5_删除最早的库_删除4个月前的备份文件.sql -E -b 2>>%errorfile% 1>>db_log_%today%.log IF ERRORLEVEL 1 echo fail......请检查db_log_%today%.log 文件>>%logfile% IF ERRORLEVEL 0 echo ok>>%logfile% echo 启动sql server代理服务...... set /p="启动sql server代理服务......"<nul >>%logfile% net start "SQLSERVERAGENT" 1>>%logfile% net start "SQLSERVERAGENT" 2>>%logfile% echo 执行完毕,请查看%errorfile%与%logfile%! timeout /t 100
【2】1_DB_renameAndCreate.sql（重命名与创建数据库）
修改老数据库 test为 test_当前日期，新建一个test
begin try use master; set nocount on; print '----------------------------------------------' print '~~~~~~~~~~1_DB_renameAndCreate.sql~~~~~~~~~~~~' print '----------------------------------------------' ---------------------------------------------- print ' ' print '------renameDB------' print ' ' declare @db_name varchar(100),@new_db_name varchar(100) declare @sql varchar(3000),@mdf varchar(500),@ldf varchar(500) -- 设置需要修改的数据库名称 --默认 PQDL_LogCenter set @db_name='test' select @new_db_name=@db_name+'_'+right(convert(varchar(8),getdate(),112),6) print '修改信息----老数据库名:'+@db_name+',修改后的数据库名:'+@new_db_name -- 判断@db_name指定的数据库是否存在 if not exists(select 1 from master.sys.master_files where database_id=db_id(@db_name)) begin declare @error_msg varchar(200) set @error_msg='renamedb.sql => 指定的数据库'+@db_name+'不存在！...' RAISERROR (@error_msg, 16, 1); return end -- 杀需要重命名的数据库进程 set @sql='' select @sql=@sql+';kill '+cast(spid as varchar)+';' from master.dbo.sysprocesses where dbid=db_id(@db_name) exec(@sql) -- 重命名操作 set @sql='sp_renamedb '+@db_name+','+@new_db_name+';' exec(@sql) -- 收缩日志 DBCC SHRINKDATABASE (@new_db_name, TRUNCATEONLY) ------------------------------------------------- print ' ' print '------createDB------' print ' ' declare @path_dir nvarchar(500) declare @mdf_path nvarchar(500),@ldf_path nvarchar(500) select top(1) @path_dir=filename from master.sys.sysdatabases where name like '%'+@db_name+'%' set @path_dir=left(@path_dir,len(@path_dir)-charindex('\',reverse(@path_dir))+1) select @mdf_path=@path_dir+@db_name+'_'+convert(char(8),getdate(),112)+'.mdf' select @ldf_path=@path_dir+@db_name+'_'+convert(char(8),getdate(),112)+'_log.ldf' print '创建数据库'+@db_name print 'MDF文件路径为：'+@mdf_path print 'MDF文件路径为：'+@ldf_path set @sql=' create database '+@db_name+' on primary ( name='+@db_name+', filename='''+@mdf_path+''', size=10MB, filegrowth=32MB ) log on ( name='+@db_name+'_log, filename='''+@ldf_path+''', size=10MB, filegrowth=10MB ); alter database '+@db_name+' set recovery simple with no_wait ' exec(@sql) end try begin catch DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); print @errorMessage RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH; --
【3】2_新建表和存储过程_修改存储过程.sql
比较机密，就不贴具体代码了
set nocount on; print ' ' print '----------------------------------------------' print '~~~~~~~~~~2_新建表和存储过程_修改存储过程.sql~~~~~~~~~~~~' print '----------------------------------------------' print ' ' print '------创建表和存储过程------' print ' ' ----------------------------------------------[2] print ' ' print '------更新存储过程------' print ' ' ----------------------------------------------[3]导入前三天的日志 print ' ' print '------导入前三天日志------' print ' '
【4】3_授权_收缩日志.sql
set nocount on; print ' ' print '----------------------------------------------' print '~~~~~~~~~~3_授权_收缩日志.sql~~~~~~~~~~~~' print '----------------------------------------------' print ' ' print '------授权------' print ' ' USE master; if not exists(select 1 from master.sys.syslogins where name='test_user') CREATE USER [test_user] FOR LOGIN [test_user] USE [test] if not exists(select 1 from sysusers where name ='test_user') begin CREATE USER [test_user] FOR LOGIN [test_user] end EXEC sp_addrolemember N'db_datareader', N'test_user' EXEC sp_addrolemember N'db_datawriter', N'test_user' EXEC sp_addrolemember N'db_owner', N'test_user' USE [master] if not exists(select 1 from master.sys.syslogins where name='testQuery') CREATE LOGIN testQuery with password='a123456!' USE [test] if not exists (select 1 from sysusers where name ='testQuery') begin CREATE USER [testQuery] FOR LOGIN [testQuery] end EXEC sp_addrolemember N'db_datareader', N'testQuery' ---------------------------------------------- print ' ' print '------收缩------' print ' ' declare @db_name varchar(100) declare @logic_name varchar(100) set @db_name='test' select top(1) @logic_name=name from master.sys.master_files where type_desc='LOG' and database_id=db_id(@db_name) print @logic_name dbcc shrinkfile(@logic_name,10)
【5】4_维护.sql
use master; set nocount off; print ' ' print '----------------------------------------------' print '~~~~~~~~~~4_维护.sql~~~~~~~~~~~~' print '----------------------------------------------' print ' ' print '------备份数据库------' begin try begin tran print ' ' print '------操作------' run sp commit tran end try BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(MAX) , @ErrorSeverity INT , @ErrorState INT , @exception NVARCHAR(255); SELECT @ErrorMessage = ERROR_MESSAGE() , @ErrorSeverity = ERROR_SEVERITY() , @ErrorState = ERROR_STATE(); SET @exception = '(State ' + CAST(@ErrorState AS NVARCHAR(20)) + ', Severity ' + CAST(@ErrorSeverity AS NVARCHAR(20)) + ') ' + @ErrorMessage; RAISERROR (@exception,16,1); ROLLBACK tran; PRINT '回滚成功'
end catch
【6】5_删除最早的库_删除4个月前的备份文件.sql
use master go PRINT ' ' print '----------------------------------------------' print '~~~~~~~~~~5_删除最早的库_删除4个月前的备份文件.sql~~~~~~~~~~~~' print '----------------------------------------------' ---------------------------------------------- DECLARE @drop_dbname VARCHAR(300),@Bak_dir VARCHAR(500),@bak_filename VARCHAR(500) DECLARE @delete_Day_before datetime, @flag INT,@db_name varchar(200) set @db_name='test' SELECT TOP(1) @drop_dbname=name FROM sys.databases WHERE name LIKE @db_name+'_%' ORDER BY CAST(right(name,6) AS INT) ASC SELECT top(1) @Bak_dir=left(bmf.physical_device_name,len(bmf.physical_device_name)-charindex('\',reverse(bmf.physical_device_name))+1) --, -- bs.backup_set_id, -- bs.database_name, -- bs.backup_start_date, -- bs.backup_finish_date, -- CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size], -- CAST(DATEDIFF(second, bs.backup_start_date, -- bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken], -- CASE bs.[type] -- WHEN 'D' THEN 'Full Backup' -- WHEN 'I' THEN 'Differential Backup' -- WHEN 'L' THEN 'TLog Backup' -- WHEN 'F' THEN 'File or filegroup' -- WHEN 'G' THEN 'Differential file' -- WHEN 'P' THEN 'Partial' -- WHEN 'Q' THEN 'Differential Partial' -- END AS BackupType, -- CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn, -- CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn, -- bs.server_name, -- bs.recovery_model FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id ORDER BY bs.backup_start_date desc; SET @bak_filename=@Bak_dir+@drop_dbname+'_'+CONVERT(CHAR(8),GETDATE(),112)+'_recovery.bak' PRINT '要删除的数据库是:'+@drop_dbname+'，为了防止误删备份文件在：'+@bak_filename print ' ' print '------备份要删除的数据库 '+@drop_dbname+'------' print ' ' BACKUP DATABASE @drop_dbname TO DISK=@bak_filename WITH init print ' ' print '------删除数据库 '+@drop_dbname+',以及删除'+@bak_dir+'目录下超过半年的bak备份文件------' print ' ' EXEC master.dbo.xp_fileexist @bak_filename,@flag OUTPUT IF @flag!=0 begin EXEC('DROP DATABASE '+@drop_dbname) END ELSE BEGIN RAISERROR('删除数据库失败！因为备份文件不存在，为了安全，不允许删除该数据库！',16,1) END SET @delete_Day_before=GETDATE()-120 --4个月 EXEC master.dbo.xp_delete_file 0,@Bak_dir,'bak',@delete_Day_before
【7】log_20200716.log （流程日志）
--------------------- 2020/07/16 周四16:06:53.13 测试引擎服务是否启动......ok 开始重命名数据库/创建数据库......ok 新建表和存储过程_修改存储过程......ok 授权_收缩日志......ok 维护......fail......请检查db_log_20200716.log 文件 ok 删除最早的库_删除4个月前的备份文件......ok 启动sql server代理服务......请求的服务已经启动。 请键入 NET HELPMSG 2182 以获得更多的帮助。
【8】db_log_20200716（输出日志）
已将数据库上下文更改为 'master'。 ---------------------------------------------- ~~~~~~~~~~1_DB_renameAndCreate.sql~~~~~~~~~~~~ ---------------------------------------------- ------renameDB------ 修改信息----老数据库名:test,修改后的数据库名:test_200716 数据库 名称 'test_200716' 已设置。 DBCC SHRINKDATABASE: 已跳过数据库 ID 13 的文件 ID 1，因为该文件没有足够的可用空间可以回收。 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 ------createDB------ 创建数据库test MDF文件路径为：D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_20200716.mdf MDF文件路径为：D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_20200716_log.ldf 已将数据库上下文更改为 'test'。 ---------------------------------------------- ~~~~~~~~~~2_新建表和存储过程_修改存储过程.sql~~~~~~~~~~~~ ---------------------------------------------- ------创建表和存储过程------ ------更新存储过程------ today:200716 ------复制老数据库的数据到新数据库------ ---------------------------------------------- ~~~~~~~~~~3_授权_收缩日志.sql~~~~~~~~~~~~ ---------------------------------------------- ------授权------ 已将数据库上下文更改为 'master'。 已将数据库上下文更改为 'test'。 已将数据库上下文更改为 'master'。 已将数据库上下文更改为 'test'。 ------收缩------ test_log DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages ------ ----------- ----------- ----------- ----------- -------------- 10 2 1280 1280 1280 1280 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 已将数据库上下文更改为 'master'。 ---------------------------------------------- ~~~~~~~~~~4_维护.sql~~~~~~~~~~~~ ---------------------------------------------- ------备份数据库------ 消息 911，级别 16，状态 1，服务器 BF-DBP-01，第 15 行 在 sysdatabases 中找不到数据库 'BOX_ServerCenter' 所对应的条目。没有找到具有该名称的条目。请确保正确地输入了该名称。 已将数据库上下文更改为 'master'。 ---------------------------------------------- ~~~~~~~~~~5_删除最早的库_删除4个月前的备份文件.sql~~~~~~~~~~~~ ---------------------------------------------- 要删除的数据库是:test_200716，为了防止误删备份文件在：D:\backup_bak\test_200716_20200716_recovery.bak ------备份要删除的数据库 test_200716------ 已为数据库 'test_200716'，文件 'test' (位于文件 1 上)处理了 232 页。 已为数据库 'test_200716'，文件 'test_log' (位于文件 1 上)处理了 1 页。 BACKUP DATABASE 成功处理了 233 页，花费 0.609 秒(3.134 MB/秒)。 ------删除数据库 test_200716,以及删除D:\backup_bak\目录下超过半年的bak备份文件------ (1 行受影响)
【9】error_20200716.log （错误日志）
只有在有sqlcmd语法错误的时候才会有内容
