February 5, 2013 at 1:57 am
Comments posted to this topic are about the item Get Detach or Attach all user databases script
May 2, 2016 at 4:08 pm
Lots of good scripts Harsha.
November 14, 2018 at 5:14 am
Thanks Harsha, a great script (5 stars awarded).
This saved me a lot of effort as I had to detach and then reattach about 50 databases on two instances as the collation needed to be changed on the system databases.
I updated the script slightly so it would output in the same format that my version of SQL Server generates
USE [master];
GO
DECLARE @database NVARCHAR(200) ,
@cmd NVARCHAR(1000) ,
@detach_cmd NVARCHAR(4000) ,
@attach_cmd NVARCHAR(4000) ,
@file NVARCHAR(1000) ,
@i INT ,
@DetachOrAttach BIT;
SET @DetachOrAttach = 1;
-- 1 Generates Detach Script
-- 0 Generates Attach Script
PRINT 'USE [master]'
DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
SELECT RTRIM(LTRIM([name]))
FROM sys.databases
WHERE database_id > 4
and name <> 'distribution';
-- No system databases
OPEN dbname_cur
FETCH NEXT FROM dbname_cur INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @i = 1;
SET @attach_cmd = '-- ' + QUOTENAME(@database) + '
CREATE DATABASE ' + QUOTENAME(@database) + ' ON
'
-- Change skip checks to false if you want to update statistics before you detach.
SET @detach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10)
+ 'ALTER DATABASE ' + QUOTENAME(@database) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC sp_detach_db @dbname = ''' + @database + ''';
GO
'
-- Get a list of files for the database
DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
SELECT physical_name
FROM sys.master_files
WHERE database_id = DB_ID(@database)
ORDER BY [file_id];
OPEN dbfiles_cur
FETCH NEXT FROM dbfiles_cur INTO @file
WHILE @@FETCH_STATUS = 0
BEGIN
SET @attach_cmd += '( FILENAME = N' + QUOTENAME(@file,'''') + ' )'
FETCH NEXT FROM dbfiles_cur INTO @file
IF @@FETCH_STATUS = 0
SET @attach_cmd += ',' + CHAR(13) + CHAR(10)
END
SET @attach_cmd += '
FOR ATTACH
GO
'
CLOSE dbfiles_cur;
DEALLOCATE dbfiles_cur;
IF ( @DetachOrAttach = 0 )
BEGIN
-- Output attach script
PRINT @attach_cmd;
END
ELSE -- Output detach script
PRINT @detach_cmd;
FETCH NEXT FROM dbname_cur INTO @database
END
CLOSE dbname_cur;
DEALLOCATE dbname_cur;
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy