exec sp_addumpdevice 'disk', @DevName_log,@log_path select @rc=@@error IF @RC<>0 begin EXEC SP_DropDevice @Devname_data exec sp_dropdevice @devname_log SELECT @RC=-1000 return @rc end END
IF @kind='backup' BEGIN IF @bakequip=0 BEGIN IF @baktype=0 BEGIN IF @bakdb=0 BEGIN BACKUP DATABASE Northwind TO DISK=@Devname_data WITH INIT END IF @baklog=0 BEGIN BACKUP LOG Northwind WITH NO_LOG BACKUP LOG Northwind TO DISK=@DevName_log WITH INIT,NO_TRUNCATE END END ELSE BEGIN IF @bakdb=0 BEGIN BACKUP DATABASE Northwind TO DISK=@DevName_data WITH NOINIT END IF @baklog=0 BEGIN BACKUP LOG Northwind WITH NO_LOG BACKUP LOG Northwind TO DISK=@DevName_log WITH NOINIT,NO_TRUNCATE END END END SELECT @retmsg='数据库备份成功!' END
IF @kind='restore' BEGIN RESTORE DATABASE Northwind FROM DISK= @DevName_data WITH REPLACE SELECT @retmsg='恢复数据库成功!' END
exec sp_addumpdevice 'disk', @DevName_log,@log_path select @rc=@@error IF @RC<>0 begin EXEC SP_DropDevice @Devname_data exec sp_dropdevice @devname_log SELECT @RC=-1000 return @rc end END
IF @kind='backup' BEGIN IF @bakequip=0 BEGIN IF @baktype=0 BEGIN IF @bakdb=0 BEGIN BACKUP DATABASE Northwind TO DISK=@Devname_data WITH INIT END IF @baklog=0 BEGIN BACKUP LOG Northwind WITH NO_LOG BACKUP LOG Northwind TO DISK=@DevName_log WITH INIT,NO_TRUNCATE END END ELSE BEGIN IF @bakdb=0 BEGIN BACKUP DATABASE Northwind TO DISK=@DevName_data WITH NOINIT END IF @baklog=0 BEGIN BACKUP LOG Northwind WITH NO_LOG BACKUP LOG Northwind TO DISK=@DevName_log WITH NOINIT,NO_TRUNCATE END END END SELECT @retmsg='数据库备份成功!' END
IF @kind='restore' BEGIN RESTORE DATABASE Northwind FROM DISK= @DevName_data WITH REPLACE SELECT @retmsg='恢复数据库成功!' END
RETURN 0
这样不行?!: ADOquery1.sql.add('backup database PCMIS to disk="c:\data1.dat";')procedure Tdatasetting.FlatButton1Click(Sender: TObject); begin ADOQuery1.Close; ADOQuery1.SQL.Clear; ADOQuery1.SQL.Add('backup database hrdb to disk = ' + ''''+FlatEdit1.Text+''''); ADOQuery1.ExecSQL; showmessage('数据以成功备份成功!'); datasetting.Close; end;procedure Tdatares.FlatButton1Click(Sender: TObject); begin ADOQuery1.Close; ADOQuery1.SQL.Clear; ADOQuery1.SQL.Add('restore database hrdb from disk = ' + ''''+FlatEdit1.Text+''''); ADOQuery1.ExecSQL; showmessage('数据以成功恢复成功!'); datares.Close; end;
还原数据库sql语句:restore database hrdb form disk='c:\hrdb.dat' 运行后出现“因为数据库正在使用,所以未能获得对数据库的排它访问权。”
@bakequip int, -- 备份设备:磁盘&磁带
@bakpath varchar(50), -- 带全路径的备份文件名
@baktype int, -- 完全备份&增量备份
@baklog int, -- ‘0’备份日志
@bakdb int, -- ‘0’备份数据库
@kind varchar(7), --备份还是恢复
@retmsg varchar(20) output --返回信息
AS
DECLARE @DevName_data varchar(50)
DECLARE @DevName_log varchar(50)
declare @db_path varchar(100)
declare @log_path varchar(100)
DECLARE @RC INT SELECT @db_path = @bakpath + '.dat'
SELECT @log_path = @bakpath + 'log.dat'
SELECT @RC=0 DBCC CHECKDB(Northwind)
/***********************************************************
** CREATE BACKUP AND RESTORE DEVICES
************************************************************/
IF @RC=0
BEGIN
EXEC sp_addumpdevice 'disk', @DevName_data,@db_path
exec sp_addumpdevice 'disk', @DevName_log,@log_path
select @rc=@@error
IF @RC<>0
begin
EXEC SP_DropDevice @Devname_data
exec sp_dropdevice @devname_log
SELECT @RC=-1000
return @rc
end
END
IF @kind='backup'
BEGIN
IF @bakequip=0
BEGIN
IF @baktype=0
BEGIN
IF @bakdb=0
BEGIN
BACKUP DATABASE Northwind TO DISK=@Devname_data
WITH INIT
END
IF @baklog=0
BEGIN
BACKUP LOG Northwind WITH NO_LOG
BACKUP LOG Northwind TO DISK=@DevName_log
WITH INIT,NO_TRUNCATE
END
END
ELSE BEGIN
IF @bakdb=0
BEGIN
BACKUP DATABASE Northwind TO DISK=@DevName_data
WITH NOINIT
END
IF @baklog=0
BEGIN
BACKUP LOG Northwind WITH NO_LOG
BACKUP LOG Northwind TO DISK=@DevName_log
WITH NOINIT,NO_TRUNCATE
END
END
END
SELECT @retmsg='数据库备份成功!'
END
IF @kind='restore'
BEGIN
RESTORE DATABASE Northwind FROM DISK= @DevName_data WITH REPLACE
SELECT @retmsg='恢复数据库成功!'
END
RETURN 0
@bakequip int, -- 备份设备:磁盘&磁带
@bakpath varchar(50), -- 带全路径的备份文件名
@baktype int, -- 完全备份&增量备份
@baklog int, -- ‘0’备份日志
@bakdb int, -- ‘0’备份数据库
@kind varchar(7), --备份还是恢复
@retmsg varchar(20) output --返回信息
AS
DECLARE @DevName_data varchar(50)
DECLARE @DevName_log varchar(50)
declare @db_path varchar(100)
declare @log_path varchar(100)
DECLARE @RC INT SELECT @db_path = @bakpath + '.dat'
SELECT @log_path = @bakpath + 'log.dat'
SELECT @RC=0 DBCC CHECKDB(Northwind)
/***********************************************************
** CREATE BACKUP AND RESTORE DEVICES
************************************************************/
IF @RC=0
BEGIN
EXEC sp_addumpdevice 'disk', @DevName_data,@db_path
exec sp_addumpdevice 'disk', @DevName_log,@log_path
select @rc=@@error
IF @RC<>0
begin
EXEC SP_DropDevice @Devname_data
exec sp_dropdevice @devname_log
SELECT @RC=-1000
return @rc
end
END
IF @kind='backup'
BEGIN
IF @bakequip=0
BEGIN
IF @baktype=0
BEGIN
IF @bakdb=0
BEGIN
BACKUP DATABASE Northwind TO DISK=@Devname_data
WITH INIT
END
IF @baklog=0
BEGIN
BACKUP LOG Northwind WITH NO_LOG
BACKUP LOG Northwind TO DISK=@DevName_log
WITH INIT,NO_TRUNCATE
END
END
ELSE BEGIN
IF @bakdb=0
BEGIN
BACKUP DATABASE Northwind TO DISK=@DevName_data
WITH NOINIT
END
IF @baklog=0
BEGIN
BACKUP LOG Northwind WITH NO_LOG
BACKUP LOG Northwind TO DISK=@DevName_log
WITH NOINIT,NO_TRUNCATE
END
END
END
SELECT @retmsg='数据库备份成功!'
END
IF @kind='restore'
BEGIN
RESTORE DATABASE Northwind FROM DISK= @DevName_data WITH REPLACE
SELECT @retmsg='恢复数据库成功!'
END
RETURN 0
ADOquery1.sql.add('backup database PCMIS to disk="c:\data1.dat";')procedure Tdatasetting.FlatButton1Click(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('backup database hrdb to disk = ' + ''''+FlatEdit1.Text+'''');
ADOQuery1.ExecSQL;
showmessage('数据以成功备份成功!');
datasetting.Close;
end;procedure Tdatares.FlatButton1Click(Sender: TObject);
begin
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('restore database hrdb from disk = ' + ''''+FlatEdit1.Text+'''');
ADOQuery1.ExecSQL;
showmessage('数据以成功恢复成功!');
datares.Close;
end;
运行后出现“因为数据库正在使用,所以未能获得对数据库的排它访问权。”