数据库打包批处理

1.完整备份数据库

 

1. @ECHO OFF  
2. @SETLOCAL  
3. @CLS  
4. @ECHO .  
5. @ECHO . --- %~nx0% - 资 料库完整备份作业 开始 ---  
6. @ECHO . --- %~dp0%  
7. @ECHO . --- %CD%  
8. @ECHO .       数 据库完整备份方案  
9. @ECHO .     
10. @ECHO .  
11. @ECHO .  
12. rem @IF "%SQLCMDSERVER%"       ==  ""    @SET /P SQLCMDSERVER=.   请输入资料库伺服器名 称 [127.0.0.1]：  
13. @IF "%SQLCMDSERVER%"       ==  ""    @SET SQLCMDSERVER=127.0.0.1  
14. rem @IF "%SQLCMDUSER%"         ==  ""    @SET /P SQLCMDUSER=.   请输入资料库使用者名 称 [sa]：  
15. @IF "%SQLCMDUSER%"         ==  ""    @SET SQLCMDUSER=sa  
16. rem @IF "%SQLCMDPASSWORD%"     ==  ""    @SET /P SQLCMDPASSWORD=.   请输入资料库验证密 码 [12345]：  
17. @IF "%SQLCMDPASSWORD%"     ==  ""    @SET SQLCMDPASSWORD=12345  
18. rem @IF "%SQLCMDDBNAME%"       ==  ""    @SET /P SQLCMDDBNAME=.   请输入资料库名 称 [TR]：  
19. @IF "%SQLCMDDBNAME%"       ==  ""    @SET SQLCMDDBNAME=TR  
20. rem @IF "%SQLCMDDATADIR%"      ==  ""    @SET /P SQLCMDDATADIR=.   请输入资料库备份资料 夹 [C:/MSSQL_BAK]：  
21. @IF "%SQLCMDDATADIR%"      ==  ""    @SET SQLCMDDATADIR=G:/MSSQL_BAK  
22. rem @IF "%SQLCMDBAKFILE%"      ==  ""    @SET /P SQLCMDBAKFILE=.   请输入资料库备份档案名 称 [%SQLCMDDBNAME%_%TodayNow%.bak]：  
23. @IF "%SQLCMDBAKFILE%"      ==  ""    @SET SQLCMDBAKFILE=%SQLCMDDBNAME%_%TodayNow%.bak  
24. @IF "%SQLCMDWORKSTATION%"  ==  ""    @SET SQLCMDWORKSTATION=%COMPUTERNAME%  
25. @IF "%SQLCMDLOGFILE%"      ==  ""    @SET SQLCMDLOGFILE= "%Temp%/%~nx0%.log"   
26. @SET __backFile="%TEMP%/%~nx0%.sql"   
27. @IF     EXIST %__backFile%       @DEL /Q /F %__backFile%  
28. @IF NOT EXIST %SQLCMDDATADIR%    @MKDIR    "%SQLCMDDATADIR%"   
29. REM     下面将备份数据库命令写入文件中，  
30. REM     然 后调用SQLCMD.EXE执行SQL命令  
31. REM     来完成数据库备份 作业  
32. @ECHO USE MASTER                                                     >>   %__backFile%  
33. @ECHO GO                                                             >>   %__backFile%  
34. @ECHO   BACKUP DATABASE %SQLCMDDBNAME% TO                            >>   %__backFile%  
35. @ECHO          DISK = N'%SQLCMDDATADIR%/%SQLCMDBAKFILE%'              >>   %__backFile%  
36. @ECHO          WITH NOFORMAT , INIT,                                 >>   %__backFile%  
37. @ECHO          NAME = N'Full Backup of %SQLCMDDBNAME%'                >>   %__backFile%  
38. @ECHO   GO                                                           >>   %__backFile%  
39. @ECHO .  
40. REM SQLCMD.EXE -r -f 65001 -i %__backFile%                              -o %SQLCMDLOGFILE%  
41. SQLCMD.EXE -r -f 65001 -i %__backFile%      
42. REM NOTEPAD.EXE %__backFile%  
43. @DEL   /Q  /F  %__backFile%  
44. @ECHO .  
45. @ECHO . --- %~nx0% --- 资料库备份作业 结束  
46. REM NOTEPAD.EXE %SQLCMDLOGFILE%  
47. @pause   

@ECHO OFF @SETLOCAL @CLS @ECHO . @ECHO . --- %~nx0% - 资料库完整备份作业 开始 --- @ECHO . --- %~dp0% @ECHO . --- %CD% @ECHO . 数据库完整备份方案 @ECHO . @ECHO . @ECHO . rem @IF "%SQLCMDSERVER%" == "" @SET /P SQLCMDSERVER=. 请输入资料库伺服器名称 [127.0.0.1]： @IF "%SQLCMDSERVER%" == "" @SET SQLCMDSERVER=127.0.0.1 rem @IF "%SQLCMDUSER%" == "" @SET /P SQLCMDUSER=. 请输入资料库使用者名称 [sa]： @IF "%SQLCMDUSER%" == "" @SET SQLCMDUSER=sa rem @IF "%SQLCMDPASSWORD%" == "" @SET /P SQLCMDPASSWORD=. 请输入资料库验证密码 [12345]： @IF "%SQLCMDPASSWORD%" == "" @SET SQLCMDPASSWORD=12345 rem @IF "%SQLCMDDBNAME%" == "" @SET /P SQLCMDDBNAME=. 请输入资料库名称 [TR]： @IF "%SQLCMDDBNAME%" == "" @SET SQLCMDDBNAME=TR rem @IF "%SQLCMDDATADIR%" == "" @SET /P SQLCMDDATADIR=. 请输入资料库备份资料夹 [C:/MSSQL_BAK]： @IF "%SQLCMDDATADIR%" == "" @SET SQLCMDDATADIR=G:/MSSQL_BAK rem @IF "%SQLCMDBAKFILE%" == "" @SET /P SQLCMDBAKFILE=. 请输入资料库备份档案名称 [%SQLCMDDBNAME%_%TodayNow%.bak]： @IF "%SQLCMDBAKFILE%" == "" @SET SQLCMDBAKFILE=%SQLCMDDBNAME%_%TodayNow%.bak @IF "%SQLCMDWORKSTATION%" == "" @SET SQLCMDWORKSTATION=%COMPUTERNAME% @IF "%SQLCMDLOGFILE%" == "" @SET SQLCMDLOGFILE="%Temp%/%~nx0%.log" @SET __backFile="%TEMP%/%~nx0%.sql" @IF EXIST %__backFile% @DEL /Q /F %__backFile% @IF NOT EXIST %SQLCMDDATADIR% @MKDIR "%SQLCMDDATADIR%" REM 下面将备份数据库命令写入文件中， REM 然后调用SQLCMD.EXE执行SQL命令 REM 来完成数据库备份作业 @ECHO USE MASTER >> %__backFile% @ECHO GO >> %__backFile% @ECHO BACKUP DATABASE %SQLCMDDBNAME% TO >> %__backFile% @ECHO DISK = N'%SQLCMDDATADIR%/%SQLCMDBAKFILE%' >> %__backFile% @ECHO WITH NOFORMAT , INIT, >> %__backFile% @ECHO NAME = N'Full Backup of %SQLCMDDBNAME%' >> %__backFile% @ECHO GO >> %__backFile% @ECHO . REM SQLCMD.EXE -r -f 65001 -i %__backFile% -o %SQLCMDLOGFILE% SQLCMD.EXE -r -f 65001 -i %__backFile% REM NOTEPAD.EXE %__backFile% @DEL /Q /F %__backFile% @ECHO . @ECHO . --- %~nx0% --- 资料库备份作业 结束 REM NOTEPAD.EXE %SQLCMDLOGFILE% @pause 

2.差异备份

