主页 / dba / 问题 / 23518
Accepted
Rui Marques
Asked: 2012-09-01 08:44:40 +0800 CST

如何从类似于 mysql 的命令行备份/恢复 SQL Server 数据库

  • 772

我需要从旧服务器自动迁移某些数据库。我知道使用 C# 编写工具脚本是可能的，但我需要一个简单、快速且有效的解决方案来构建批处理文件来完成这项工作。

sql-server sql-server-2008

2 个回答

  1. Best Answer

    使用您的备份/恢复语句创建一个 .sql 文件并使用SQLCMD

    在那篇文章的一半左右，有一个做备份的例子。

    您可以参考以下页面，了解有关用于执行备份和恢复的 T-SQL 的信息：

    • 9

  2. 由于您必须将数据库从旧服务器迁移到新服务器，因此下面是自动化它的脚本..

    重要提示：事先在测试服务器上对其进行测试，以便您了解脚本的作用......我对任何数量的数据丢失不承担任何责任或义务！！！

    set nocount on
/****************************************************************************** 
    Author  :: Kin
    Desc    :: Transfer Logins, Databases from one instance/Server to another
*******************************************************************************/
  declare @datafile varchar(255),
            @logfile varchar(255),
            @dbid tinyint,
            @SQLText varchar(8000),
            @dbname varchar(255),
            @destserver varchar(255),
            @SQLText2 varchar(8000)
set @destserver ='' --Destination Server Name goes here.

--1.Transfer Logins
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@@servername+' -E -Q"execute master.dbo.sp_help_revlogin" -oD:\logs\revloginout.sql'''
--print @sqltext
exec (@sqltext)
-- Create on Destination Server.
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@destserver+' -E -iD:\logs\revloginout.sql'''
--print @sqltext
exec (@sqltext)

--2. Detach All Local Databases and prepare for Attach on dest.
 --- if you want to filer only some database, then you can do it here !!
if exists(select 1 from tempdb..sysobjects where name like '%#filetable%')
      begin
      drop table #filetable
      end
      create table #filetable (mdf varchar(255),ldf varchar(255),dbid tinyint,dbname varchar(100),fileid tinyint)
      --

      insert #filetable (mdf,dbid,fileid) 
      select physical_name,database_id,data_space_id from sys.master_files where data_space_id=1

      insert #filetable (ldf,dbid,fileid) 
      select physical_name,database_id,data_space_id from sys.master_files where data_space_id=0

      update u 
      set u.dbname = s.name
      from #filetable u
      inner join master..sysdatabases s 
      on u.dbid = s.dbid


select @dbid = min(dbid) from #filetable where dbid > 4
while @dbid is not null
begin

      select @SQLText = 'alter database '+ dbname from #filetable where dbid = convert(varchar,@dbid) 
      select @SQLText = @SQLText+' set single_user with rollback immediate'
      select @SQLText = @SQLText+' exec master..sp_detach_db ' + dbname from #filetable where dbid = convert(varchar,@dbid)
      print @SQLText
      Exec(@SQLText)

      select @SQLText2 = 'exec opendatasource(''SQLNCLI'',''Datasource='+@destserver+';Persist Security Info=False;Integrated Security=SSPI'').master.dbo.sp_attach_db '''+dbname+'''' from #filetable where dbid = @dbid
      select @SQLText2= @SQLText2+','''+ mdf+'''' from #filetable where dbid = @dbid and mdf is not null
      select @SQLText2=@SQLText2+','''+ ldf+''''  from #filetable where dbid = @dbid and ldf is not null
      print @SQLText2
      Exec(@SQLText)
      select @dbid = min(dbid) from #filetable where dbid > 4 and dbid > @dbid
end

select * from #filetable
drop table #filetable
--Finally Shutdown SQL Server
shutdown with nowait
    • 2

相关问题