SQL2005数据库附加、分离、查询、备份、还原、删除小程序

k_lb
浏览: 831703 次
性别:
来自: 郑州
最近访客更多访客>>

u012363178
rattersnake
LuffyMother
uclnn
博主相关

博客
微博
相册
留言
关于我
文章分类

社区版块

存档分类

 SQL2005数据库 附加、分离、查询、备份、还原、删除 小程序
: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=saa
::----------------------------------------
::★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   [1]分离%AAA%数据库
Echo.        
Echo   [2]附加 %BBB% 下的%CCC%数据库文件    
Echo. 
Echo   [3]查看数据库中已存在的非系统表
Echo. 
Echo   [4]备份数据库%GGG%到 %HHH%\%III%
Echo. 
Echo   [5]还原" %JJJ% "数据库备份文件为%LLL%.mdf和%LLL%_log.ldf且存放到 %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=%Temp%\1.sql
Echo 正在清理与此数据库的连接，请等待……
::将下面的这句语句写到.sql文件里面，再通过下面的第二条语句调用执行这个.sql文件的内容，第三条语句是删除这个.sql文件，第四条语句开始分离
>"%XXX%" Echo alter database %AAA% set offline with rollback after 1;                                                                                 
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%下已经存在同名的数据库备份文件，而导致文件叠加到一块，这也算是SQL的一个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.bak'"
Echo.
Echo.
Echo 恭喜!恭喜!已成功将SQL中的数据库"%GGG%"备份成%HHH%下文件名为"%III%"的文件
Echo.
set CHS=0
pause
cls
goto dosmenu
::============================================================================
:5555
cls
Echo 数据库文件正在还原，在出现还原成功的提示前请您耐心等待……
set XXX=%Temp%\1.sql
Echo.
Echo.
>"%XXX%"   Echo DECLARE @bakFile nvarchar(1024);                      --定义@bakFile        
>>"%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 varchar(255), PhysicalName varchar(255),Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20), Fileld VARCHAR(20), CreateLSN VARCHAR(20), DropLSN VARCHAR(20), Uniqueld uniqueidentifier, ReadOnlyLSN VARCHAR(20), ReadWriteLSN VARCHAR(20), BackupSizeInBytes VARCHAR(255), SourceBlockSize VARCHAR(20), FileGroupld VARCHAR(20), LogGroupGUID VARCHAR(20), DifferentialBaseLSN VARCHAR(20), DifferentialBaseGUID uniqueidentifier, IsReadOnly VARCHAR(20), IsPresent VARCHAR(20))        
>>"%XXX%" Echo    --建一个名叫#LogicalFileBak的新表，为什么要建这么多字段的理由见⑤和⑥
>>"%XXX%" Echo. 
>>"%XXX%" Echo INSERT #LogicalFileBak EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @bakFile + '''');    --从备份文件中读取逻辑名并存到#LogicalFileBak表中           
>>"%XXX%" Echo. 
>>"%XXX%" Echo DECLARE cur CURSOR FOR SELECT LogicalName,Type,FileGroupName FROM #LogicalFileBak;    --定义一个游标cur(相当于数据集)并将搜索结果放到其中              
>>"%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 + '''';      --将这一串语句赋给@cmd             
>>"%XXX%" Echo SET @cmd = @cmd + ' WITH REPLACE'                                                      --接着将语句赋给@cmd             
>>"%XXX%" Echo. 
>>"%XXX%" Echo OPEN cur;                                --打开游标                                                           
>>"%XXX%" Echo FETCH NEXT FROM cur INTO @LogicalName,@Type,@FileGroupName;             --从这句往下的几句语句用来还原数据库 ,FETCH NEXT表示逐条读取游标中的数据                           
>>"%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;       --删除#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" (
Echo 正在删除，请等待……
::下面这两句用于清除连接
::>"%Temp%\1.sql" Echo alter database %MMM% set offline with rollback after 1;      
::Osql -U"%EEE%" -P"%FFF%" -i %XXX%
cls
::下面这句开始删除
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 库名"
del "%Temp%\1.sql"
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系统表中的name字段。
::可通过SELECT * FROM sysfiles语句查询本库的sysfiles表的字段，如下：
::fileid groupid     size      maxsize   growth status perf    name         filename
:: 1       1          66264       -1         2560     2         0      jx              D:\测试用数据\安吉\ForestFireControl_StandingBook.mdf
:: 2       0          1280         -1        2560      66       0     jx_log         D:\测试用数据\安吉\ForestFireControl_StandingBook_log.ldf
::④★===============================================================================================
::RESTORE FILELISTONLY 语句返回的结果集如果传递到参数呢？请高手指教。
::正常情况下，在查询分析器中   执行   
:: RESTORE   FILELISTONLY   From   disk   =   'path'   
:: 会返回一个结果集。例如：   
:: LogicalName         PhysicalName             Type   FileGroupName         Size        MaxSize     
:: -------------------------------------------------------------------------   
:: Northwind              C:\..\northwnd.mdf        D      PRIMARY               3407872     351843720   
:: Northwind_log       C:\..\northwnd.ldf           L       NULL                    1048576      351843720     
::⑤★================================================================================================
::MS sql server 2000，2005 如何取出restore filelistonly结果集
::            ---------------------------这是SQL SERVER 2000中的原形代码
::            use master
::            go
::            create table #restoreFileList (LogicalName varchar(255), PhysicalName varchar(255),Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20) )
::            declare @filename varchar(1000)
::            select @filename = 'e:\svwlx.bak'
::            insert into #restoreFileList execute('restore filelistonly from disk='''+@filename+'''')
::            insert into #restoreFileList execute('restore filelistonly from disk=''e:\svwlx.bak''')
::            select * from #restoreFileList           
::            ---------------------------
::
::            ---------------------------这是SQL SERVER 2005中的原形代码
::            USE [master]
::            GO
::            create table #restoreFileList4 (LogicalName varchar(255), PhysicalName varchar(255),Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20), Fileld VARCHAR(20), CreateLSN VARCHAR(20), DropLSN VARCHAR(20), Uniqueld uniqueidentifier, ReadOnlyLSN VARCHAR(20), ReadWriteLSN VARCHAR(20), BackupSizeInBytes VARCHAR(255), SourceBlockSize VARCHAR(20), FileGroupld VARCHAR(20), LogGroupGUID VARCHAR(20), DifferentialBaseLSN VARCHAR(20), DifferentialBaseGUID uniqueidentifier, IsReadOnly VARCHAR(20), IsPresent VARCHAR(20))
::            SELECT * FROM #restoreFileList4 
::            insert into #restoreFileList4 execute('restore filelistonly from disk=''d:\Northwind.bak''')
::            SELECT * FROM #restoreFileList4
::⑥★================================================================================================== 
::若5555中不添加这么多的字段，则会出现：列名或所提供值的数目与表定义不匹配   的错误
::原因是：
::比如:有个表A,有字段a,b,你在插入表纪录的时候写成
::insert A(a,b) values(xx,xx,xxx)
::表只有2个列,你写了3个值进去,就会出现你描述的错误信息,具体是哪问题,你根据你的环境去检查一下就知道了。
分享到：