526互联

SQLServer命令行备份数据库

发布时间 2024-01-01 13:52:06作者: 济南小老虎

SQLServer命令行备份数据库

背景

SQLServer on linux 想设置计划任务自动备份数据库

感觉sqlcmd应该是最简单快捷的方式了
所以进行一下处理.

安装必备软件

sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo

yum install mssql-tools -y

查看帮助信息

/opt/mssql-tools/bin/sqlcmd -?
usage: sqlcmd            [-U login id]          [-P password]
  [-S server or Dsn if -D is provided]
  [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-K application intent]
  [-M multisubnet failover]
  [-b On error batch abort]
  [-D Dsn flag, indicate -S is Dsn]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-g enable column encryption]
  [-G use Azure Active Directory for authentication]
  [-? show syntax summary]

登录数据库以及备份

/opt/mssql-tools/bin/sqlcmd -H127.0.0.1 -Usa -PTestxxxx  <<EOS
backup database mysqlapp2206mss0721 to disk = '/dbback/mysqlapp2206mss0721.bak' with compression
GO
EOS

备份效果为:
已为数据库 'mysqlapp2206mss0721'，文件 'mysqlapp2206mss0721' (位于文件 1 上)处理了 1156112 页。
已为数据库 'mysqlapp2206mss0721'，文件 'mysqlapp2206mss0721_log' (位于文件 1 上)处理了 1 页。
BACKUP DATABASE 成功处理了 1156113 页，花费 60.296 秒(149.796 MB/秒)。

备份呢文件大小为:
1.9G 1月   1 13:29 mysqlapp2206mss0721.bak

恢复数据库的命令

/opt/mssql-tools/bin/sqlcmd -H127.0.0.1 -Usa -PTestxxxx  <<EOS
RESTORE DATABASE [db1] FROM  DISK = N'/dbback/mysqlapp2206mss0721.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO
EOS