数据库有没有移动（备份）mdf文件的命令？或者用DOS命令实现？

[istring]

因为在数据库中，要先关闭数据库进程才能拷贝备份mdf 文件，
如果用程序实现怎么实现

[青锋-SS]

backup database

[青锋-SS]

复制备份出来的文件,然后再还原.
或者把数据库分离以后再复制,完了再附加上去.

[青锋-SS]

也可以把服务管理控制器先停止了,然后复制,之后再启动.

[liangCK]

先用系统表获取数据库所包含的文件和文件路径..

然后将数据库给分离掉.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')
go

exec compdb @dbname=N'MyTest',@backupfile=N'g:\'

[青锋-SS]

楼上的代码真全啊

[liangCK]

[Quote=引用 5 楼 sdhylj 的回复:]
楼上的代码真全啊
[/Quote]

呵呵..跟老乌龟相比..差远了.

[istring]

4楼说先detach，后拷贝数据库文件，这个方法也不错哦，
谁还有更好的办法呢？

[青锋-SS]

应该就这么几种吧

[chuifengde]

先备份，再还原，还原的时候用move子句