首页 > 数据库 >[学习笔记]SQL server完全备份指南

[学习笔记]SQL server完全备份指南

时间：2023-07-20 10:44:46浏览次数：53  
标签：opt 备份 数据库 SQL var server mssql

目录


本文将介绍如何在日常项目中，对SQL server数据库做备份和还原工作，SQL server的备份/还原机制，详情参见官方文档：备份和还原 Linux 上的 SQL Server 数据库

 

方式一，使用SQL Server Management Studio

准备工作

连接目标数据库服务器

在目标数据库上右键->属性，将数据库的恢复模式设置为简单，兼容级别设置为SQL Server 2016(130)
在这里插入图片描述

[可选]将表中将无用的业务数据删除，以减少备份或移动的文件大小，这一步根据业务需求而决定，以ABP系统为例，运行下列SQL命令将AbpAuditLogs表中的数据清除：

TRUNCATE TABLE [dbo].[AbpAuditLogs]

收缩数据库

在这里插入图片描述
这一步将减少数据库中数据文件和日志文件的大小，并允许数据库中有 10% 的可用空间。详情参见官方文档：收缩数据库
在这里插入图片描述

移动数据库

通过将源数据库服务器中的数据库文件的分离，拷贝文件，再将文件附加至目标数据库服务器中，实现数据库移动，详情参见官方文档：数据库分离和附加 (SQL Server)
数据库->任务->分离，打开数据库分离对话框

在这里插入图片描述
勾选删除连接
在这里插入图片描述
点击确定，等待数据库分离完成

拷贝数据库文件到目标服务器的/var/opt/mssql/data目录下
使用SQL Server Management Studio连接目标服务器数据库，数据库->附加

点击“添加..”，选择/var/opt/mssql/data目录下的.mdf文件，点击确定，等待数据库附加完成

数据库备份

数据库->任务->备份，打开备份对话框
在这里插入图片描述
制定备份策略
策略是每周一次的全量备份，每天一次的增量备份， 全量备份的文件需要单独拷贝到其他地方做异地备份。
每次全量备份，将单独生成独立的.bak文件，命名以<数据库名称>-full-<日期编号>.bak为标准
如 BlogDb-full-0216.bak
增量备份时，备份类型选择“差异”，详情参见官方文档：差异备份 (SQL Server)
指定备份目标到“磁盘”，并添加一个路径，这里以/var/opt/mssql/backup目录为例

在这里插入图片描述
点击确定开始备份
等待备份完成，宿主机的备份目录下，可以看到.bak文件，将这些文件拷贝至其他服务器上以实现异地备份
在这里插入图片描述

还原数据库

数据库中右键，选择还原数据库
“源”中选择设备，并指定备份介质为目标.bak文件
在这里插入图片描述

选择后可以查看最新的备份集，如果备份集包含多个差异备份，可以通过时间线功能，查看并选择所需要的备份集位置
在这里插入图片描述
点选需要还原的备份集
在这里插入图片描述
点击确定开始还原

方式二，使用命令行工具

在客户机上往往不提供Windows环境，因此需要使用终端工具通过命令行完成操作

准备工作

sqlcmd 实用工具是一个命令行实用工具，用于执行 Transact-SQL 语句，详情请参考官方文档
sqlcmd - 使用实用工具，Transact-SQL 语句
安装mssql-tools工具集，其中包含了我们要用的sqlcmd 实用工具:

curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/msprod.repo
sudo yum install -y mssql-tools unixODBC-devel
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

​确认数据库容器映射的宿主机物理地址，若使用docker方式部署SQL server，且没有做目录映射，则按照下面的方式操作

首先将docker容器中的 /var/opt/mssql目录内容拷贝到宿主机中的相同目录下

docker cp sqlserver2017:/var/opt/mssql/ /var/opt/mssql

停止原始容器

docker stop sqlserver2017

新建容器，将/var/opt/mssql目录映射至宿主机中的目录中

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<你的密码>" -p 1433:1433 -v /var/opt/mssql:/var/opt/mssql --name sqlserver2017 --restart always -d mcr.microsoft.com/mssql/server:2017-latest

或者如果按照官方文档中的卷操作，输入

