script for attach/detach for more number of databases
-
I have to move datafiles from local drive to SAN. Since
detach/attach will take a lot of time.There are more than 100 databases. Is there
any script to do this which reduces time.
-
I'm not sure how much time it will take you but you could easily create a dynamic SQL script to do the work for you.
Create a cursor on the master.sys.databases
Go to the database pull the files from sysfiles
Create a dynamic script to
1) Detatch the database
Something like @sql = 'EXEC master.dbo.sp_detach_db @dbname = N'''+sys.databases.Name+''', @keepfulltextindexfile=N''true''
2) Move the files you got from sysfiles (create the command before running step 1) or just do all the detaches .. then manually move all the files .. really up to you.
3) Re-attach the database
Something like @sql = 'EXEC master.dbo.sp_detach_db @dbname = N'''+sys.databases.Name+''''
Hope that helps.
Ken
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
-
dunno if this is the case, but if you move ALL your db files from the local drives to a SAN area and nothing else is left on the local drive and it isn't same as the system drive, you can do the following:
1. stop your sql server
2. copy all files from local drive to san drive
3. change drive letters so that the SAN will be the same as the local drives were before
4. start sql server
I've done and seen this done multiple times, pretty simple,eh?
If you need to generate scripts, it is not so hard to do when you use the sysfiles and sysdatabases system views.
If that is too much for you, use excel as a script generator. Get the db names with select * sys.sysdatabases, write the command on left and right of the database and db file names and add the up in the most left column with a1&a2&a3 etc etc.
Btw, the SQL Server 2005 "official" attach syntax is under the "Create database" command, maybe look that up also from books online. Although sp_detach_db and sp_attach_db both also work on 2005.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply