数据库打包批处理

来源：互联网发布：windows server 97 编辑：程序博客网时间：2024/05/21 21:34

1.完整备份数据库

@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

@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.1rem @IF "%SQLCMDUSER%" == "" @SET /P SQLCMDUSER=. 请输入资料库使用者名称 [sa]：@IF "%SQLCMDUSER%" == "" @SET SQLCMDUSER=sarem @IF "%SQLCMDPASSWORD%" == "" @SET /P SQLCMDPASSWORD=. 请输入资料库验证密码 [12345]：@IF "%SQLCMDPASSWORD%" == "" @SET SQLCMDPASSWORD=12345rem @IF "%SQLCMDDBNAME%" == "" @SET /P SQLCMDDBNAME=. 请输入资料库名称 [TR]：@IF "%SQLCMDDBNAME%" == "" @SET SQLCMDDBNAME=TRrem @IF "%SQLCMDDATADIR%" == "" @SET /P SQLCMDDATADIR=. 请输入资料库备份资料夹 [C:/MSSQL_BAK]：@IF "%SQLCMDDATADIR%" == "" @SET SQLCMDDATADIR=G:/MSSQL_BAKrem @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.差异备份

@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

@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.1rem @IF "%SQLCMDUSER%" == "" @SET /P SQLCMDUSER=. 请输入资料库使用者名称 [sa]：@IF "%SQLCMDUSER%" == "" @SET SQLCMDUSER=sarem @IF "%SQLCMDPASSWORD%" == "" @SET /P SQLCMDPASSWORD=. 请输入资料库验证密码 [12345]：@IF "%SQLCMDPASSWORD%" == "" @SET SQLCMDPASSWORD=12345rem @IF "%SQLCMDDBNAME%" == "" @SET /P SQLCMDDBNAME=. 请输入资料库名称 [TR]：@IF "%SQLCMDDBNAME%" == "" @SET SQLCMDDBNAME=TRrem @IF "%SQLCMDDATADIR%" == "" @SET /P SQLCMDDATADIR=. 请输入资料库备份资料夹 [C:/MSSQL_BAK]：@IF "%SQLCMDDATADIR%" == "" @SET SQLCMDDATADIR=G:/MSSQL_BAKrem @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.还原数据库

@ECHO OFF
@SETLOCAL
@CLS
@ECHO .
@ECHO . --- %~nx0% - 资料库还原作业开始 ---
@ECHO .
@ECHO . 数据库还原方案
@ECHO .
@ECHO .
@ECHO .
@IF "%SQLCMDSERVER%" == "" @SET /P SQLCMDSERVER=. 请输入服务器[127.0.0.1]：
@IF "%SQLCMDSERVER%" == "" @SET SQLCMDSERVER=127.0.0.1
@ECHO .
@IF "%SQLCMDUSER%" == "" @SET /P SQLCMDUSER=. 请输入用户名[sa]：
@IF "%SQLCMDUSER%" == "" @SET SQLCMDUSER=sa
@ECHO .
@IF "%SQLCMDPASSWORD%" == "" @SET /P SQLCMDPASSWORD=. 请输入服务器密码[12345]：
@IF "%SQLCMDPASSWORD%" == "" @SET SQLCMDPASSWORD=12345
@ECHO .
@IF "%SQLCMDDBNAME%" == "" @SET /P SQLCMDDBNAME=. 请输入资料库名称 [TR]：
@IF "%SQLCMDDBNAME%" == "" @SET SQLCMDDBNAME=TR
@ECHO .
rem @IF "%SQLCMDDATADIR%" == "" @SET /P SQLCMDDATADIR=. 请输入资料库备份资料夹 [C:/MSSQL_BAK]：
@IF "%SQLCMDSOURCEFILE%" == "" @SET /P SQLCMDSOURCEFILE=. 请输入还原的数据库文件路径:
@IF "%SQLCMDSOURCEFILE%" == "" @SET SQLCMDSOURCEFILE=G:/MSSQL_BAK/2009.BAK
@IF "%SQLCMDWORKSTATION%" == "" @SET SQLCMDWORKSTATION=%COMPUTERNAME%
@IF "%SQLCMDLOGFILE%" == "" @SET SQLCMDLOGFILE="%Temp%/%~nx0%.log"
@IF "%SQLCMDSOURCEFILE%" == "" GOTO AAAA
@ECHO .
@ECHO .
@SET /P Answer=. 您真的要还原数据库吗[N]?
@IF "%Answer%" == "" GOTO AAAA
@IF "%Answer%" == "n" GOTO AAAA
@IF "%Answer%" == "N" GOTO AAAA
@IF "%Answer%" == "Y" GOTO INIT
@IF "%Answer%" == "y" GOTO INIT
rem @IF ERRORLEVEL number @NET START MSSSQLSERVER ELSE GOTO END
:INIT
@SET __backFile="%TEMP%/%~nx0%.sql"
@IF EXIST %__backFile% @DEL /Q /F %__backFile%
@ECHO USE master >> %__backFile%
@ECHO GO >> %__backFile%
@ECHO RESTORE DATABASE %SQLCMDDBNAME% >> %__backFile%
@ECHO FROM DISK = N'%SQLCMDSOURCEFILE%' >> %__backFile%
@ECHO WITH >> %__backFile%
@ECHO FILE = 1, >> %__backFile%
@ECHO NOUNLOAD, >> %__backFile%
@ECHO REPLACE, >> %__backFile%
@ECHO STATS = 10 >> %__backFile%
@ECHO GO >> %__backFile%
@PAUSE
SQLCMD.EXE -r -f 65001 -i %__backFile%
@DEL /Q /F %__backFile%
:AAAA
@ECHO .
@ECHO . --- %~nx0% --- 资料库还原作业结束
@PAUSE