Πώς κάνουμε μαζικά detach / attach βάσεις δεδομένων στον SQL Server

• Σχετικά με
• τελευταίες δημοσιεύσεις

Stratos Matzouranis

Το ταξίδι άρχισε το 2008 όπου ξεκίνησα να εργάζομαι επίσημα στον τομέα της τεχνολογίας πληροφορικής (IT). Ξεκινώντας το πρώτο εξάμηνο της σχολής κατάλαβα μια ιδιαίτερη έλξη προς τις βάσεις δεδομένων και τους αυτοματισμούς.Έχω ασχοληθεί με βάσεις δεδομένων όπως Microsoft SQL Server / Oracle Database, ανάλυση δεδομένων και αυτοματισμούς με τη χρήση γραμμής εντολών (CLI), Visual Basic for Applications και Python. Μέσα από τα χρόνια εμπειρίας εξέλιξα αυτές τις δυνατότητες ώστε να μπορώ να κάνω τη ζωή μου πιο εύκολη. Για μένα ο σκοπός του κάθε IT guy αλλά και κάθε υπαλλήλου γραφείου είναι το να έχει τις γνώσεις ώστε μέσω εργαλείων να μπορεί να δουλεύει λίγο αλλά να παράγει πολύ. Μέσα από αυτό τον ιστότοπο του DataPlatform.gr προσπαθώ να προσφέρω γνώσεις και να προτείνω λύσεις σε καθημερινά προβλήματα.

Πιστοποιήσεις:

Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

• Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
• Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
• Πώς κάνουμε μαζικά detach / attach βάσεις δεδομένων στον SQL Server - 2 Φεβρουάριος 2024

Κάποιες φορές θα χρειαστεί να μετακινήσουμε βάσεις δεδομένων στον SQL Server. Αυτή η διαδικασία γίνεται και με backup / restore είτε αλλάζοντας το status της βάσης σε offline στο άρθρο όμως θα ασχοληθούμε τις διαδικασίες του detach και attach. Στο άρθρο θα δούμε ένα script που δημιουργεί τις εντολές που ολοκληρώνουν αυτή την εργασία.

Για ποιους λόγους χρειάζεται να γίνει

Ο συνήθης λόγος είναι για να μεταφέρουμε τη βάση σε διαφορετικό instance ή ακόμα και διαφορετικό server. Από εκεί και πέρα μπορούμε να μεταφέρουμε τα αρχεία της βάσης σε ξεχωριστό δίσκο λόγω επιδόσεων και χώρων. Τέλος μπορούμε να κάνουμε attach μία βάση σε ένα instance πιο πρόσφατης έκδοσης αναβαθμίζοντας την.

Τι πρέπει να προσέξουμε

Με αυτή τη διαδικασία έχουμε κάποιους περιορισμούς. Ας αναλύσουμε τα πιο βασικά:

• Θα πρέπει μετά την μεταφορά των βάσεων να δημιουργηθούν τα logins και να συνδεθούν με τους χρήστες της κάθε βάσεις διότι χάνετε η σύνδεση αυτή κατά την μεταφορά.
• Η διαδικασία του attach γίνεται μόνο σε instance ίδιας έκδοσης με αυτή που έγινε το detach ή νεότερης
• Αν γίνει η βάση attach σε νεότερη έκδοση το compatibility level δεν αλλάζει από μόνο του πάρα την εκτέλεση της εντολής alter database compatibility.
• Όταν μεταφέρουμε μια βάση δεν μεταφέρονται και τα jobs μαζί της τα οποία πάντα είναι αποθηκευμένα στην συστεμική βάση msdb.

Η διαδικασία

Ο στόχος του script είναι να παράγει την εντολές για να κάνουμε detach και attach. Εκτελούμε τις εντολές detach στο instance με τις βάσεις που θέλουμε να μεταφέρουμε. Αφου ολοκληρωθεί μεταφέρουμε τα αρχεία των βάσεων. Τέλος συνδεόμαστε στο instance που θέλουμε να τις κάνουμε attach και εκτελούμε τα attach εντολές.

Παράδειγμα εντολών:

EXEC sp_detach_db @dbname = 'db_test' , @skipchecks = 'true'; 

EXEC sp_attach_db @dbname = 'db_test'     ,@filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL19\MSSQL\DATA\ddd.mdf'     ,@filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL19\MSSQL\DATA\fff_log.ldf' 

Το script

Πριν την εκτέλεση του θα μπορούμε να ορίσουμε αν θα γίνει το attach στο ίδιο drive με την παράμετρο @Drive_New, αν θέλουμε μόνο μια συγκεκριμένη βάση με την παράμετρο @dbname και τέλος αν θέλουμε ή όχι και τις συστημικές βάσεις με τη παράμετρο @whichdb:

USE [master];
GO
DECLARE @database NVARCHAR(200) ,
    @cmd NVARCHAR(1000) ,
    @detach_cmd NVARCHAR(4000) ,
    @attach_cmd NVARCHAR(4000) ,
    @file NVARCHAR(1000) ,
    @i INT ,
	@whichdb INT=0,
	@dbname nvarchar(200)='%%',
	@Drive_Now nvarchar(10)='',
	@Drive_New nvarchar(10)='';
create table #tempattach(attach_script varchar(max));
create table #tempdetach(detach_script varchar(max));

-- set 4 for only userdbs, 0 for all dbs 
set @whichdb  = 4

--if u want to attach db on  new drive with same path uncomment with  the correct drive letters
--set @Drive_Now = 'C:\' set @Drive_New = 'Z:\'

--uncomment if u pick only one DBname
--set @dbname = '%DBA%'

DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
    SELECT  RTRIM(LTRIM([name]))
    FROM    sys.databases
    WHERE   database_id > @whichdb
	and name like @dbname;

OPEN dbname_cur

FETCH NEXT FROM dbname_cur INTO @database

WHILE @@FETCH_STATUS = 0 
    BEGIN
        SELECT  @i = 1;

        SET @attach_cmd = 
            'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR(10);
      -- Change skip checks to false if you want to update statistics before you detach.
        SET @detach_cmd =
            '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 @attach_cmd = @attach_cmd + '    ,@filename'
                    + CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + ''''
                    + CHAR(10);
                SET @i = @i + 1;

                FETCH NEXT FROM dbfiles_cur INTO @file
            END

        CLOSE dbfiles_cur;

        DEALLOCATE dbfiles_cur; 
       insert into #tempattach values(REPLACE(@attach_cmd,@Drive_Now,@Drive_New));
	   insert into #tempdetach values(@detach_cmd);

        FETCH NEXT FROM dbname_cur INTO @database
    END

CLOSE dbname_cur;

DEALLOCATE dbname_cur; 

select * from #tempdetach
select *  from #tempattach

drop table #tempattach
drop table #tempdetach

Με το που εκτελέσουμε θα παραχθούν οι εντολές για το detach και attach όλων των βάσεων:

Πηγές:

• Database Detach and Attach (SQL Server)