公司做用siss软件,很多客户的数据库经常置疑,而处理流程就是这么一条,隧做成熟悉的bat..
另外还做了一个BCP修复数据的批处理,考虑到连接数据库有用到密码[机密]就赞不透露了。
从这个批处理可以看出bat的应用可以无所不在..
不涉及版权,欢迎修改以满足个人所需。- @echo off & setlocal ENABLEEXTENSIONS
- ::此版传递参数版本
- ::数据库置疑批处理sql2000
- ::数据无误也可以用此批处理,可达到缩减日志的作用
- ::Made By [CDKLD]-006
- ::0:45 2010-10-29
- ::使用前提,SQL2000已经安装-Path在默认路径里了-切安装目录为D:\Program Files\Mircro~1\mssql\data\
- title siss软件-数据库置疑-快速修复 Made By [CDKLD]-006
- color 0a
- :start
- cls
- set /p your_database=请输入需要处理的[置疑的数据库名]:
- if /i "%your_database%" equ "" goto :out
- if not exist "D:\Program Files\Microsoft SQL Server\MSSQL\Data\%your_database%_data.mdf" goto :not_exist_database
- call :down_Server
- dir /ad %SystemDrive%\my_backup >nul 2>nul||md %SystemDrive%\my_backup 2>nul
- echo 备份用目录为[%SystemDrive%\my_backup]
- echo.
- echo 备份数据库中...时间长短由所备份的【%your_database%】数据库大小决定。
- if exist "D:\Program Files\Microsoft SQL Server\MSSQL\Data\%your_database%_data.mdf" copy /y "D:\Program Files\Microsoft SQL Server\MSSQL\Data\%your_database%_data.mdf" "%SystemDrive%\my_backup\%your_database%_data%date:~0,10%.mdf"
- if exist "D:\Program Files\Microsoft SQL Server\MSSQL\Data\%your_database%_log.ldf" copy /y "D:\Program Files\Microsoft SQL Server\MSSQL\Data\%your_database%_log.ldf" "%SystemDrive%\my_backup\%your_database%_log%date:~0,10%.ldf"
- call :up_Server
- echo -------备份【%your_database%】成功,任意键-放心进入数据库修复操作!-------
- pause>nul
- cls
- echo 置疑数据库【%your_database%】的修复工作已经开始...
- isql -H. -E -Q "drop database %your_database%"
- call :down_Server
- del /a /q /f "D:\Program Files\Microsoft SQL Server\MSSQL\Data\%your_database%_data.mdf" 2>nul
- if exist "D:\Program Files\Microsoft SQL Server\MSSQL\Data\%your_database%_log.ldf" del /a /q /f "D:\Program Files\Microsoft SQL Server\MSSQL\Data\%your_database%_log.ldf"
- call :up_Server
- echo 即将创建新-原同名数据库【%your_database%】...&ping 127.1 -n 3 >nul 2>nul
- isql -H. -E -Q "create database %your_database% on (name='%your_database%_data',filename='D:\Program Files\Microsoft SQL Server\MSSQL\Data\%your_database%_data.mdf',size=10,filegrowth=5)log on(name='%your_database%_log',filename='D:\Program Files\Microsoft SQL Server\MSSQL\Data\%your_database%_log.ldf',size=1,filegrowth=1)"
- call :down_Server
- if exist "D:\Program Files\Microsoft SQL Server\MSSQL\Data\%your_database%_log.ldf" del /a/q/f "D:\Program Files\Microsoft SQL Server\MSSQL\Data\%your_database%_log.ldf"
- copy /y "%SystemDrive%\my_backup\%your_database%_data%date:~0,10%.mdf" "D:\Program Files\Microsoft SQL Server\MSSQL\Data\%your_database%_data.mdf"&&echo 已经覆盖新建的同名数据库文件!
- call :up_Server
- dir /ad .\%your_database% >nul 2>nul||md %your_database% 2>nul
- if not exist .\%your_database%\%your_database%_001.sql call :data_control001
- isql -H. -E -i .\%your_database%\%your_database%_001.sql
- call :down_Server
- call :up_Server
- if not exist .\%your_database%\%your_database%_002.sql call :data_control002
- isql -H. -E -i .\%your_database%\%your_database%_002.sql
- echo.
- call :down_Server
- call :up_Server
- if not exist .\%your_database%\%your_database%_003.sql call :data_control003
- isql -H. -E -i .\%your_database%\%your_database%_003.sql
- echo.
- echo -------任意键查询【%your_database%】的修复状况----------&&pause>nul&&echo 修复状况即将揭晓...&&ping 127.1 -n 3 >nul 2>nul&&isql -H. -E -Q "dbcc checkdb(%your_database%)"
- ping 127.1 -n 3 >nul
- :is_Right
- echo.
- echo #@#执行结果为"CHECKDB 发现了0个分配错误,0个一致性错误"---^>修善的目的达到!
- set /p all_right=数据库置疑是否已经达到,选否为进行后续的数据库修复[Y/N]:
- if /i "%all_right%" equ "y" goto :end
- if /i "%all_right%" equ "n" goto :need_repair
- echo.
- echo 你的输入有错,请重新输入!
- echo.
- set all_right=
- ping 127.1 -n 2 >nul 2>nul
- goto :is_Right
- :need_repair
- if not exist .\%your_database%\repair_%your_database%_rebuild.sql call :data_rebuild
- isql -H. -E -i .\%your_database%\repair_%your_database%_rebuild.sql
- echo.
- echo.
- echo #@#执行结果为"CHECKDB 发现了0个分配错误,0个一致性错误"---^>修善的目的达到!
- set /p all_right_two=数据库置疑是否已经达到效果[Y/N]:
- if /i "%all_right_two%" equ "y" goto :not_single
- if not exist .\%your_database%\repair_%your_database%_loss.sql call:data_repair
- isql -H. -E -i .\%your_database%\repair_%your_database%_loss.sql
- echo.
- :not_single
- if not exist .\%your_database%\%your_database%_not_single_user.sql call :data_no_single_user
- isql -H. -E -i .\%your_database%\%your_database%_not_single_user.sql
- echo.
- echo 高级修复已完成^^-^^&&ping 127.1 -n 5 >nul
- :end
- cls
- echo Wow,置疑数据库【%your_database%】修复完成!
- echo 若还有问题,请直接咨询 CDKLD-006 Tel:02885242244&&pause>nul
- exit
-
- :up_Server
- (sc start MSSQLSERVER >nul 2>nul||net start MSSQLSERVER >nul 2>nul)&ping 127.0.0.1 -n 4 >nul 2>nul
- echo ******数据库服务[MSSQLSERVER]已成功的启动!******
- goto :eof
-
- :down_Server
- (sc stop MSSQLSERVER >nul 2>nul||net stop MSSQLSERVER >nul 2>nul)&ping 127.0.0.1 -n 3 >nul 2>nul
- echo ******数据库服务[MSSQLSERVER]已成功的停止!******
- goto :eof
- :not_exist_database
- cls
- echo 数据库【%your_database%】不存在,请确认!
- set your_database=
- pause>nul&goto :start
- :out
- echo 请输入程序运行必备参数【置疑数据库名】-愿你好运...
- set your_database=
- pause>nul&goto :start
-
-
- :data_control001
- echo Use Master>%your_database%\%your_database%_001.sql
- echo Go>>.\%your_database%\%your_database%_001.sql
- echo sp_configure 'allow updates', ^1>>%your_database%\%your_database%_001.sql
- echo reconfigure with override>>%your_database%\%your_database%_001.sql
- echo Go>>%your_database%\%your_database%_001.sql
- echo begin tran>>%your_database%\%your_database%_001.sql
- echo update sysdatabases set status = 32768 where name = '%your_database%'>>%your_database%\%your_database%_001.sql
- echo commit tran>>%your_database%\%your_database%_001.sql
- goto :eof
-
- :data_control002
- echo USE MASTER>.\%your_database%\%your_database%_002.sql
- echo GO>>.\%your_database%\%your_database%_002.sql
- echo DBCC TRACEON(3604)>>.\%your_database%\%your_database%_002.sql
- echo DBCC REBUILD_LOG('%your_database%','D:\Program Files\Microsoft SQL Server\MSSQL\Data\%your_database%_log.ldf')>>.\%your_database%\%your_database%_002.sql
- echo Go>>.\%your_database%\%your_database%_002.sql
- goto :eof
- :data_control003
- echo use master>.\%your_database%\%your_database%_003.sql
- echo update sysdatabases set status = 8 where name = '%your_database%'>>.\%your_database%\%your_database%_003.sql
- echo Go>>.\%your_database%\%your_database%_003.sql
- echo sp_configure 'allow updates', ^0>>.\%your_database%\%your_database%_003.sql
- echo reconfigure with override>>.\%your_database%\%your_database%_003.sql
- echo Go>>.\%your_database%\%your_database%_003.sql
- goto :eof
- :data_rebuild
- echo USE master>%your_database%\repair_%your_database%_rebuild.sql
- echo Go>>%your_database%\repair_%your_database%_rebuild.sql
- echo EXEC sp_dboption '%your_database%', 'single user', 'TRUE'>>%your_database%\repair_%your_database%_rebuild.sql
- echo go>>%your_database%\repair_%your_database%_rebuild.sql
- echo DBCC CHECKDB ('%your_database%','repair_rebuild')>>%your_database%\repair_%your_database%_rebuild.sql
- echo Go>>%your_database%\repair_%your_database%_rebuild.sql
- echo DBCC CHECKDB ('%your_database%')>>%your_database%\repair_%your_database%_rebuild.sql
- echo Go>>%your_database%\repair_%your_database%_rebuild.sql
- goto :eof
- :data_repair
- echo USE master>%your_database%\repair_%your_database%_loss.sql
- echo GO>>%your_database%\repair_%your_database%_loss.sql
- echo DBCC CHECKDB ('%your_database%','repair_allow_data_loss')>>%your_database%\repair_%your_database%_loss.sql
- echo Go>>%your_database%\repair_%your_database%_loss.sql
- echo DBCC CHECKDB ('%your_database%')>>%your_database%\repair_%your_database%_loss.sql
- echo Go>>%your_database%\repair_%your_database%_loss.sql
- echo EXEC sp_dboption '%your_database%', 'single user','FALSE'>>%your_database%\repair_%your_database%_loss.sql
- echo go>>%your_database%\repair_%your_database%_loss.sql
- goto :eof
- :data_no_single_user
- echo USE master>%your_database%\%your_database%_not_single_user.sql
- echo Go>>%your_database%\%your_database%_not_single_user.sql
- echo EXEC sp_dboption '%your_database%', 'single user','FALSE'>>%your_database%\%your_database%_not_single_user.sql
- echo go>>%your_database%\%your_database%_not_single_user.sql
- goto :eof
复制代码
[ 本帖最后由 bat_fan 于 2010-11-6 18:20 编辑 ] |