Πώς μπορούμε να κάνουμε μαζικά backup/restore των βάσεων δεδομένων στον 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

Πολλές φορές μπορεί να χρειαστεί να κάνουμε backup πολλές μικρές βάσεις δεδομένων και να τις μεταφέρουμε σε κάποιο άλλο σύστημα ή να τις επαναφέρουμε στο ίδιο. Η διαδικασία του backup/restore της κάθε βάσης μία μία θα είναι χρονοβόρα και μπορεί να οδηγήσει σε λάθη. Σε αυτό το άρθρο θα δούμε ένα T-SQL script που έχω γράψει για αυτή τη δουλειά και πως χρησιμοποιείται.

Η πληροφορία που χρειαζόμαστε βρίσκεται στα συστημικά views master_files και databases. Για να εξάγουμε αυτή τη πληροφορία θα χρειαστούμε να κάνουμε τη χρήση cursor ή Common Table Expression (CTE). Το συγκεκριμένο script το έχω γράψει με τη χρήση CTE.

Στις νεότερες εκδόσεις από SQL Server 2017 και έπειτα, με τη function STRING_AGG μπορούμε ενώσουμε σε μια γραμμή όλα τα paths για τα files που χρειάζονται για το restore. Επειδή όμως θέλουμε το script να λειτουργεί και σε παλαιότερες εκδόσεις θα χρησιμοποιήσουμε την function stuff μαζί με Xml PATH.

Το μόνο που θα χρειαστεί να πειράξουμε είναι τις παραμέτρους backup_loc, rest_dbf, rest_log και skipped dbs (που βρίσκεται στο τέλος του script). Σε αυτές τις παραμέτρους ορίζουμε σε ποιά τοποθεσία να τοποθετηθούν τα backup files και που θα γίνουν restore. Επίσης έχουμε την δυνατότητα να εξαιρέσουμε βάσεις απο την διακασία όπως τις συστημικές που τις έχω εξαιρέσει ήδη.

Το script

DECLARE @date CHAR(8)
SET @date = (SELECT CONVERT(char(8), GETDATE(), 112))

DECLARE @backup_loc VARCHAR(125)  
SET @backup_loc = 'X:\backups\'  -- Σε ποιά τοποθεσία θα παρθούν τα backups

DECLARE @rest_dbf VARCHAR(125)
SET @rest_dbf = 'D:\Databases\'   -- Σε ποιά τοποθεσία θα γίνουν restore τα datafiles

DECLARE @rest_log VARCHAR(125)
SET @rest_log = 'L:\Logfiles\';  -- Σε ποια τοποθεσία θα γίνουν restore τα logfiles


WITH CTE ( DatabaseName, Npath )
          AS ( SELECT DISTINCT
                        DB_NAME(database_id) ,
                        STUFF((SELECT   ' ' + CHAR(13)+', MOVE ''' + name + ''''
                                        + CASE Type
                                            WHEN 0 THEN ' TO ''' +@rest_dbf
                                            ELSE ' TO ''' +@rest_log
                                          END
                                        + REVERSE(LEFT(REVERSE(physical_name),
                                                       CHARINDEX('\',
                                                              REVERSE(physical_name),
                                                              1) - 1)) + ''''
                               FROM     sys.master_files sm1
                               WHERE    sm1.database_id = sm2.database_ID
                        FOR   XML PATH('') ,
                                  TYPE).value('.', 'varchar(max)'), 1, 1, '') AS Npath
               FROM     sys.master_files sm2
  )
--select * from CTE
SELECT
	'BACKUP DATABASE ' + name + ' TO DISK = ''' + @backup_loc + '' + name + '_' + @date + '.bak'' WITH COMPRESSION, COPY_ONLY, STATS=5' as Backup_Commands_onSource,
	'RESTORE DATABASE '+ name + ' FROM DISK = ''' + @backup_loc + '' + name + '_' + @date + '.bak'' WITH RECOVERY, REPLACE, STATS=5 ' + CTE.Npath as Restore_Commands_onTarget
FROM sys.databases d
	INNER JOIN CTE ON d.name = cte.databasename
WHERE d.name not in('master','tempdb','model','msdb') --skipped DBs
GO

Το αποτέλεσμα

Τα αποτελέσματα που δημιουργούνται τα χρησιμοποιούμε για την διαδικασία. Την πρώτη στήλη στο σύστημα που θα κάνουμε τα backups και αφού μεταφέρουμε τα αρχεία (τα .bak που δημιουργήθηκαν από τα backups), εκτελούμε στο άλλο σύστημα την δεύτερη στήλη των restore statements.

Το παράδειγμα του statement για backup της πρώτης βάσης.

BACKUP DATABASE bi_test TO DISK = 'X:\backups\bi_test_20200825.bak' WITH COMPRESSION, COPY_ONLY, STATS=5

Το παράδειγμα του statement για restore της πρώτης βάσης.

RESTORE DATABASE bi_test FROM DISK = 'X:\backups\bi_test_20200825.bak' WITH RECOVERY, REPLACE, STATS=5  , MOVE 'bi_test' TO 'D:\Databases\bi_test.mdf'  , MOVE 'bi_test_log' TO 'L:\Logfiles\bi_test_log.ldf'  , MOVE 'bi_test2' TO 'D:\Databases\bi_test2.ndf'