开发笔记:batbat实现自动化脚本实战

@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

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

set nocount on;
print ‘ ‘
print ‘----------------------------------------------‘
print ‘~~~~~~~~~~2_新建表和存储过程_修改存储过程.sql~~~~~~~~~~~~‘
print ‘----------------------------------------------‘
print ‘ ‘
print ‘------创建表和存储过程------‘
print ‘ ‘
----------------------------------------------[2]
print ‘ ‘
print ‘------更新存储过程------‘
print ‘ ‘
----------------------------------------------[3]导入前三天的日志
print ‘ ‘
print ‘------导入前三天日志------‘
print ‘ ‘

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)

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

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

---------------------
2020/07/16 周四16:06:53.13
测试引擎服务是否启动......ok
开始重命名数据库/创建数据库......ok
新建表和存储过程_修改存储过程......ok
授权_收缩日志......ok
维护......fail......请检查db_log_20200716.log 文件
ok
删除最早的库_删除4个月前的备份文件......ok
启动sql server代理服务......请求的服务已经启动。
请键入 NET HELPMSG 2182 以获得更多的帮助。

已将数据库上下文更改为 ‘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 行受影响)