docker volume ls
docker volume inspect <卷名称>

查看对应sqlserver容器的卷所映射的物理路径
在这里插入图片描述
物理路径下的内容：
在这里插入图片描述
将数据库的恢复模式设置为“简单”，运行命令：

sqlcmd -S <SqlServer服务器地址> -U SA -P "<你的密码>" -Q "ALTER DATABASE [dbo].[<数据库名称>] SET RECOVERY SIMPLE"

将数据库的兼容级别设置为130，运行命令：

sqlcmd -S <SqlServer服务器地址> -U SA -P "<你的密码>" -Q "ALTER DATABASE  [<数据库名称>] SET COMPATIBILITY_LEVEL = 130;"

[可选]将表中将无用的业务数据删除，以减少备份或移动的文件大小，这一步根据业务需求而决定，以ABP系统为例，运行下列SQL命令将AbpAuditLogs表中的数据清除：

sqlcmd -S <SqlServer服务器地址> -U SA -P "<你的密码>" -Q "TRUNCATE TABLE [dbo].[AbpAuditLogs]"

收缩数据库

运行命令

sqlcmd -S <SqlServer服务器地址> -U SA -P "<你的密码>" -Q "DBCC SHRINKDATABASE (<数据库名称>, 10)"

这一步将减少数据库中数据文件和日志文件的大小，并允许数据库中有 10% 的可用空间。
在这里插入图片描述

移动数据库

首先将数据库设置下线

sqlcmd -S <SqlServer服务器地址> -U SA -P "<你的密码>" -Q "ALTER DATABASE [<数据库名称>] SET OFFLINE WITH ROLLBACK IMMEDIATE"

分离数据库

sqlcmd -S <SqlServer服务器地址> -U SA -P "<你的密码>" -Q "EXEC sp_detach_db '<数据库名称>', 'true'"

拷贝数据库文件到目标服务器的/var/opt/mssql/data目录下

scp -r -P 22 root@<SqlServer服务器地址>:/var/opt/mssql/data/<数据库名称>.mdf /var/opt/mssql/data
scp -r -P 22 root@<SqlServer服务器地址>:/var/opt/mssql/data/<数据库名称>_log.ldf /var/opt/mssql/data

在目标服务器中附加这个数据库

sqlcmd -S <SqlServer服务器地址> -U SA -P "<你的密码>" -Q "CREATE DATABASE [<数据库名称>] ON (FILENAME = '/var/opt/mssql/data/<数据库名称>.mdf'),(FILENAME = '/var/opt/mssql/data/<数据库名称>_log.ldf') FOR ATTACH"

备份数据库

运行命令

sqlcmd -S <SqlServer服务器地址> -U SA -P "<你的密码>" -Q "BACKUP DATABASE [<数据库名称>] TO DISK = N'/var/opt/mssql/backup/<数据库名称>-full.bak' WITH NOFORMAT, NOINIT, NAME = '<数据库名称>-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

备份应该只包含上次完整备份后更改的数据库或文件部分。 差异备份一般会比完整备份占用更少的空间
如果是差异化备份，请添加如下参数

 WITH DIFFERENTIAL

等待数据库备份完成
在这里插入图片描述

[可选]将备份文件.bak文件拷贝至目标服务器中，实现异地备份

mkdir /var/opt/mssql/backup
scp -r -P 22 root@<SqlServer服务器地址>:/var/opt/mssql/backup/<数据库名称>-full.bak /var/opt/mssql/backup

还原数据库

运行命令

sqlcmd -S <SqlServer服务器地址> -U SA -P "<你的密码>" -Q "RESTORE DATABASE [<数据库名称>] FROM DISK = N'/var/opt/mssql/backup/<数据库名称>-full.bak' WITH NOUNLOAD, REPLACE, NORECOVERY, STATS = 5"

如果数据库备份文件还包含备差异备份，则还需要选择还原的差异备份集位置，

WITH FILE = 1

比如要还原的完整数据库备份是设备上的第六个备份集 (FILE = 6)，差异数据库备份是设备上的第九个备份集 (FILE = 9)。 在恢复了差异备份之后，便恢复了数据库。

