Adsense

Monday, January 14, 2013

Create and attach script for a database prior to detaching it


If you are going to detach a database in SQL Server that you’re planning on attaching later save yourself some trouble and generate the attach script first.  Running the code below will generate a script to be used to attach your database at a later time (assuming that you keep the database files in the same location that they were in when you detached it).  Make sure you’re in the database you’re going to detach when you run this script


declare @dbname as sysname
declare @sql as varchar(1000)
declare @n as integer
declare @filename as sysname

set @dbname = db_name()

declare curs cursor for
select rtrim(filename) from sysfiles

open curs

fetch next from curs into @filename
set @n = 0

print '--ATTACH SCRIPT FOR ' + @dbname
print 'exec sp_attach_db @dbname = N''' + @dbname + ''','

while @@fetch_status = 0
begin
      set @n = @n + 1
      print '@filename' + cast(@n as varchar) + ' = ''' + @filename + ''','
      fetch next from curs into @filename
end

close curs
deallocate curs

No comments:

Post a Comment