asp.net数据库备份与还原
-2
所有回答(3)
0
写一个备份（还原）数据库的SQL语句，在点击按钮时执行就可以了
backup database northwind to mybackupfile
你是说这个吧,但是怎么把它应用到VS中呢？具体的呢！
0
存储过程,会用吗?
把SQL语句封装成存储过程
--数据库自动备份_1
USE [Esurfing_Business]
GO
/****** Object: StoredProcedure [dbo].[zsp_backup_db_HourlyJob] Script Date: 11/25/2011 17:17:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[zsp_backup_db_HourlyJob]
@p varchar(256)
--严禁手动执行
as
--zsp_backup_db_HourlyJob 'f:\db_backup'
--declare @p varchar(100)
--set @p = 'Y:\tsmBackup'
declare @ datetime
set @ = getdate()
-- 周一到周日
-- 1 2 3 4 5 6 7
declare @i int
set @i = (@@Datefirst + datepart(weekday, @)) % 7
+ case when (@@Datefirst + datepart(weekday, @)) % 7 < 2
then 6
else
-1
end
declare @j int --钟点
set @j = datepart(Hour, @)
if @i = 7 --周日
begin
if @j in (4,5,6,7,8)
begin
--周日凌晨、点不做备份,为全备留出足够的时间
return
end
end
declare @dbName sysname
set @dbName = db_name()
declare @bakType varchar(4)
set @bakType = 'Log'
exec zsp_backup_db @dbName, @i, @j, @p, @bakType
if @j = 10 -- 03:00 时间点数
begin
if @i = 2 -- 周日full backup，如果是星期天，full
begin
set @bakType = 'Full'
exec zsp_backup_db @dbName, @i, @j, @p, @bakType
end
else
begin -- except 周日diff backup
set @bakType = 'Diff'
exec zsp_backup_db @dbName, @i, @j, @p, @bakType
end
end
--数据库自动备份_2
USE [Esurfing_Business]
GO
/****** Object: StoredProcedure [dbo].[zsp_backup_db] Script Date: 11/25/2011 17:46:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[zsp_backup_db]
@db sysname
, @weekday int
, @hour int
, @path varchar(100) = ''
, @bakType varchar(5) = 'Full'
as
declare @w char(1)
set @w = cast(@weekday as char(1))
declare @h varchar(2)
set @h = right('0' + cast(@hour as varchar(2)), 2)
set @bakType = upper(ltrim(rtrim(@bakType)))
declare @bkFile varchar(100)
set @bkFile = @db
+ '.'
+ @w
+ '.'
+ @h
+ '.'
+ left(@bakType, 3)
+ '.bak'
set @path = @path
+ '\'
+ @db
+ '\'
+ @w
+ '\'
print(@bkFile+'-----'+@path+'----'+@bakType+'------'+@db)
exec zsp_backup @dbname = @db, @bkpath = @path, @bkfname = @bkFile, @bktype = @bakType
--数据库自动备份_3
USE [Esurfing_Business]
GO
/****** Object: StoredProcedure [dbo].[zsp_backup] Script Date: 11/25/2011 17:46:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[zsp_backup]
--20050906 YuXiYue
@dbname sysname = '' --要备份的数据库名称,不指定则备份当前数据库
, @bkpath varchar(256) = '' --备份文件的存放目录,不指定则使用SQL默认的备份目录
, @bkfname varchar(256) = '' --备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间
, @bktype varchar(4) = 'FULL' --备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份
, @appendfile bit = 0 --1追加/0覆盖备份文件
, @password varchar(20) = '' --为备份文件设置的密码(仅sql2000支持),设置后,恢复时必须提供此密码
as
set @bktype = upper(@bktype)
declare @sql varchar(8000)
if isnull(@dbname, '') = ''
begin
set @dbname = db_name()
end
if isnull(@bkpath, '') = ''
begin
select @bkpath = rtrim(reverse(filename))
from master..sysfiles
where name = 'master'
select @bkpath = substring(@bkpath, charindex('\', @bkpath) + 1, 4000)
, @bkpath = reverse(substring(@bkpath, charindex('\', @bkpath), 4000))
+ 'BACKUP\'
end
if isnull(@bkfname, '') = ''
begin
set @bkfname = '\DBNAME\_\DATE\_\TIME\.BAK'
end
set @bkfname = replace(replace(replace(@bkfname, '\DBNAME\', @dbname)
, '\DATE\'
, convert(varchar, getdate(), 112))
, '\TIME\'
, replace(convert(varchar, getdate(), 108), ':', ''))
set @sql = 'BACKUP '
+ case @bktype when 'LOG' then 'LOG [' else 'DATABASE [' end
+ @dbname
+ '] TO DISK = '''
+ @bkpath
+ @bkfname
+ ''' WITH COMPRESSION,FORMAT,'
+ case @bktype when 'DIFF' then 'DIFFERENTIAL, ' else '' end
+ case @appendfile when 1 then 'NOINIT' else 'INIT' end
+ case isnull(@password,'') when '' then '' else ', PASSWORD = '''
+ @password
+ '''' end
print @sql
exec(@sql)
set @bkpath = @bkpath
+ @bkfname
--由于是覆盖备份所以不dump
--EXEC sp_addumpdevice 'disk', @bkfname, @bkpath
我要个实例就好了，这个看不懂？
@丰: 哎,扶不上墙
@dearz: 我的路径怎么总是出错：exec zsp_backup @dbname = backups, @bkpath =E:\备份\新建文件夹, @bkfname = file, @bktype =文件
@丰: 将3个存储过程都创建
然后建立文件夹,F:\db_backup ,在此文件夹中简历 为 1 ,2,3,4,5,6,7 的名字的7个文件夹
调用之前先做一次数据库的全备
调用的话就用此入口即可实现 zsp_backup_db_HourlyJob 'f:\db_backup'
0
是用SMO备份的 记着添加SMO相关的dll引用 备份按钮里调备份函数 还原按钮调还原函数就行了
备份函数
private void RestoreBase()
{
ServerConnection sConn = new ServerConnection("sqlserver所在机器的Ip", "用户名", “密码”);
Server s=new Server(sConn);
s.ConnectionContext.Connect();
Restore restore=new Restore();
restore.Database = "数据库名";
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
//D:\testFullBackUp.bak 备份路径 D盘是SQlserve所在的机器D盘
restore.Devices.Add(new BackupDeviceItem(@"D:\testFullBackUp.bak", DeviceType.File));
restore.SqlRestore(s);
}
还原函数：
private void RestoreBase()
{
ServerConnection sConn = new ServerConnection("sqlserver所在机器的Ip", "用户名", “密码”);
Server s=new Server(sConn);
s.ConnectionContext.Connect();
Restore restore=new Restore();
restore.Database = "数据库名";
restore.ReplaceDatabase = true;
restore.Action = RestoreActionType.Database;
//D:\testFullBackUp.bak D盘是SQlserve所在的机器D盘
restore.Devices.Add(new BackupDeviceItem(@"D:\testFullBackUp.bak", DeviceType.File));
restore.SqlRestore(s);
}
