DOS工具：每次取出最新的数据，备份后再复制到文件服务器指定目录的工具

快来打我* 2022-09-28 15:22 98阅读 0赞

@echo off  
rem -- =============================================  
rem -- DESCRIPTION: <自動\_導出バッチ>  
rem -- AUTHOR:  <  
rem -- CREATE DATE: <2010/12/27>  
rem -- =============================================

rem -------------------------------------------------------------  
rem         ファイルサーバ\_\_username  
rem -------------------------------------------------------------  
@SET file\_server\_username=xxx

rem -------------------------------------------------------------  
rem         ファイルサーバ\_\_password  
rem -------------------------------------------------------------  
@SET file\_server\_password=xxx

rem --------------------------------------------------------------  
rem         ファイルサーバ\_dir(copy to)  
rem --------------------------------------------------------------  
@SET file\_server\_dir=//192.168.0.218/aaa

rem --------------------------------------------------------------  
rem         コピー元タイプ\_(default: txt)  
rem --------------------------------------------------------------  
@SET file\_type1=.txt  
@SET file\_type2=.dat

rem -------------------------------------------------------------  
rem         データベース\_\_name  
rem -------------------------------------------------------------  
@SET database\_name=\[project\_guest\].\[dbo\]

rem -------------------------------------------------------------  
rem         テーブル\_\_name  
rem -------------------------------------------------------------  
@SET table\_name=.\[m\_partslist\]

rem -------------------------------------------------------------  
rem         SQL  
rem -------------------------------------------------------------  
@SET select\_query\_upd="select max(upd\_date) from \[project\_guest\].\[dbo\].\[m\_partslist\]"  
@SET select\_query="select replace(replace(replace(CONVERT(nvarchar,GETDATE(),120),'-',''),':',''),' ','')"  
rem -------------------------------------------------------------  
rem         仮ファイル  
rem -------------------------------------------------------------  
@SET tmp\_file\_1=now\_time.dat  
@SET tmp\_file\_2=old.dat

rem -------------------------------------------------------------  
rem         前回更新日付ファイル名  
rem -------------------------------------------------------------  
@SET upd\_date\_file=upd\_date.dat

rem -------------------------------------------------------------  
rem         CONFIGファイル名  
rem -------------------------------------------------------------  
@SET config\_file\_name=CONFIG.TXT

IF EXIST %database\_name%%table\_name%%file\_type1% (REN %database\_name%%table\_name%%file\_type1% %tmp\_file\_2%)

@IF NOT EXIST %upd\_date\_file% GOTO now\_date

@FOR /f "delims=," %%x IN (%upd\_date\_file%) DO (  
@SET str\_upd\_date=%%~nx  
)  
@SET "select\_query\_1=select \* from \[project\_guest\].\[dbo\].\[m\_partslist\] where convert(datetime,upd\_date,121) > convert(datetime,'%str\_upd\_date%',121)"

@FOR /F "eol=\# tokens=1,2,3,4\* delims=," %%i IN (%config\_file\_name%) DO (  
(BCP "%select\_query\_1%" queryout %database\_name%%table\_name%%file\_type2% -S%%i -U%%j -P%%k -c)  
IF errorlevel 1 GOTO error  
)

@GOTO next\_1

@:now\_date

@SET str\_upd\_date=%date% %time%  
@SET "select\_query\_2=select \* from \[project\_guest\].\[dbo\].\[m\_partslist\] where convert(datetime,upd\_date,121) < convert(datetime,'%str\_upd\_date%',121)"  
@FOR /F "eol=\# tokens=1,2,3,4\* delims=," %%i IN (%config\_file\_name%) DO (  
(BCP "%select\_query\_2%" queryout %database\_name%%table\_name%%file\_type2% -S%%i -U%%j -P%%k -c)

IF errorlevel 1 GOTO error  
)  
@GOTO next\_1

@:next\_1  
@FOR /F "eol=\# tokens=1,2,3,4\* delims=," %%i IN (%config\_file\_name%) DO (  
(BCP %select\_query\_upd% queryout %upd\_date\_file% -S%%i -U%%j -P%%k -c)

IF errorlevel 1 GOTO error  
)  
@GOTO success

@:success  
@IF EXIST %tmp\_file\_2% (COPY %tmp\_file\_2% + %database\_name%%table\_name%%file\_type2% %database\_name%%table\_name%%file\_type1% && DEL %tmp\_file\_2% && DEL %database\_name%%table\_name%%file\_type2%)  
@REN %database\_name%%table\_name%%file\_type2% %database\_name%%table\_name%%file\_type1%

rem -------------------------------------------------------------  
rem         システム日付を取得  
rem -------------------------------------------------------------  
@FOR /F "eol=\# tokens=1,2,3\* delims=, " %%i IN (%config\_file\_name%) DO (  
BCP %select\_query% queryout %tmp\_file\_1% -S%%i -U%%j -P%%k -c  
IF errorlevel 1 GOTO error  
 @FOR /f "delims=," %%x IN (%tmp\_file\_1%) DO (  
 @set now\_time=%%~nx  
 )  
)  
@DEL %tmp\_file\_1%

@echo -- =============================================  
@echo  導出成功しました。  
@echo -- =============================================

@COPY %database\_name%%table\_name%%file\_type1% %database\_name%%table\_name%\_%now\_time%%file\_type1%

rem -------------------------------------------  
rem         ファイルサーバを接続  
rem -------------------------------------------  
@NET USE %file\_server\_dir% %file\_server\_password% /u:%file\_server\_username%  
IF errorlevel 1 GOTO error

rem -------------------------------------------  
rem         ファイルサーバへコピー  
rem -------------------------------------------  
@XCOPY %database\_name%%table\_name%\_%now\_time%%file\_type1% %file\_server\_dir% /Y

IF errorlevel 1 GOTO error  
@echo -- ===========================================================  
@echo   %file\_server\_dir%へコピーしました。  
@echo   コピー完了しました。  
@echo -- ===========================================================

@GOTO end  
@:error  
@echo -- =============================================  
@echo  導出失敗しました。  
@echo -- =============================================  
@GOTO end  
@:end  
rem -------------------------------------------  
rem         コピー元を削除  
rem -------------------------------------------  
@IF EXIST %database\_name%%table\_name%\_%now\_time%%file\_type1% (DEL %database\_name%%table\_name%\_%now\_time%%file\_type1% /Q/F)

\----------------------------------------

config.txt

192.168.0.218,sa,password