先用系统表获取数据库所包含的文件和文件路径..然后将数据库给分离掉.sp_detach_db .再用xp_cmdshell去copy到你的目标目录.. 再给符加回来.sp_attach_db参考这个if exists(select name from sys.objects where name=N'compdb' and type='P') drop proc compdb go create proc compdb @dbname nvarchar(128), @backupfile nvarchar(260)='' as declare @sql nvarchar(260),@dbid varchar(2) exec ('backup log ['+@dbname+'] with no_log') exec ('dbcc shrinkdatabase(['+@dbname+'])')
set @sql=convert(char(10),getdate(),120) exec ('backup database ['+@dbname+'] to disk='''+@backupfile+@dbname+'_'+@sql+'.bak'' with format,checksum')
set @dbid=db_id(@dbname) create table #t(name nvarchar(128),physical_name nvarchar(260),type_desc nvarchar(20)) exec ('insert #t select name,physical_name,type_desc from sys.master_files where database_id='+@dbid) exec ('sp_detach_db '''+@dbname+'''')
declare @name nvarchar(128),@physical_name nvarchar(260) declare cur cursor for select physical_name from #t where type_desc=N'LOG' open cur fetch next from cur into @physical_name while @@fetch_status=0 begin set @sql='del '''+rtrim(@physical_name)+'''' exec xp_cmdshell @sql,no_output fetch next from cur into @physical_name end close cur deallocate cur set @sql='' declare cur cursor for select name,physical_name from #t where type_desc!=N'LOG' open cur fetch next from cur into @name,@physical_name while @@fetch_status=0 begin set @sql=@sql+',(name='''+@name+''',filename='''+@physical_name+''')' fetch next from cur into @name,@physical_name end close cur deallocate cur set @sql=stuff(@sql,1,1,'') exec ('create database '+@dbname+' on '+@sql+' for attach_rebuild_log') goexec compdb @dbname=N'MyTest',@backupfile=N'g:\'
.再用xp_cmdshell去copy到你的目标目录..
再给符加回来.sp_attach_db参考这个if exists(select name from sys.objects where name=N'compdb' and type='P')
drop proc compdb
go
create proc compdb
@dbname nvarchar(128),
@backupfile nvarchar(260)=''
as
declare @sql nvarchar(260),@dbid varchar(2) exec ('backup log ['+@dbname+'] with no_log')
exec ('dbcc shrinkdatabase(['+@dbname+'])')
set @sql=convert(char(10),getdate(),120)
exec ('backup database ['+@dbname+'] to disk='''+@backupfile+@dbname+'_'+@sql+'.bak'' with format,checksum')
set @dbid=db_id(@dbname)
create table #t(name nvarchar(128),physical_name nvarchar(260),type_desc nvarchar(20))
exec ('insert #t select name,physical_name,type_desc from sys.master_files where database_id='+@dbid) exec ('sp_detach_db '''+@dbname+'''')
declare @name nvarchar(128),@physical_name nvarchar(260)
declare cur cursor for
select physical_name from #t where type_desc=N'LOG'
open cur
fetch next from cur into @physical_name
while @@fetch_status=0
begin
set @sql='del '''+rtrim(@physical_name)+''''
exec xp_cmdshell @sql,no_output fetch next from cur into @physical_name
end
close cur
deallocate cur set @sql=''
declare cur cursor for
select name,physical_name from #t where type_desc!=N'LOG'
open cur
fetch next from cur into @name,@physical_name
while @@fetch_status=0
begin
set @sql=@sql+',(name='''+@name+''',filename='''+@physical_name+''')'
fetch next from cur into @name,@physical_name
end
close cur
deallocate cur
set @sql=stuff(@sql,1,1,'')
exec ('create database '+@dbname+' on '+@sql+' for attach_rebuild_log')
goexec compdb @dbname=N'MyTest',@backupfile=N'g:\'
谁还有更好的办法呢?