CREATE PROCEDURE dt_databasebackup @str_backuppath varchar(255),@str_databasename varchar(50),@str_tablename varchar(50) AS declare @str_sqlexecute varchar(255) if @str_tablename=‘‘or @str_tablename=null begin declare backuptable_cursor cursor for select sysobjects.name from sysobjects where sysobjects.type=‘U‘ open backuptable_cursor fetch next from backuptable_cursor into @str_tablename while @@fetch_status=0 begin select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ out ‘+ @str_backuppath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘ exec master..xp_cmdshell @str_sqlexecute fetch next from backuptable_cursor into @str_tablename end deallocate backuptable_cursor end else begin select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ out ‘+ @str_backuppath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘ exec master..xp_cmdshell @str_sqlexecute end --数据库数据备份存储过程 go CREATE PROCEDURE dt_databaserestore @str_restorepath varchar(255),@str_databasename varchar(50),@str_tablename varchar(50) AS declare @str_sqlexecute varchar(255) if @str_tablename=‘‘ or @str_tablename=null begin declare restoretable_cursor cursor for select sysobjects.name from sysobjects where sysobjects.type=‘U‘ open restoretable_cursor fetch next from restoretable_cursor into @str_tablename while @@fetch_status=0 begin select @str_sqlexecute=‘truncate table ‘+@str_tablename execute(@str_sqlexecute) select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ in ‘+ @str_restorepath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘ exec master..xp_cmdshell @str_sqlexecute fetch next from restoretable_cursor into @str_tablename end deallocate restoretable_cursor end else begin select @str_sqlexecute=‘truncate table ‘+@str_tablename execute(@str_sqlexecute) select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ in ‘+ @str_restorepath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘ end --数据库数据恢复存储过程 go
declare @str_sqlexecute varchar(255)
if @str_tablename=‘‘or @str_tablename=null
begin
declare backuptable_cursor cursor for
select sysobjects.name from sysobjects where sysobjects.type=‘U‘
open backuptable_cursor
fetch next from backuptable_cursor into @str_tablename
while @@fetch_status=0
begin
select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ out ‘+ @str_backuppath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘
exec master..xp_cmdshell @str_sqlexecute
fetch next from backuptable_cursor into @str_tablename
end
deallocate backuptable_cursor
end
else
begin
select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ out ‘+ @str_backuppath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘
exec master..xp_cmdshell @str_sqlexecute
end
--数据库数据备份存储过程
go
CREATE PROCEDURE dt_databaserestore @str_restorepath varchar(255),@str_databasename varchar(50),@str_tablename varchar(50) AS
declare @str_sqlexecute varchar(255)
if @str_tablename=‘‘ or @str_tablename=null
begin
declare restoretable_cursor cursor for
select sysobjects.name from sysobjects where sysobjects.type=‘U‘
open restoretable_cursor
fetch next from restoretable_cursor into @str_tablename
while @@fetch_status=0
begin
select @str_sqlexecute=‘truncate table ‘+@str_tablename
execute(@str_sqlexecute)
select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ in ‘+ @str_restorepath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘
exec master..xp_cmdshell @str_sqlexecute
fetch next from restoretable_cursor into @str_tablename
end
deallocate restoretable_cursor
end
else
begin
select @str_sqlexecute=‘truncate table ‘+@str_tablename
execute(@str_sqlexecute)
select @str_sqlexecute=‘bcp ‘+@str_databasename+‘..‘+@str_tablename+‘ in ‘+ @str_restorepath+‘\‘+@str_tablename+‘.bac -c -U sa -P‘
end
--数据库数据恢复存储过程
go