Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to automate the migration for some databases from an older server. I know that script a tool using C# for example can be a possibility but I need a simple, fast and effective solution in order to build a batch file to do the job.

share|improve this question
add comment

2 Answers

up vote 8 down vote accepted

Create a .sql file with your backup / restore statement(s) and use SQLCMD

About halfway down that article there is an example of doing a backup.

You can refer to the following pages with information on the T-SQL for doing backups and restores:

share|improve this answer
You can call the Sqlcmd above in a batch file. It would make it more convince to use, while you can assign the server name and user name differently if you need. –  Nazila Beikpour Sep 4 '12 at 8:50
add comment

As you have to migrate databases from old server to new, below is the script that will automate it ..

Important: Test it before hand on a TEST Server, so that you understand what the script does ...I bear no Responsibility or Obligation for any amount of DATA LOSS !!!

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%')
      drop table #filetable
      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

      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

      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
      select @dbid = min(dbid) from #filetable where dbid > 4 and dbid > @dbid

select * from #filetable
drop table #filetable
--Finally Shutdown SQL Server
shutdown with nowait
share|improve this answer
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.