HELP!!! Detach databases - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

HELP!!! Detach databases

Author

Topic

Newbie_SQL
Starting Member

5 Posts

Posted - 2012-05-23 : 11:53:14

I am working on a 2005 sql set up that has over 2000 databases that need to be detached!
All the databases that need to be detached are in the same folder and contain the string "NYMT".
Can someone please tell me if there is a way to detach all of these files withour doing it one by one?

Thanks in advance.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-23 : 12:32:38

SELECT 'EXEC sp_detach_db '''' + name + ''';' FROM sys.databases WHERE name LIKE '%NYMT%'

Copy and paste the results into a new window and run it. Obviously start out with only 1 at a time, then a few more, and make sure they detach cleanly.

Newbie_SQL
Starting Member

5 Posts

Posted - 2012-05-23 : 12:41:25

THANK YOU but I am a complete newbie and not sure where to paste this and how it works.
Sorry to be a pain but can you provide more details.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-23 : 13:42:28

If you're running SQL Server Management Studio (SSMS), connect to your SQL Server. Open a new query window. Copy the SQL I posted into the query window and run it.

The results will display in a split window below the query window, either grid or text format. If in text format, highlight all the text EXCEPT the column heading and "------" separator at the top, and the "(n) rows affected" message at the bottom. If in grid format, click the grid heading to select all the rows, then right-click the grid heading and choose Copy. Then open a new query window and Paste into it.

Use your mouse to select/highlight one line at a time, then Run that line. Do this one line at a time for a bit to make sure the databases detach. Then select a few lines at a time. Do not select the same line multiple times, you might want to remove these lines after you execute them. If you make a mistake, just re-run the original query, it will automatically list available databases to be detached.

Newbie_SQL
Starting Member

5 Posts

Posted - 2012-05-23 : 14:22:32

Thanks so much but I am getting the following error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' FROM sys.databases WHERE name LIKE '.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string '

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-23 : 14:40:20

Ooooops, typo, this is fixed:

SELECT 'EXEC sp_detach_db '''' + name + '';' FROM sys.databases WHERE name LIKE '%NYMT%'

Sorry about that.

Newbie_SQL
Starting Member

5 Posts

Posted - 2012-05-23 : 14:43:53

Getting closer but this is what the results look like.

EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';
EXEC sp_detach_db '' + name + ';

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-23 : 14:46:50

Mental note for me: make sure to TEST the code I post BEFORE I post it:

SELECT 'EXEC sp_detach_db ''' + name + ''';' FROM sys.databases WHERE name LIKE '%NYMT%'

Newbie_SQL
Starting Member

5 Posts

Posted - 2012-05-23 : 15:13:29

That worked perfectly!!

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources