Written by:R Teachout
3/31/2008 4:44 PM

How to Attach a database file: (use this template and some fancy dos scripting and manual search/replacing to build it)

use [Master] go Create Database [ACV]      on (Filename = N'D:\MSSQL\Data\DB1_Data.MDF') ,         (Filename = N'D:\MSSQL\Data\DB1_log.ldf') FOR ATTACH GO

(Following chunk should probably be on one line - but so you can read it here..)
EXEC sp_msForEachDb 'use ?;
exec sp_dropuser [dbouser];
EXEC sp_changedbowner [dbouser];
exec sp_dropuser [websiteuser];
EXEC sp_adduser [websiteuser], [websiteuser];
EXEC sp_addrolemember [db_datareader], [websiteuser];
EXEC sp_addrolemember [db_datawriter], [websiteuser]'

And because the above script is only sooo smart.. fix some stuff..
use master
exec sp_dropuser [websiteuser];
exec sp_dropuser [dbouser];
go
use model
exec sp_dropuser [websiteuser];
exec sp_dropuser [dbouser];
go
use msdb
exec sp_dropuser [websiteuser];
exec sp_dropuser [dbouser];
go
use tempdb
exec sp_dropuser [websiteuser];
exec sp_dropuser [dbouser];
go

That should do it.

@echo off echo use [Master] echo GO for /F usebackq %%i in (`dir /b *.mdf`) do echo Create Database [%%i] on (Filename = N'D:\MSSQL\Data\%%i'), (Filename=N'D:\MSSQL\Data\%%i_log.ldf'') FOR ATTACH  GO @echo on

TrackbackPrint

Location: BlogsRamblings, Scriptlets, and more!MSSQL Scriptlets

Your name:
Title:
Comment:
Security Code Enter the code shown above in the box below
Add Comment   Cancel