1. @ECHO OFF  
2. @SETLOCAL  
3. @CLS  
4. @ECHO .  
5. @ECHO . --- %~nx0% - 资 料库差异备份作业 开始 ---  
6. @ECHO .  
7. @ECHO .       数据库差异备份方案  
8. @ECHO .     
9. @ECHO .  
10. @ECHO .  
11. rem @IF "%SQLCMDSERVER%"       ==  ""    @SET /P SQLCMDSERVER=.   请输入资料库伺服器名 称 [127.0.0.1]：  
12. @IF "%SQLCMDSERVER%"       ==  ""    @SET SQLCMDSERVER=127.0.0.1  
13. rem @IF "%SQLCMDUSER%"         ==  ""    @SET /P SQLCMDUSER=.   请输入资料库使用者名 称 [sa]：  
14. @IF "%SQLCMDUSER%"         ==  ""    @SET SQLCMDUSER=sa  
15. rem @IF "%SQLCMDPASSWORD%"     ==  ""    @SET /P SQLCMDPASSWORD=.   请输入资料库验证密 码 [12345]：  
16. @IF "%SQLCMDPASSWORD%"     ==  ""    @SET SQLCMDPASSWORD=12345  
17. rem @IF "%SQLCMDDBNAME%"       ==  ""    @SET /P SQLCMDDBNAME=.   请输入资料库名 称 [TR]：  
18. @IF "%SQLCMDDBNAME%"       ==  ""    @SET SQLCMDDBNAME=TR  
19. rem @IF "%SQLCMDDATADIR%"      ==  ""    @SET /P SQLCMDDATADIR=.   请输入资料库备份资料 夹 [C:/MSSQL_BAK]：  
20. @IF "%SQLCMDDATADIR%"      ==  ""    @SET SQLCMDDATADIR=G:/MSSQL_BAK  
21. rem @IF "%SQLCMDBAKFILE%"      ==  ""    @SET /P SQLCMDBAKFILE=.   请输入资料库备份档案名 称 [%SQLCMDDBNAME%_%TodayNow%.bak]：  
22. @IF "%SQLCMDBAKFILE%"      ==  ""    @SET SQLCMDBAKFILE=%SQLCMDDBNAME%_%TodayNow%.bak  
23. @IF "%SQLCMDWORKSTATION%"  ==  ""    @SET SQLCMDWORKSTATION=%COMPUTERNAME%  
24. @IF "%SQLCMDLOGFILE%"      ==  ""    @SET SQLCMDLOGFILE= "%Temp%/%~nx0%.log"   
25. @SET __backFile="%TEMP%/%~nx0%.sql"   
26. @IF     EXIST %__backFile%       @DEL /Q /F %__backFile%  
27. @IF NOT EXIST %SQLCMDDATADIR%    @MKDIR    "%SQLCMDDATADIR%"   
28. REM     下面将备份数据库命令写入文件中，  
29. REM     然 后调用SQLCMD.EXE执行SQL命令  
30. REM     来完成数据库备份作 业  
31. @ECHO USE MASTER                                                     >>   %__backFile%  
32. @ECHO GO                                                             >>   %__backFile%  
33. @ECHO   BACKUP DATABASE %SQLCMDDBNAME% TO                            >>   %__backFile%  
34. @ECHO          DISK = N'%SQLCMDDATADIR%/%SQLCMDBAKFILE%'              >>   %__backFile%  
35. @ECHO          WITH DIFFERENTIAL ,                                   >>   %__backFile%  
36. @ECHO          NAME = N'Diff Backup of %SQLCMDDBNAME%'                >>   %__backFile%  
37. @ECHO   GO                                                           >>   %__backFile%  
38. @ECHO .  
39. REM SQLCMD.EXE -r -f 65001 -i %__backFile%                              -o %SQLCMDLOGFILE%  
40. SQLCMD.EXE -r -f 65001 -i %__backFile%      
41. REM NOTEPAD.EXE %__backFile%  
42. @DEL   /Q  /F  %__backFile%  
43. @ECHO .  
44. @ECHO . --- %~nx0% --- 资料库备份作业 结束  
45. REM NOTEPAD.EXE %SQLCMDLOGFILE%  
46. @pause  

