bat批处理 SQL2000数据库附加、分离、查询、备份、还原、删除小程序

@Echo off
::本程序在XP环境下对SQL2000 数据库测试通过

::SQL2005的见 ： http://hi.baidu.com/yjjjjs/blog/item/7236891afe0be4f6ae513360.html

Title SQL2000数据库 附加、分离、查询、备份、还原、删除 小程序
:dosmenu

::▇AAA代表在SQL中看到的[要分离、附加的数据库名]
::▇BBB代表[要附加的数据库的位置]            CCC代表[要附加的数据库文件名]            DDD代表[要附加的数据库日志文件名]
::▇EEE代表[指定的数据库实例用户名]          FFF代表[指定的数据库实例用户的密码] （这里指定的实例是"127.0.0.1"或"Local"或"."即指本机SQL自带的实例）
::▇GGG代表[要备份的数据库名]                HHH代表[数据库备份的路径]                III代表[备份后的名字]
::▇JJJ代表[用于还原的数据库备份文件所在的路径+文件名]   KKK代表[还原后的数据库文件所存放的路径]   LLL代表[还原数据库后在SQL中所显示的名称]
::▇MMM代表[要删除的数据库名]
::#**********************************************************************#
::★1、【分离】请编辑AAA
set AAA=ForestFireControl_StandingBook
::----------------------------------------
::★2、【附加】请编辑AAA、BBB、CCC、DDD
set BBB=C:\
set CCC=ForestFireControl_StandingBook.mdf
set DDD=ForestFireControl_StandingBook_log.ldf
::----------------------------------------
::★设定数据库的登入【账号和密码】请编辑EEE、FFF
set EEE=sa
set FFF=sa
::----------------------------------------
::★4、【备份】请编辑GGG、HHH、III
set GGG=ForestFireControl_StandingBook
set HHH=C:\
set III=备份的数据库2.bak
::----------------------------------------
::★5、【还原】请编辑JJJ、KKK、LLL
set JJJ=C:\备份的数据库2.bak
set KKK=C:\
set LLL=ForestFireControl_StandingBook
::----------------------------------------
::★6、【删除】请编辑MMM
set MMM=ForestFireControl_StandingBook
::#**********************************************************************#
REM 选择菜单

Echo ★★★注意：本文件最好不要放在路径名中含有空格的位置上运行(如桌面等位置)    
Echo #**********************************************************************#
Echo              选项如下（可用记事本打开对相应参数重新编辑）
Echo #**********************************************************************#
Echo.
Echo   [1]分离%AAA%数据库
Echo.       
Echo   [2]附加%BBB%下的%CCC%数据库文件   
Echo.
Echo   [3]查看数据库中已存在的非系统表
Echo.
Echo   [4]备份数据库%GGG%到%HHH%%III%
Echo.
Echo   [5]还原"%JJJ%"数据库备份文件为%LLL%.mdf且存放到%KKK%下
Echo.
Echo   [6]彻底删除数据库%MMM%
Echo.
Echo       退出请按其他键
Echo.
Echo #**********************************************************************#
Echo 选择后请按回车进行确认
Echo.
set /P CHS=请选择: [1],[2],[3],[4],[6]?

