Trwałe odłączanie bazy danych

Jeśli baza danych zostanie trwale odłączona od instancji, czy istnieją jakieś zadania czyszczenia, które należy wykonać?

Jeśli odłączysz bazę danych od instancji, będziesz musiał wykonać usunięcie pliku na poziomie systemu operacyjnego. Bezpieczniejszym podejściem jest zamiast tego usunięcie bazy danych.

Sugeruję wykonanie ostatecznej kopii zapasowej bazy danych po przejściu do trybu Tylko do odczytu (ponieważ zapewni to brak aktywności podczas tworzenia kopii zapasowej), po czym usuń ją z systemu za pomocą polecenia Drop Database .

Pełny zestaw poleceń wyglądałby podobnie do następującego:

-- Use master db to ensure you don't have an active connection to the db you wish to affect
USE [master]
GO

-- This will kill any active transactions, but will force the database into a Read-Only state
ALTER DATABASE [db_name] SET READ_ONLY WITH ROLLBACK IMMEDIATE
GO

BACKUP DATABASE [db_name] -- Fill in more options here or use the UI to take a backup if you chooose
GO

-- This will kick out all connections from the database allowing you to drop it.
ALTER DATABASE [db_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- Drop the database (which automatically removes the files from the OS)
DROP DATABASE [db_name]
GO

Następnie poszukaj zadań, które uruchamiały skrypty w bazie danych. Proponuję tylko poczekać, aby zobaczyć, co się nie powiedzie (po czym można wykonać skrypt / usunąć zadanie), ponieważ istnieje wiele sposobów odwoływania się do bazy danych (nie wszystkie są łatwe do zidentyfikowania).

Na koniec będziesz chciał usunąć wszystkich użytkowników z instancji, którzy mieli dostęp tylko do tej bazy danych. Ten skrypt powinien identyfikować tych użytkowników, chociaż wersja Maxa jest znacznie czystsza (nie zdawałem sobie sprawy, że opublikował podejście, dopóki nie zredagowałem mojej odpowiedzi, aby uwzględnić to):

DECLARE @ExecString NVARCHAR (4000)

-- Create Empty Table in a very lazy manner
SELECT  name, principal_id, CAST('' AS NVARCHAR(128)) as database_name
INTO ##tmp_AllDBUsers
FROM sys.server_principals
WHERE 1 = 2

-- Declare Cursor to iterate through all DBs on the instance
DECLARE dbCursor CURSOR
FOR
        SELECT name
        FROM sys .databases


DECLARE @name NVARCHAR (128)
OPEN dbCursor
FETCH NEXT FROM dbCursor
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @ExecString = 
    'USE [' + @name + '];
    INSERT INTO ##tmp_AllDBUsers
    SELECT sp.name, sp.principal_id, DB_NAME()
    FROM sys.server_principals sp INNER JOIN sys.database_principals dp
        ON sp.sid = dp.sid'

    EXEC(@ExecString)

    FETCH NEXT FROM dbCursor
    INTO @name
END

-- Close and deallocate the cursor because you've finished traversing all it's data
CLOSE dbCursor
DEALLOCATE dbCursor

-- Show all logins that do not belong to a server-level role nor have access to any databases
SELECT sp.*
FROM sys.server_principals sp LEFT JOIN ##tmp_AllDBUsers adu
    ON sp.principal_id = adu.principal_id
WHERE adu.principal_id IS NULL
    AND sp.principal_id NOT IN (SELECT member_principal_id
                            FROM sys.server_role_members)
    AND TYPE IN ('S', 'U', 'G')

-- cleanup
DROP TABLE ##tmp_AllDBUsers

Poparłem odpowiedź Johna; Chciałbym tylko dodać kilka szczegółów na temat innych przedmiotów, które możesz chcieć posprzątać.

1. Zadania i alerty agenta programu SQL Server mogą odwoływać się do bazy danych. Ich wyczyszczenie zapobiegnie zgłaszaniu niepotrzebnych błędów.

2. Usuń wszelkie dane logowania utworzone specjalnie dla bazy danych. Poniższy T-SQL identyfikuje możliwe nazwy kandydatów, które możesz zbadać, aby sprawdzić, czy są one używane. Kod identyfikuje dane logowania, do których nie odwołuje się żadna baza danych.

   DECLARE @cmd nvarchar(max);
   SET @cmd = '    SELECT sp.sid
       FROM master.sys.server_principals sp
   ';
   SELECT @cmd = @cmd + '  EXCEPT 
       SELECT dp.sid
       FROM ' + QUOTENAME(d.name) + '.sys.database_principals dp
   '
   FROM sys.databases d
   WHERE d.[state] <> 6; --ignore offline DBs
   
   SET @cmd = 'SELECT spr.*
   FROM (
   ' + @cmd + '
   ) src
       INNER JOIN master.sys.server_principals spr
           ON src.sid = spr.sid
   WHERE spr.type <> ''R''
       AND spr.name NOT LIKE ''%##MS_%''
       AND spr.name NOT LIKE ''NT %''
       AND NOT EXISTS (
           SELECT 1
           FROM sys.server_role_members srm
           WHERE srm.member_principal_id = spr.principal_id
               )
   ORDER BY spr.name;
   ';
   EXEC sys.sp_executesql @cmd;

3. Dla tej bazy danych mogą istnieć urządzenia do tworzenia kopii zapasowych. Chociaż ich usunięcie nie jest absolutnie konieczne, jeśli nie są używane, powinny one wyeliminować potencjalne przyszłe zamieszanie.

4. Wyzwalacze na poziomie serwera mogą odwoływać się do bazy danych.

5. Poszukaj planów konserwacji odwołujących się do bazy danych - zakończą się one niepowodzeniem, jeśli nie zostaną zaktualizowane w celu usunięcia brakującej bazy danych.

Wszystkie główne punkty zostały już omówione. Poniżej są moje 2 centy:

Odłączenie bazy danych nigdy nie jest trwałym rozwiązaniem, ponieważ miało służyć do przenoszenia plików bazy danych na serwerze lub na inny serwer. Trwałe usunięcie bazy danych można wykonać za pomocą opcji Usuń w SSMS lub polecenia bazy danych DROP, jak wspomniano powyżej.

Zwykle bazy danych celowo utrzymywane w trybie offline i generujące alerty są tymi, które odłączamy i przechowujemy do momentu, aż będzie można je trwale usunąć (usunąć).

Zadanie przed odłączeniem: Uruchom, sp_helpdb dbnameaby poznać lokalizacje plików.

Zadania czyszczenia:

1. Usuń pliki mdf, ndf i ldf bazy danych z lokalizacji, w których się znajdują.
2. Stare pliki kopii zapasowej bazy danych należy usunąć lub przenieść na inny serwer, biorąc pod uwagę okres przechowywania.

Oprócz Loginów, zadań agentów, wyzwalaczy i wspomnianych już przez Max punktów, na te 2 można również spojrzeć.