[SQL Server] MSSQL CLI Backup

 

[SQL Server] MSSQL CLI Backup

 

 

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-backup-and-restore-database?view=sql-server-ver15

Backup and restore SQL Server databases on Linux - SQL Server

 

Linux backup sh 파일 설정

dateTimeString=$(date '+%Y-%m-%d_%H%M%S')
backupPath=/data/mssql/backup
if ! [ -d $backupPath ]
then
    mkdir -p "${backupPath}"
fi
dbName=
dbUser=
dbPassword=
backFile=${dbName}_${dateTimeString}.bak

/opt/mssql-tools18/bin/sqlcmd -S localhost -C -U ${dbUser} -P ${dbPassword} -Q "BACKUP DATABASE [${dbName}] TO DISK = N'${backupPath}/${backFile}' WITH NOFORMAT, NOINIT, NAME = '${dbName}-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

 

Windows backup bat 파일 설정

set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%

::Korean
set year=%date:~0,4%
set month=%date:~5,2%
if "%month:~0,1%" == " " set month=0%month:~1,1%
set day=%date:~8,2%
if "%day:~0,1%" == " " set day=0%day:~1,1%

::English
::set year=%date:~-4%
::set month=%date:~4,2%
::if "%month:~0,1%" == " " set month=0%month:~1,1%
::set day=%date:~7,2%
::if "%day:~0,1%" == " " set day=0%day:~1,1%

set dbName=
set datetimstr=%year%%month%%day%_%hour%%min%%secs%
set backupdir=D:\Database\Backup
set filename=Backup_%dbName%_%datetimstr%.bak
set filepath=%backupdir%\%filename%
if not exist %backupdir% mkdir %backupdir%
sqlcmd -E -S "127.0.0.1" -Q "backup database %dbName% to disk='%filepath%'"