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
backup database mydb to disk='c:\mydb.bak' 把数据库备份下来。 然后要通程序把c:\mydb.bak还原到数据库。sql语句如下: restore database mydb from disk='c:\mydb.bak'
windindance(风舞轻扬) 兄弟,系统总是说:“to附近有语法错误”
backup database mydb to disk='c:\mydb.bak' 这样不错吧 只要你有Mydb的Backup权限
在Master数据库中建如下存储过程: Create Procedure killspid (@dbname varchar(20)) as begin declare @sql nvarchar(500) declare @spid int set @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')' exec (@sql) open getspid fetch next from getspid into @spid while @@fetch_status<>-1 begin exec('kill '+@spid) fetch next from getspid into @spid end close getspid deallocate getspid endGO 在恢复数据库之前先执行此存储过程,像这样:Use Master Go exec killspid 'mydb' Go restore database mydb from disk='c:\mydb.bak'在Delphi中直接用ExecSQL先后执行以上SQL语句即可
begin
Close;
SQL.Text := 'backup database aaa to disk=''c:\aaa.bak''';
ExecSQL;
end;
备份数据库: backup database @DatabaseName To DISK=@Filename WITH INIT
恢复数据库: Restore Database @DatabaseName From DISK=@FileName
@DatabaseName :SQL SERVER数据库
@FileName :备份的文件名
@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
把数据库备份下来。
然后要通程序把c:\mydb.bak还原到数据库。sql语句如下:
restore database mydb from disk='c:\mydb.bak'
这样不错吧
只要你有Mydb的Backup权限
必须关闭所有数据库连接(包括程序)
应在master数据库下进行恢复
在Master数据库中建如下存储过程:
Create Procedure killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status<>-1
begin
exec('kill '+@spid)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
endGO
在恢复数据库之前先执行此存储过程,像这样:Use Master
Go
exec killspid 'mydb'
Go
restore database mydb from disk='c:\mydb.bak'在Delphi中直接用ExecSQL先后执行以上SQL语句即可