-
Title
How to Remove Previously Mounted Databases from the Local SQL Instance on the Core -
Description
Sometimes, for various reasons, the databases mounted on the local instance of the Core are not dismounted. They show as being mounted in recovery mode (the word recovery is shown in parenthesis). The recovery points hosting these databases have already been you want to detach these databases.
-
Resolution
You need to be able to connect to the Core local SQL instance with SQL Server Management Studio. If you caught the issue when only a few database are mounted, you can easily dismount them by taking them offline first (connect to the SQL management Studio, right-click on each database, select all tasks, and click take database offline). Once the database is off line you can detach it (connect to the SQL management Studio, right-click on each database, select all tasks, click detach).
However, in most cases there are many instances (hundreds or even thousands) of mounted databases that need to be dismounted and it is almost impossible to do it manually. To solve this issue, a two steps SQL script has been prepared.
Running it in SQL Management Studio will generate another script in the results Window. This second script contains the “take offline” & “detach” Transact SQL commands for each user database, in the format
– [AA-SomeDatabaseName-bff5ad41-1af6-4ce3-afa7-b6bc43be4091]
ALTER DATABASE “AA-SomeDatabaseName-bff5ad41-1af6-4ce3-afa7-b6bc43be4091″ SET OFFLINE;
EXEC sp_detach_db @dbname = ‘AA-SomeDatabaseName-bff5ad41-1af6-4ce3-afa7-b6bc43be4091′ , @skipchecks = ‘true’;
Please note the usage of simple and double quotes.
The generated script can be manually edited to remove any legitimate databases if you have any databases in use on the Core server. Once this is done, copying the text window containing the generate script, pasting it into a new Sql Query window and executing it will detach the desired databases. Please note that you may have to disconnect from the database engine and connect back in order to see the results as, in some cases, after executing the script, the refresh does not work.
USE [master];
GO
DECLARE @database NVARCHAR(200) ,
@cmd NVARCHAR(1000) ,
@detach_cmd NVARCHAR(4000) ,
@attach_cmd NVARCHAR(4000) ,
@file NVARCHAR(1000) ,
@i INT
DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
SELECT RTRIM(LTRIM([name]))
FROM sys.databases
WHERE database_id > 4;
– No system databases
OPEN dbname_cur
FETCH NEXT FROM dbname_cur INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @i = 1;
– Change skip checks to false if you want to update statistics before you detach.
SET @detach_cmd = ‘– ‘ + QUOTENAME(@database) + CHAR(10)
+ ‘ALTER DATABASE “‘+ @database + ‘” SET OFFLINE;’ + CHAR(10)
+ ‘EXEC sp_detach_db @dbname = ”’ + @database
+ ”’ , @skipchecks = ”true”;’ + CHAR(10);
– 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 @i = @i + 1;
FETCH NEXT FROM dbfiles_cur INTO @file
END
CLOSE dbfiles_cur;
DEALLOCATE dbfiles_cur;
PRINT @detach_cmd;
FETCH NEXT FROM dbname_cur INTO @database
END
CLOSE dbname_cur;
DEALLOCATE dbname_cur;
-
Attachments