script to backup and detach a db

• wdolby

  Old Hand

  Points: 376

  More actions

  June 1, 2012 at 6:31 am

  #1495823

  Here is a quick script to detach all Test_ databases in my instance. If you need to detach everything, use the commented line in the cursor. You can add any other databases you need to keep attached to the where clause on that line (ReportServer or any others).

  As others have already suggested, maybe run this from sqlcmd against multiple instances.

  CREATE DATABASE Test1;

  GO

  CREATE DATABASE Test2;

  GO

  CREATE DATABASE Test3;

  GO

  CREATE DATABASE Test4;

  GO

  DECLARE @SQL nvarchar(255)

  DECLARE @Detach sysname

  DECLARE Detach CURSOR FOR

  SELECT name FROM sys.databases WHERE name LIKE 'Test_'

  --Replace cursor with this statement to detach everything except system databases:

  --SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

  OPEN Detach

  FETCH NEXT FROM Detach INTO @Detach

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SET @SQL = 'EXEC sp_detach_db '''+@Detach+''';'

  EXECUTE sp_executesql @SQL

  FETCH NEXT FROM Detach INTO @Detach

  END

  CLOSE Detach

  DEALLOCATE Detach;

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  June 1, 2012 at 2:11 pm

  #1496079

  Before you just go detaching databases you may want to record their physical and logical filenames. Do this using

  select 'Logical file ''' + name + ''' from database ' +

  quotename(DB_NAME(database_id)) + ' is located at path\file '''

  + physical_name + ''''

  from sys.master_files where database_id > 4

  You could then use the cursor provided bywdolby above to loop through user databases and detach them, change the cursor definition to the following

  DECLARE Detach CURSOR FOR

  SELECT name FROM sys.databases WHERE database_id > 4

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