attach_db_v2.cmd

@echo off
rem ###################################
rem
rem 產生自動掛載 SQL
rem
rem ###################################
setlocal

rem 變數
set _DB_PATH_=d:\SQL_DATA
set _OUT_SQL_=attach_dbs.sql

:MAIN
cls
rem 檔頭
echo /*> %_OUT_SQL_%
echo. 自動掛載 SQL>> %_OUT_SQL_%
echo */>> %_OUT_SQL_%

rem 產出 Script
for %%A in (%_DB_PATH_%\*.mdf) do call :ATTACH %%A

rem 印出 Script
type %_OUT_SQL_%

endlocal
goto END

rem ===================================
rem ATTACH db_filename
rem
rem ===================================
:ATTACH
set _DB_NAME_=%~n1

rem 判斷是否為獨檔或分檔
set _DB_PREFIX_N_=%_DB_NAME_:~0,-1%
set _DB_SUFFIX_1_=%_DB_NAME_:~-1%
set _DB_DATAFILE_2_=%_DB_PATH_%\%_DB_PREFIX_N_%2.ndf
set _DB_FILEGROUP_=PRIMARY
if %_DB_SUFFIX_1_%==1 (
    if exist %_DB_DATAFILE_2_% (
        set _DB_NAME_=%_DB_PREFIX_N_%
        set _DB_FILEGROUP_=SECONDARY
    )
)
echo 處理資料庫 %_DB_NAME_% (%_DB_FILEGROUP_%) ...
goto _%_DB_FILEGROUP_%
goto END
rem -----------------------------------
:_PRIMARY
echo.>> %_OUT_SQL_%
echo -- %_DB_NAME_% (PRIMARY) -->> %_OUT_SQL_%
echo EXEC sp_attach_db @dbname = '%_DB_NAME_%'>> %_OUT_SQL_%
echo   ,@filename1 = '%~dpnx1'>> %_OUT_SQL_%
echo   ,@filename2 = '%~dpn1_log.ldf'>> %_OUT_SQL_%
goto END
rem -----------------------------------
:_SECONDARY
set _DB_NAME_=%_DB_PREFIX_N_%
set _DB_PREFIX_=%_DB_PATH_%\%_DB_NAME_%
echo.>> %_OUT_SQL_%
echo -- %_DB_NAME_% (SECONDARY) -->> %_OUT_SQL_%
echo EXEC sp_attach_db @dbname = '%_DB_NAME_%'>> %_OUT_SQL_%
echo   ,@filename1 = '%_DB_PREFIX_%1.mdf'>> %_OUT_SQL_%
for /L %%a in (2 1 16) do (
    if exist %_DB_PREFIX_%%%a.ndf (
        echo   ,@filename%%a = '%_DB_PREFIX_%%%a.ndf'>> %_OUT_SQL_%
    ) else (
        echo   ,@filename%%a = '%_DB_PREFIX_%_log.ldf'>> %_OUT_SQL_%
        goto END
    )
)

:END