if /I "%CHS%"=="1" goto 1111
if /I "%CHS%"=="2" goto 2222
if /I "%CHS%"=="3" goto 3333
if /I "%CHS%"=="4" goto 4444
if /I "%CHS%"=="5" goto 5555
if /I "%CHS%"=="6" goto 6666
goto zzzz
::============================================================================
:1111
cls
set XXX=C:\121.sql
Echo 正在清理与此数据库的连接，请等待……
::将下面的这三句语句写到.sql文件里面，再通过下面的第四条语句调用执行这个.sql文件的内容，第五条语句是删除这个.sql文件，第六条语句开始分离
>"%XXX%" Echo use   master                                                                                                   
>>"%XXX%" Echo declare hcforeach cursor global for select 'kill '+rtrim(spid) from sysprocesses where dbid=db_id(N'%AAA%')     
>>"%XXX%" Echo exec   sp_msforeach_worker   '?'                                                                                
Osql -U"%EEE%" -P"%FFF%" -i %XXX%
del %XXX%
OSQL -E -Q "SP_DETACH_DB %AAA%"
::其他例子： osql -U"sa" -P"sa" -S"127.0.0.1" -Q "sp_detach_db '库名'"
Echo.
Echo.
Echo 分离SQL中名为%AAA%的数据库成功
Echo.
set CHS=0
pause
cls
goto dosmenu
::============================================================================
:2222
cls
OSQL -U"%EEE%" -P"%FFF%" -S"127.0.0.1" -Q "sp_attach_db '%AAA%','%BBB%\%CCC%','%BBB%\%DDD%'"
Echo.
Echo.
Echo 附加%BBB%下的数据库文件%CCC%到SQL中成功
Echo.
::★★★ OSQL -U"sa" -P"sa" -S"127.0.0.1" -Q "sp_attach_db '库名','路径\数据文件名','路径\日志文件名'"
set CHS=0
pause
cls
goto dosmenu
::============================================================================
:3333
cls
OSQL -E -Q "SELECT NAME,FILENAME FROM MASTER..SYSDATABASES WHERE name<>'master' and name<>'tempdb' and name<>'model' and name<>'msdb' "
::★★★上面语句中FROM后面的MASTER..SYSDATABASES表示系统自带的MASTER数据库中的SYSDATABASES表
set CHS=0
pause
cls
goto dosmenu
::============================================================================
:4444
cls
del %HHH%%III%
cls
::加上述的del %HHH%%III%语句的作用是为了防止在路径%III%下已经存在同名的数据库备份文件，而导致文件叠加到一块，这也算是SQL2000的一个Bug
Echo 正在备份数据库，在出现成功的提示之前请您耐心等待……
Echo.
Echo.
OSQL -U"%EEE%" -P"%FFF%" -S"127.0.0.1" -d"%GGG%"   -Q "Backup DataBase %GGG% to disk = '%HHH%%III%'"
::★★★OSQL -U"sa" -P"sa" -S"127.0.0.1" -d"CircleImage" -Q "Backup DataBase CircleImage to disk = 'c:\111.db'"
Echo.
Echo.
Echo 恭喜!恭喜!已成功将SQL中的数据库"%GGG%"备份成%HHH%下文件名为"%III%"的文件
Echo.
set CHS=0
pause
cls
goto dosmenu
::============================================================================
:5555
cls
Echo 数据库文件正在还原，在出现还原成功的提示前请您耐心等待……
set XXX=C:\123.sql

