sql server 2000备份还原数据库

2023-02-14 16:34:43

转载请注明出处：http://blog.csdn.net/neochan1108/article/details/79248017

备份：

-- Create the backup device for the full MyNwind backup.
EXEC sp_addumpdevice 'disk', 'MyNwind_2',
'f:\neochan\MyNwind_2.dat'
--Create the log backup device.
EXEC sp_addumpdevice 'disk', 'MyNwindLog1',
'f:\neochan\MyNwindLog1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE testDB TO MyNwind_2
-- Update activity has occurred since the full database backup.
-- Back up the log of the MyNwind database.
BACKUP LOG testDB TO MyNwindLog1
-- 清除设备
exec sp_dropdevice 'MyNwind_2'
exec sp_dropdevice 'MyNwindLog1'

还原：

-- 分离数据库
use master
exec killspid 'testDB'
EXEC sp_detach_db 'testDB', 'true'
-- 还原数据库
EXEC sp_addumpdevice 'disk', 'MyNwind_2',
'f:\neochan\MyNwind_2.dat'
RESTORE DATABASE testDB FROM MyNwind_2 WITH REPLACE,NORECOVERY
EXEC sp_addumpdevice 'disk', 'MyNwindLog1',
'f:\neochan\MyNwindLog1.dat'
RESTORE LOG testDB FROM MyNwindLog1 WITH REPLACE
exec sp_dropdevice 'MyNwind_2'
exec sp_dropdevice 'MyNwindLog1'

killspid的存储过程为（在master上）：

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE killspid (@dbname varchar(20)) AS
begin
declare @sql nvarchar(500),@temp varchar(1000)
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
set @temp='kill '+rtrim(@spid)
exec(@temp)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO