【最佳实践】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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA	est_20200716.mdf
MDF文件路径为：D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA	est_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:ackup_bak	est_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:ackup_bak目录下超过半年的bak备份文件------
 

(1 行受影响)

【9】error_20200716.log （错误日志）

只有在有sqlcmd语法错误的时候才会有内容