@ECHO OFF @SETLOCAL @CLS @ECHO . @ECHO . --- %~nx0% - 资料库差异备份作业 开始 --- @ECHO . @ECHO . 数据库差异备份方案 @ECHO . @ECHO . @ECHO . rem @IF "%SQLCMDSERVER%" == "" @SET /P SQLCMDSERVER=. 请输入资料库伺服器名称 [127.0.0.1]： @IF "%SQLCMDSERVER%" == "" @SET SQLCMDSERVER=127.0.0.1 rem @IF "%SQLCMDUSER%" == "" @SET /P SQLCMDUSER=. 请输入资料库使用者名称 [sa]： @IF "%SQLCMDUSER%" == "" @SET SQLCMDUSER=sa rem @IF "%SQLCMDPASSWORD%" == "" @SET /P SQLCMDPASSWORD=. 请输入资料库验证密码 [12345]： @IF "%SQLCMDPASSWORD%" == "" @SET SQLCMDPASSWORD=12345 rem @IF "%SQLCMDDBNAME%" == "" @SET /P SQLCMDDBNAME=. 请输入资料库名称 [TR]： @IF "%SQLCMDDBNAME%" == "" @SET SQLCMDDBNAME=TR rem @IF "%SQLCMDDATADIR%" == "" @SET /P SQLCMDDATADIR=. 请输入资料库备份资料夹 [C:/MSSQL_BAK]： @IF "%SQLCMDDATADIR%" == "" @SET SQLCMDDATADIR=G:/MSSQL_BAK rem @IF "%SQLCMDBAKFILE%" == "" @SET /P SQLCMDBAKFILE=. 请输入资料库备份档案名称 [%SQLCMDDBNAME%_%TodayNow%.bak]： @IF "%SQLCMDBAKFILE%" == "" @SET SQLCMDBAKFILE=%SQLCMDDBNAME%_%TodayNow%.bak @IF "%SQLCMDWORKSTATION%" == "" @SET SQLCMDWORKSTATION=%COMPUTERNAME% @IF "%SQLCMDLOGFILE%" == "" @SET SQLCMDLOGFILE="%Temp%/%~nx0%.log" @SET __backFile="%TEMP%/%~nx0%.sql" @IF EXIST %__backFile% @DEL /Q /F %__backFile% @IF NOT EXIST %SQLCMDDATADIR% @MKDIR "%SQLCMDDATADIR%" REM 下面将备份数据库命令写入文件中， REM 然后调用SQLCMD.EXE执行SQL命令 REM 来完成数据库备份作业 @ECHO USE MASTER >> %__backFile% @ECHO GO >> %__backFile% @ECHO BACKUP DATABASE %SQLCMDDBNAME% TO >> %__backFile% @ECHO DISK = N'%SQLCMDDATADIR%/%SQLCMDBAKFILE%' >> %__backFile% @ECHO WITH DIFFERENTIAL , >> %__backFile% @ECHO NAME = N'Diff Backup of %SQLCMDDBNAME%' >> %__backFile% @ECHO GO >> %__backFile% @ECHO . REM SQLCMD.EXE -r -f 65001 -i %__backFile% -o %SQLCMDLOGFILE% SQLCMD.EXE -r -f 65001 -i %__backFile% REM NOTEPAD.EXE %__backFile% @DEL /Q /F %__backFile% @ECHO . @ECHO . --- %~nx0% --- 资料库备份作业 结束 REM NOTEPAD.EXE %SQLCMDLOGFILE% @pause

3.还原数据库

