sql server 2000备份还原数据库

柔光的暖阳◎ 2022-05-31 01:57 199阅读 0赞

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