[其他] [分享]数据库置疑-批处理修复

@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
复制代码