SQL server数据库备份

1backup database  mydb  to disk = 'E:\backup\aa.bak' with differential
2                  |                     |                   |
3              (数据库名称)    (磁盘路径，需要有写入权限)  (差异备份，不加默认是完整备份)
4
5backup log mydb to disk = 'E:\backup\aa.trn'
6                                 |
7                         (日志备份，注意后缀改变)

1USE [mydb]
2GO
3/****** Object:  StoredProcedure [dbo].[sp_BackupDatabase]    Script Date: 2026/10/28 14:40:12 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8-- =============================================
9-- Author:		<Author,,Name>
10-- Create date: <Create Date,,>
11-- Description:	<Description,,>
12-- =============================================
13ALTER PROCEDURE [dbo].[sp_BackupDatabase]
14	@dbname nvarchar(500),
15	@backupType char(1) = 'f'
16AS
17BEGIN
18	-- SET NOCOUNT ON added to prevent extra result sets from
19	-- interfering with SELECT statements.
20	SET NOCOUNT ON;
21
22	declare @backupPath nvarchar(1000)
23	declare @filename nvarchar(1000)
24	declare @sql nvarchar(4000)
25	declare @errorMsg varchar(1000)
26	begin try
27		set @backupPath = 'E:\backup\'
28		exec xp_cmdshell 'if not exist "E:\backup\" mkdir "E:\backup\"', no_output
29		set @filename = @dbname + '_' + 
30			case @backupType when 'f' then 'Full' when 'd' then 'Diff' when 'l' then 'log'end +'_' + 
31			format(getdate(), 'yyyyMMdd_hhmmss') + 
32			case @backupType when 'l' then '.trn' else '.bak'end
33		set @sql = case @backupType when 'f' then 'backup database' + quotename(@dbname)
34			when 'd' then 'backup database' + quotename(@dbname) + 'with differential'
35			when 'l' then 'backup log' + quotename(@dbname) end
36		set @sql = @sql + 'to disk='''+@backupPath + @filename +''''
37		if @backupType in ('f', 'd')
38		set @sql=@sql + 'with compression, stats=10'
39
40		print '开始备份'+@dbname
41		print '备份类型' + case @backupType when 'f' then '完整备份' when 'd' then '差异备份' when 'l' then '日志备份' end
42		print '备份文件'+@filename
43
44		exec sp_executesql @sql
45	end try
46	begin catch
47		print '错误信息：'+ ERROR_MESSAGE();
48	end catch
49END