①.备份数据库
backup database [mzwudb] to disk='D:\mzwudb.bak' with format②.还原数据库
exec killconnection [mzwudb]
restore database [mzwudb] from disk='D:\mzwudb.bak' with replace若是还原到不同名的库，则用：
exec [killconnection] [newdb]
restore database [newdb] from disk='D:\mzwudb.bak' with replace,move 'mzwudb_Data' to 'D:\newdb_Data.MDF',move 'mzwudb_Log' to 'D:\newdb_Log.LDF'2.SQL Server分离附加数据库
①.分离数据库
exec killconnection [mzwudb]
exec sp_detach_db [mzwudb]②.附加数据库
exec sp_attach_db 'mzwudb','D:\mzwudb_Data.MDF','D:\mzwudb_Log.LDF'补充说明
[1].上边所有操作均应在master库中执行(use master)；
[2].killconnection存储过程创建于master库，用于删除指定数据库连接：
create proc [killconnection]
@dbname nvarchar(100)
as
declare @spid nvarchar(20)
declare #tab cursor for select spid=cast(spid as nvarchar(20)) from [sysprocesses] where dbid=db_id(@dbname)
open #tab
fetch next from #tab into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #tab into @spid
end
close #tab
deallocate #tab
go
评论（0）
暂无评论。