1. @ECHO OFF  
2. @SETLOCAL  
3. @CLS  
4. @ECHO .  
5. @ECHO . --- %~nx0% - 资 料库还原作业 开始 ---  
6. @ECHO .  
7. @ECHO .       数据库还原方案  
8. @ECHO .     
9. @ECHO .  
10. @ECHO .  
11. @IF "%SQLCMDSERVER%"       ==  ""    @SET /P SQLCMDSERVER=.   请输入服务器 [127.0.0.1]：  
12. @IF "%SQLCMDSERVER%"       ==  ""    @SET SQLCMDSERVER=127.0.0.1  
13. @ECHO .  
14. @IF "%SQLCMDUSER%"         ==  ""    @SET /P SQLCMDUSER=.   请输入用户名[sa]：  
15. @IF "%SQLCMDUSER%"         ==  ""    @SET SQLCMDUSER=sa  
16. @ECHO .  
17. @IF "%SQLCMDPASSWORD%"     ==  ""    @SET /P SQLCMDPASSWORD=.   请输入服务器密码 [12345]：  
18. @IF "%SQLCMDPASSWORD%"     ==  ""    @SET SQLCMDPASSWORD=12345  
19. @ECHO .  
20. @IF "%SQLCMDDBNAME%"       ==  ""    @SET /P SQLCMDDBNAME=.   请输入资料库名 称 [TR]：  
21. @IF "%SQLCMDDBNAME%"       ==  ""    @SET SQLCMDDBNAME=TR  
22. @ECHO .  
23. rem @IF "%SQLCMDDATADIR%"      ==  ""    @SET /P SQLCMDDATADIR=.   请输入资料库备份资料 夹 [C:/MSSQL_BAK]：  
24. @IF "%SQLCMDSOURCEFILE%"   ==  ""    @SET /P SQLCMDSOURCEFILE=.   请输入还原的数据库文 件路径:   
25. @IF "%SQLCMDSOURCEFILE%"   ==  ""    @SET SQLCMDSOURCEFILE=G:/MSSQL_BAK/2009.BAK  
26. @IF "%SQLCMDWORKSTATION%"  ==  ""    @SET SQLCMDWORKSTATION=%COMPUTERNAME%  
27. @IF "%SQLCMDLOGFILE%"      ==  ""    @SET SQLCMDLOGFILE= "%Temp%/%~nx0%.log"   
28. @IF "%SQLCMDSOURCEFILE%"  ==  ""  GOTO AAAA   
29. @ECHO .  
30. @ECHO .  
31. @SET /P Answer=.           您真的要还原数据库吗[N]?  
32. @IF "%Answer%"  ==  ""     GOTO AAAA  
33. @IF "%Answer%"  ==  "n"    GOTO AAAA  
34. @IF "%Answer%"  ==  "N"    GOTO AAAA  
35. @IF "%Answer%"  ==  "Y"    GOTO INIT  
36. @IF "%Answer%"  ==  "y"    GOTO INIT  
37. rem @IF ERRORLEVEL number @NET START MSSSQLSERVER ELSE GOTO END  
38. :INIT  
39. @SET __backFile="%TEMP%/%~nx0%.sql"   
40. @IF EXIST %__backFile%  @DEL /Q /F %__backFile%  
41. @ECHO USE master                                                         >> %__backFile%  
42. @ECHO GO                                                                 >> %__backFile%  
43. @ECHO RESTORE DATABASE %SQLCMDDBNAME%                                    >> %__backFile%  
44. @ECHO   FROM  DISK = N'%SQLCMDSOURCEFILE%'                                >> %__backFile%  
45. @ECHO       WITH                                                         >> %__backFile%  
46. @ECHO       FILE = 1,                                                    >> %__backFile%    
47. @ECHO       NOUNLOAD,                                                    >> %__backFile%  
48. @ECHO       REPLACE,                                                     >> %__backFile%  
49. @ECHO       STATS = 10                                                   >> %__backFile%  
50. @ECHO  GO                                                                >> %__backFile%   
51. @PAUSE  
52. SQLCMD.EXE -r -f 65001 -i %__backFile%  
53. @DEL   /Q  /F  %__backFile%  
54. :AAAA  
55. @ECHO .  
56. @ECHO . --- %~nx0% --- 资 料库还原作业 结束  
57. @PAUSE