www.129028.com金沙sqlserver备份的几种方式

日期:2020-05-07编辑作者:数据库

前言:任何的优化和修改都是以业务情况为前提,可能有的写的有误或者不准确的地方,欢迎各位来拍砖。

用sqlserver的维护计划

在这里我就不给截图演示了,这个比较简单,无非就是通过sqlserver自己的维护计划拖拽出2个一个‘备份数据库’任务和一个‘清除维护’任务。

www.129028.com金沙,需要注意的点

  1. 有备份任务里边选择备份的库尽量选‘所有用户数据库’这项,以免选择了特定数据库备份以后某天添加了新数据库却忘了勾选导致丢备份。
  2. 选项验证备份集完整性和压缩备份尽量都选上。
  3. 备份的路径尽量别选磁盘根目录。

1.在创建db的时候自增长建议设置成按MB(M)增长,步长根据业务量来设置,一般情况建议设置100-200M,还有就是尽量别改初始大小,这个默认就好。

通过脚本+作业的方式备份数据库(非xp_cmdshell)

DECLARE @filename VARCHAR(500)
DECLARE @date DATETIME
DECLARE @OLD_DATE DATETIME
SET @date=GETDATE()
SET @OLD_DATE=GETDATE()-5 --超过5天的备份即将被删除
SET @FILENAME = 'E:存放位置数据库名称-'+CAST(DATEPART(YYYY,@DATE) AS VARCHAR(10))+'-'+CAST(DATEPART(MM,@DATE) AS VARCHAR(10))+'-'+CAST(DATEPART(DD,@DATE) AS VARCHAR(10))+'.BAK'
#备份数据库
BACKUP DATABASE [数据库名称] TO DISK = @filename WITH COMPRESSION
#删除过期备份
EXECUTE master.dbo.xp_delete_file 0,N'E:存放位置',N'bak',@OLD_DATE,1

  GO

如果按照数据文件按照默认的1m增长,同时db的业务写入比较频繁,那数据库就会频繁的向磁盘请求空间,会造成不必要的io消耗,也会因为申请空间造成资源等待等问题。

通过脚本+作业的方式备份数据库(用xp_cmdshell)

ps:如果你觉得你们服务器的网络、代码、以及防火墙足够安全可以开启xp_cmdshell的方式来备份,否则慎用

  • 如果数据库没开启xp_cmdshell的功能需要用以下脚本开通此功能,开通以后记得关闭sp_configure。
USE Master
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell',1;
GO
RECONFIGURE WITH OVERRIDE;
GO
  • 下边是备份的脚本
DECLARE @DBNAME VARCHAR(128)
DECLARE @PATH VARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DDATE VARCHAR(8)

SET @PATH = 'E:BackUp'
SET @DDATE = convert(char(8),getdate(),112)

--删除超过1天的备份
SET @SQL ='xp_cmdshell '' forfiles /p "'+@path+'" /d -0 /m *.bak /c "cmd /c echo deleting @file.... && del /f @file"'''
EXEC (@SQL)

SET @SQL = ''
SELECT @SQL = @SQL + '
BACKUP DATABASE ['+NAME+'] TO DISK = '''+@PATH+''+REPLACE(name,'.','')+@DDATE+'.bak '''
FROM master..sysdatabases
WHERE NAME NOT IN ('master','tempdb','model','msdb')
EXEC (@SQL)

4.用powershell调用sqlcmd来执行备份命令。

在master库下边写好备份的存储过程pr_1

然后创建powershell脚本,将下边语句粘贴进去并保存成xx.ps1,在通过Windows的任务调度定时执行备份就可以了(我就是举个例子过期删除备份的语句也可以通过powershel实现,在家没有环境就随便写个思路没写全各位见谅)。

$dbname = 'test'
write-host "------"$dbname
& cmd /c
"sqlcmd -U sa -P 123456  -S 127.0.0.1 -Q `" pr_1 '$dbname'`" "

以上就是常见的几种备份的方法,大家可以根据自己的业务场景来选择适合自己的备份方式。

如果把日志文件按照默认的百分比10%增长,同样当db业务量很大或者有大事务的时候,日志会蹭蹭的增长,假如现在日志文件大小是100G,那每次增长步长就是10G,各位可以想一下如果数据库突然像磁盘申请10个G的空间,那当时的io情况会是什么样。

2.建议给系统预留一些内存。根据内存大小预留。因为sqlserver有一些操作是不消耗系统给sqlserver预留的内存的。比如你的服务器内存是64g,现在sqlserver进程占用50g,这时候如果你做备份操作的时候,这个操作消耗的不是sqlserver占用的50g,而是剩余的14g内存。还有一些其他的操作也会占用非sqlserver的内存,例如索引重建。假如现在sqlserver把64g内存都占满了,这时候你需要备份,那内存从哪来呢?只能让sqlserver释放缓存中的一些对象来给你提供备份的内存。如果现在业务繁忙可能就会因为内存不足或者内存命中引起性能问题了。

3.profiler是个好东西,谁用谁知道。

4.windows的性能监视器是监控sqlserver性能必备的工具。具体监控指标我会在其他blog里写。

5.如果你们机房的防火墙足够流弊,程序代码写的足够严谨,数据库没有公网ip,那你可以使用xp_cmdshell这个存储过程,否则请关闭这个config。

曾经有一台sqlserver服务器放在我面前我没有统一管理,后来我忘了服务器的登陆密码。于是我用sa口令登陆这台数据库,成功的通过xp_cmdshell添加了服务器管理员账号,成功的通过这个管理员账号登陆了服务器。本来这应该是一个皆大欢喜的故事,但是你有没有想过如果别人通过非法程序知道了你的数据库连接串,恰巧你又开启了xp_cmdshell呢,那么你就可以准备简历了。

6.不要勾选登陆框上边的保存密码选项。

7.如非特殊需求尽量不要用过ssms跨机器登陆。比如你在192.168.1.3这个服务器上用ssms登陆通过ip账号密码192.168.1.4这台服务器的db,这是没问题的,但是当你使用完以后尽量养成随手关掉这个连接的习惯。否则下一个人或者你自己不注意就会把想在本机上执行的脚本执行到了远端的那个db上。

本文由www.129028.com金沙发布于数据库,转载请注明出处:www.129028.com金沙sqlserver备份的几种方式

关键词:

Sql Server中的非聚集索引详细介

这篇我们来解开神秘面纱。 3:通过最后的槽位列表,可以得知173号索引页上存放着8条索引记录。 复制代码 代码如下...

详细>>

必须会的SQL语句(一) 创建数据库与删除数据库【www.129028.com金沙

1.创建数据库 drop命令用于删除数据库。 Create database 名称on primary{ name ='名称', filename ='c:xx名称.mdf', size = 10mb, --数据库...

详细>>

SqlServer提示“列前缀tempdb.无效: 未指定表名”问题解决方案www

在查询凭证、审核凭证时出现“列前缀tempdb.无效:未指定表名”的错误提示,怎么解决? 本文我们总结了几个在安装...

详细>>

【www.129028.com金沙】SQL Server中将查询结果转换为Json格式脚本分享

脚本源码: 复制代码 代码如下:SET ANSI_NULLS ONGOSETQUOTED_IDENTIFIER ONGOCREATEPROCEDURE[dbo].[SerializeJSON](@ParameterSQL ASVARCHAR(MAX)...

详细>>