在这里插入图片描述

 

 

出处：https://www.cnblogs.com/jevonsflash/p/17141767.html

标签：opt,备份,数据库,SQL,var,server,mssql
From： https://www.cnblogs.com/mq0036/p/17567690.html

相关文章

  • Calcite解析Sql中的表名
    目的是为了解析视图中的表名，构建血缘关系，下面是入门的demoCalcite解析有不能解析出来的情况，需要进一步了解javacc，Jsqparser解析，都能正常解析，然而面对复杂子查询，效率很低使用Calcite解析packagesqlparser;importorg.apache.calcite.avatica.util.Casing;importorg.apac......
  • 2-12 MySQL字段约束-索引-外键
    高版本导出报错问题，是由于高版本对导出文件优化了权限设置，showvariableslike'%secure%';查看权限是NULL就代表禁止导出在配置文件my.cnf[mysqld]下加secure_file_priv=指定导出目录  本节所讲内容：1.  字段修饰符2.  清空表记录3.  索引4.  外键视图......
  • PostgreSQL explain使用
    1.概述PostgreSQL为每个收到的查询产生一个执行计划，这个执行计划是一个非完全的二叉树。通过这个执行计划，DBA或者应用人员可以清晰的了解到某个SQL在数据库中的预估的执行情况以及实际的执行情况，也能根据执行计划中资源的消耗判断性能的瓶颈点，从而对该SQL进行有针对性的优化。下......
  • 2-10-Mysql基本语句和常见数据类型
    1  SQL概述结构化查询语言(StructuredQueryLanguage)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。从上可以看出我们数据库相关工作职位大概两种：DBD和DBAdba是数据库管......
  • MySql基础学习
     一、基础学习1、打开MySql？2、如何创建数据库、数据表？ 3、bug 0、注意事项mysql是不区分大小写的 1、启动MySqlcmd命令行，管理员模式运行，输入命令启动服务-----netstartmysql输入ml(命令)----------mqsql-uroot-p出现如下界面就登录成功了 2、基础sql命......
  • 在 Amazon 上以高可用性模式实现 Microsoft SQL 数据库服务现代化的注意事项
    许多企业都有需要MicrosoftSQLServer来运行关系数据库工作负载的应用程序：一些应用程序可能是专有软件，供应商可使用它强制MicrosoftSQLServer运行数据库服务；其他应用程序可能是长期存在的、自主开发的应用程序，它们在最初开发时便已包含MicrosoftSQLServer。当企业将应用......
  • postgresql-备份恢复
    1、逻辑备份恢复pg_dump/pg_restore命令备份恢复对数据库或表备份恢复##备份指定的数据库test$pg_dump-Upostgres-W-h192.168.3.122-p1921test>/pgdata/dumpbak/test.sql##备份指定库中的某个表t1$pg_dump-Upostgres-W-h192.168.3.122-p1921test......
  • 使用Canal同步mysql数据到es
    一、简介Canal主要用途是基于MySQL数据库增量日志解析，提供增量数据订阅和消费。当前的canal支持源端MySQL版本包括5.1.x,5.5.x,5.6.x,5.7.x,8.0.x二、工作原理MySQL主备复制原理MySQLmaster将数据变更写入二进制日志(binarylog,其中记录叫做二进制日志事件bin......
  • 使用Canal同步mysql数据到es
    一、简介Canal主要用途是基于MySQL数据库增量日志解析，提供增量数据订阅和消费。当前的canal支持源端MySQL版本包括5.1.x,5.5.x,5.6.x,5.7.x,8.0.x二、工作原理MySQL主备复制原理MySQLmaster将数据变更写入二进制日志(binarylog,其中记录叫做二进制日志......
  • 错误连接数据库 [mysql] : org.pentaho.di.core.exception.KettleDatabaseExcepti
    错误连接数据库[mysql]:org.pentaho.di.core.exception.KettleDatabaseException是一种常见的错误，通常在使用PentahoDataIntegration（PDI）工具连接到MySQL数据库时出现。本文将介绍这个错误的原因，以及如何解决它。在使用PDI工具连接到MySQL数据库时，经常会遇到数据库连接失败的......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99