东莞网站建设牛魔网a/广东省最新疫情
需求
(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%.logecho--------------------- >>%logfile%
echo %date%%time%>>%logfile%echo 测试引擎服务是否启动......set /p="测试引擎服务是否启动......">%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="开始重命名数据库/创建数据库......">%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="新建表和存储过程_修改存储过程......">%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="授权_收缩日志......">%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="维护......">%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个月前的备份文件......">%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代理服务......">%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
begintryusemaster;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)+';'
frommaster.dbo.sysprocesseswhere 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)endtrybegincatchDECLARE @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.
);ENDCATCH;--
【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 ' '
USEmaster;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
usemaster;set nocount off;print ' '
print '----------------------------------------------'
print '~~~~~~~~~~4_维护.sql~~~~~~~~~~~~'
print '----------------------------------------------'
print ' '
print '------备份数据库------'
begintrybegin tran
print ' '
print '------操作------'run spcommit tran
endtryBEGINCATCHDECLARE
@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
usemastergo
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=nameFROMsys.databasesWHERE 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
FROMmsdb.dbo.backupset bsINNER JOINmsdb.dbo.backupmediafamily bmfON bs.media_set_id =bmf.media_set_idORDER 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 WITHinitprint ' '
print '------删除数据库'+@drop_dbname+',以及删除'+@bak_dir+'目录下超过半年的bak备份文件------'
print ' '
EXEC master.dbo.xp_fileexist @bak_filename,@flagOUTPUTIF @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 HELPMSG2182 以获得更多的帮助。
【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语法错误的时候才会有内容