MSSQL备份与还原
1.备份
ALTER procedure [dbo].[DB_backup] (@DB_Name nvarchar(40))
asdeclare @back_str nvarchar(150),@mysql nvarchar(600)
set @back_str = case datepart(dw,getdate())
when 1 then 'Z:\backup\sunday\'
when 2 then 'Z:\backup\monday\'
when 3 then 'Z:\backup\tuesday\'
when 4 then 'Z:\backup\wednesday\'
when 5 then 'Z:\backup\thursday\'
when 6 then 'Z:\backup\friday\'
when 7 then 'Z:\backup\saturday\'
else 'Z:\backup\others\' end
-- set @back_str = @back_str + convert(nvarchar(12),getdate(),112) + '_'+ @DB_Name + '.bak'
set @back_str = @back_str + 'uniohm_'+ @DB_Name + '.bak'
set @mysql = 'backup database ' + @db_Name + ' to disk = ' + '''' + @back_str+ '''' + ' with init '
execute(@mysql)
2.还原
ALTER procedure [dbo].[sky_restoredatabase] @dbname nvarchar(50),@dirpath nvarchar(250)--@dbname 數據庫名字,@dirpath 已備份出來的路徑加文件名
as
Declare @exists int
if not exists(select name from master..sysdatabases where name = @dbname)
begin
Print 'Database '+@dbname+' not found,Enter valid Database Name'
Return
end
Execute master..xp_fileexist @dirpath,@exists output
if (@exists = 0)
begin
Print 'File '+@dirpath+' Does not Exist'
print 'DataBase Cannot be restored,Enter the valid Backup File'
return
end
Declare SysProc cursor for select spid from master..sysprocesses where
dbid=(select dbid from master..sysdatabases where name = @dbname)
Declare @SysProcId smallint
Declare @killStatement char(30)
Open SysProc
while ( 0 = 0) begin
Fetch Next From SysProc into @SysProcId
if (@@Fetch_status <> 0)
Break
set @killStatement = 'Kill '+ Cast(@SysProcId as char(30))
Exec(@killStatement)
end
Close SysProc
deallocate SysProc
execute('use master')
Execute('Restore database '+@dbname+' From disk='''+@dirpath+''' '+
' with FILE = 1,NOUNLOAD,STATS =10,RECOVERY,REPLACE')