sql server 2000备份还原数据库

转载请注明出处：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