Detach & Attach User Databases

October 26, 2009

>>Script Language and Platform: SQL Server 2000
This script moves databases to different physical locations. This script can also be used to just detach/attach user databases as part of your bigger process.

You can customize the script to use cursors which I HATE.

Author: Ajith Dharwar

DECLARE @Path varchar(400)
SET @Path = "D:\MSSQL\Data\"
use [master]
--STEP 1 
--Keep the output of this script aside
Select "EXEC sp_attach_db @dbname = ''"+ [name] +"''," +
     "@filename1 = ''" + @Path +  [name] +"_Data.mdf''," + 
     "@filename2 = ''"+ @Path + [name] +"_Log.ldf''" from sysdatabases 
	 	where dbid > 4

--STEP 2
--Generate this script and run
Select ''EXEC sp_detach_db '' + [name]  from sysdatabases where dbid > 4

--After Successful run from Output of STEP 2 RUN Output of STEP 2

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued

Back to Database Journal Home