Posted By

pollusb on 03/10/09


Tagged

attach detach


Versions (?)

Detach and Attach all


 / Published in: SQL
 

I wrote that while working at CGI on Océ project in 2009-03. This script will help you migrate DB from one server to another. You have to use the source server to generate the script for the destination server.

  1. -- ATTACH / DETACH
  2. SELECT 'exec sp_detach_db '''+rtrim(name)+'''' FROM sysdatabases WHERE dbid > 4
  3.  
  4. -- Use script on source to attach on destination
  5. SELECT dbid*1000,name,'exec sp_attach_db '''+name+''',' FROM sysdatabases WHERE dbid > 4
  6. union ALL
  7. SELECT dbid*1000+fileid,db_name(dbid), ','''+rtrim(filename)+''''
  8. FROM sysaltfiles WHERE dbid > 4
  9. ORDER BY 1

Report this snippet  

You need to login to post a comment.