>"%XXX%"   Echo DECLARE @bakFile nvarchar(1024);                               
>>"%XXX%" Echo SET @bakFile = N'%JJJ%';                                      
>>"%XXX%" Echo.                               
>>"%XXX%" Echo DECLARE @restorePath nvarchar(1024);                           
>>"%XXX%" Echo SET @restorePath = N'%KKK%';                                  
>>"%XXX%" Echo.
>>"%XXX%" Echo DECLARE @dbname nvarchar(128);                                 
>>"%XXX%" Echo SET @dbname = N'%LLL%';                                       
>>"%XXX%" Echo.
>>"%XXX%" Echo DECLARE @filename nvarchar(128);                              
>>"%XXX%" Echo SET @filename = @dbname;                                      
>>"%XXX%" Echo.
>>"%XXX%" Echo CREATE TABLE #LogicalFileBak(LogicalName nvarchar(128),        
>>"%XXX%" Echo                                 PhysicalName nvarchar(260),       
>>"%XXX%" Echo                                 Type char(1),                     
>>"%XXX%" Echo                                 FileGroupName nvarchar(128),      
>>"%XXX%" Echo                                 [Size] numeric(20,0),             
>>"%XXX%" Echo                                 [MaxSize] numeric(20,0) );       
>>"%XXX%" Echo.
>>"%XXX%" Echo   INSERT #LogicalFileBak EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @bakFile + '''');              
>>"%XXX%" Echo.
>>"%XXX%" Echo   DECLARE cur CURSOR FOR SELECT LogicalName,Type,FileGroupName FROM #LogicalFileBak;                
>>"%XXX%" Echo   DECLARE @LogicalName nvarchar(128),@Type char(1),@FileGroupName nvarchar(128);                     
>>"%XXX%" Echo.
>>"%XXX%" Echo   DECLARE @cmd nvarchar(4000);                                                                       
>>"%XXX%" Echo   SET @cmd = 'RESTORE DATABASE [' + @dbname + '] FROM DISK = ''' + @bakFile + '''';                  
>>"%XXX%" Echo   SET @cmd = @cmd + ' WITH REPLACE'                                                                  
>>"%XXX%" Echo.
>>"%XXX%" Echo   OPEN cur;                                                                                         
>>"%XXX%" Echo   FETCH NEXT FROM cur INTO @LogicalName,@Type,@FileGroupName;                                       
>>"%XXX%" Echo   WHILE @@FETCH_STATUS = 0                                                                           
>>"%XXX%" Echo        BEGIN                                                                                         
>>"%XXX%" Echo             SET @cmd = @cmd + ',MOVE ''' + @LogicalName + ''' TO ''' + @restorePath                   
>>"%XXX%" Echo                             + @filename + CASE WHEN @Type = 'D' AND @FileGroupName = 'PRIMARY'        
>>"%XXX%" Echo                                                    THEN '.mdf'                                        
>>"%XXX%" Echo                                                WHEN @Type = 'D' AND @FileGroupName ^<^> 'PRIMARY'     
>>"%XXX%" Echo                                                    THEN '.ndf'                                       
>>"%XXX%" Echo                                                ELSE '_log.ldf'                                      
>>"%XXX%" Echo                                                END + ''''                                         
>>"%XXX%" Echo             FETCH NEXT FROM cur INTO @LogicalName,@Type,@FileGroupName;                            
>>"%XXX%" Echo        END                                                                                           
>>"%XXX%" Echo   CLOSE cur;                                                                                      
>>"%XXX%" Echo   DEALLOCATE cur;                                                                                   
>>"%XXX%" Echo.
>>"%XXX%" Echo   EXEC(@cmd);                                                                                        
>>"%XXX%" Echo.
>>"%XXX%" Echo   DROP TABLE #LogicalFileBak;                                                                        

Osql -U"%EEE%" -P"%FFF%" -i %XXX%
del %XXX%

Echo.
Echo.
Echo 恭喜!已成功将"%JJJ%"还原成%LLL%，且还原后的文件存放在%KKK%下
Echo.
pause
set CHS=0
cls
goto dosmenu
::============================================================================
:6666
Echo 真的要删除吗？删除后将不可恢复
Echo.
set /P QR=确定删除请按y，放弃删除请按其他键，请选择?
cls
if /I "%QR%"=="y" (
OSQL -U"%EEE%" -P"%FFF%" -S"127.0.0.1" -Q "Drop DataBase %MMM%"
::其他例子：OSQL -U"sa" -P"sa" -S"127.0.0.1" -Q "Drop DataBase 库名"
Echo.
Echo 删除SQL中名为%AAA%的数据库及源文件成功
Echo.
pause
)
set QR=0
set CHS=0
cls
goto dosmenu
::============================================================================
:zzzz
REM 退出
exit

::**************************************************************************************************
::**************************************************************************************************
::**************************************************************************************************
::分离数据库的命令：Sp_detach_db 数据库名
::　　连接数据库的命令：Sp_attach_db或者sp_attach_single_file_db
::　　sp_attach_db [@dbname =] 'dbname', [@filename1 =] 'filename_n' [,...16]
::　　sp_attach_single_file_db [@dbname =] 'dbname', [@physname =] 'physical_name'
::
::使用此方法可以正确附加和分离SQL Sever7.0和SQL Server 2000的数据库文件，要点是备份的时候一定要将mdf和ldf两个文件都备份下来，mdf文件是数据库数据文件，ldf是日志文件。

::OSQL -? 显示帮助（具体如下所示）
::用法: osql [-U 登录 ID] [-P 密码]
::[-S 服务器] [-H 主机名] [-E 可信连接]
::[-d 使用数据库名称] [-l 登录超时值] [-t 查询超时值]
::[-h 标题] [-s 列分隔符] [-w 列宽]
::[-a 数据包大小] [-e 回显输入] [-I 允许带引号的标识符]
::[-L 列出服务器] [-c 命令结束] [-D ODBC DSN 名称]
::[-q "命令行查询"] [-Q "命令行查询" 并退出]
::[-n 删除编号方式] [-m 错误级别]
::[-r 发送到 stderr 的消息] [-V 严重级别]
::[-i 输入文件] [-o 输出文件]
::[-p 打印统计信息] [-b 出错时中止批处理]

::★★★可通过下面的语句查询已有备份库的逻辑文件名，以用于 还原数据库语句操作 中的操作
::OSQL -U"sa" -P"sa" -S"127.0.0.1"   -Q "restore filelistonly from disk ='C:\备份的数据库1'"
::
::OSQL -U"sa" -P"sa" -S"127.0.0.1"   -Q "restore filelistonly from disk ='C:\备份的数据库1'" >>C:\1234.txt

::=============================================================================================
::★★★★★ 还原数据库语句操作
::其中的 数据库的逻辑文件名 和 数据库日志的逻辑文件名 是通过上述的语句来查询的

::格式说明：
::osql -E -d CircleImage -Q "restore database 备份后SQL中看到的数据库名 from disk='用于还原的备份文件的路径+文件名' with move '数据库的逻辑文件名' TO '备份后的路径+实际文件名', MOVE '数据库日志的逻辑文件名' TO '备份后的路径+实际文件名'"

::实例：
::OSQL -U"sa" -P"sa" -S"127.0.0.1" -Q "restore database CircleImage from disk='C:\CircleImage备份' with move 'CircleImage_Data' to 'c:\CircleImage.mdf',move ::'CircleImage_Log' to 'c:\CircleImage_log.ldf'"

::上述这么长的语句中切不可出现回车等换行符，且参数的大小写是不可随意更改的

::=============================================================================================
::★★ 更改数据库备份文件逻辑名的方法：

::alter database 数据库名 modify file (name=逻辑名,newname=新逻辑名)

::事实上，上面的语句是更改了sysfiles和sysfiles1系统表中的name字段。