Comment déplacer des fichiers de base de données SQL Server vers un nouvel emplacement – MlakarTechTalk – Bien choisir son serveur d impression

Avez-vous déjà eu à déplacer des bases de données et leurs fichiers? Une migration de base de données implique des temps d'arrêt et les erreurs commises peuvent certainement gâcher votre journée. Vous ne voulez pas être en pleine migration et ne pas savoir quoi faire. Poursuivez votre lecture pour apprendre à déplacer des fichiers de base de données SQL Server et à visionner des démos. Pourquoi aurions-nous besoin / envie de déplacer des fichiers de base de données? Il existe certains scénarios dans lesquels une migration est effectuée.

Le disque est à court d'espace

Parfois, lorsqu'un administrateur dort au travail, les disques pleins passent inaperçus jusqu'à ce qu'il soit trop tard. Dans ces cas, certaines bases de données peuvent être déplacées vers un autre disque, volume, point de montage, etc.

Déménagement dans un nouvel entrepôt

Un nouvel espace de stockage pour la base de données arrive et nous devons déplacer certaines (ou toutes les) bases de données doivent être déplacées.

Séparer les fichiers de base de données

Suivre les meilleures pratiques pour séparer les fichiers de base de données – fichier de données (mdf), journal des transactions (ldf), tempdb, sauvegardes, fichiers de trace, etc. J'ai écrit à ce sujet ici et plus précisément ici dans la section intitulée «Configuration du disque».

La base de données sera indisponible pendant cette opération, nous devons donc en informer nos utilisateurs finaux. Prenez en compte les ramifications si une application utilise la base de données. Nous voudrons peut-être arrêter les services d'application ou entreprendre une autre action personnalisée lors du déplacement. Planifiez à l'avance avant de commencer le travail. Sachez ce que vous allez faire avant de le faire. Si vous pouvez tester votre méthode sur une base de données de laboratoire ou de développement, cela vous aidera également.

Une fois que nous avons le plan de match, nous pouvons commencer à le mettre en œuvre. Regardons quelques détails. Migration – Manières générales de déplacer des fichiers de base de données Il existe quelques méthodes que nous pouvons employer pour faire ce travail:

Détacher la base de données / Déplacer les fichiers / Attacher la base de données Définir la base de données hors connexion / Déplacer les fichiers / Modifier la base de données Modifier le fichier / Définir en ligne Sauvegarde / restauration ailleurs

Voici quelques bases de connaissances Microsoft pour vous aider tout au long du processus.

Joindre une base de données – explique CREATE DATABASE FOR ATTACH sp_attach_db – obsolète! Veuillez utiliser la méthode ci-dessus pour attacher Créer une base de données – plus d'informations sur la clause FOR ATTACH Déplacer les bases de données utilisateur – met en évidence ALTER DATABASE MODIFY FILE sp_detach_db – mises en garde concernant le détachement

Regardons en profondeur dans chaque sens. Méthode 1: détacher la base de données / déplacer des fichiers / créer une base de données pour l'attachement Nous créons quelques bases de données pour le tester.

UTILISER le maître; ALLER / ***************************************        Créer des exemples de bases de données **************************************** / –DB1 CREATE DATABASE [DB1] SUR PRIMAIRE (NOM = N'DB1 ', NOMFICHIER = N'C: Program Files Serveur SQL Microsoft MSSQL13.MSSQLSERVER MSSQL DATA DB1.mdf', TAILLE = 8192 Ko, FILEGROWTH = 65536 Ko)  SE CONNECTER (NAME = N'DB1_log ', FILENAME = N'C: Program Files Microsoft Serveur SQL MSSQL13.MSSQLSERVER MSSQL DATA DB1_log.ldf', SIZE = 8192 Ko, FILEGROWTH = 65536 Ko) ALLER –DB2 CREATE DATABASE [DB2] SUR PRIMAIRE (NOM = N'DB2 ', NOMFICHIER = N'C: Program Files Microsoft Serveur SQL MSSQL13.MSSQLSERVER MSSQL DATA DB2.mdf', SIZE = 8192 Ko, FILEGROWTH = 65536 Ko)  SE CONNECTER (NAME = N'DB2_log ', FILENAME = N'C: Program Files Microsoft Serveur SQL MSSQL13.MSSQLSERVER MSSQL DATA DB2_log.ldf', SIZE = 8192 Ko, FILEGROWTH = 65536 Ko) ALLER –DB3 CREATE DATABASE [DB3] SUR PRIMAIRE (NOM = N'DB3 ', NOMFICHIER = N'C: Program Files Microsoft Serveur SQL MSSQL13.MSSQLSERVER MSSQL DATA DB3.mdf', TAILLE = 8192 Ko, FILEGROWTH = 65536 Ko)  SE CONNECTER (NOM = N'DB3_log ', NOMFICHIER = N'C: Program Files Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA DB3_log.ldf', SIZE = 8192 Ko, FILEGROWTH = 65536 Ko) ALLER

1 2 3 4 5 6 7 8 9 dix 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

UTILISATION maîtriser; ALLER

/ *************************************** Créer des exemples de bases de données **************************************** / –DB1 CRÉER BASE DE DONNÉES [[[[DB1] SUR PRIMAIRE ( PRÉNOM = N'DB1', NOM DE FICHIER = N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA DB1.mdf' , TAILLE = 8192 Ko , FILEGROWTH = 65536KB ) BÛCHE SUR ( PRÉNOM = N'DB1_log', NOM DE FICHIER = N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA DB1_log.ldf' , TAILLE = 8192 Ko , FILEGROWTH = 65536KB ) ALLER

–DB2 CRÉER BASE DE DONNÉES [[[[DB2] SUR PRIMAIRE ( PRÉNOM = N'DB2', NOM DE FICHIER = N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA DB2.mdf' , TAILLE = 8192 Ko , FILEGROWTH = 65536KB ) BÛCHE SUR ( PRÉNOM = N'DB2_log', NOM DE FICHIER = N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA DB2_log.ldf' , TAILLE = 8192 Ko , FILEGROWTH = 65536KB ) ALLER

–DB3 CRÉER BASE DE DONNÉES [[[[DB3] SUR PRIMAIRE ( PRÉNOM = N'DB3', NOM DE FICHIER = N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA DB3.mdf' , TAILLE = 8192 Ko , FILEGROWTH = 65536KB ) BÛCHE SUR ( PRÉNOM = N'DB3_log', NOM DE FICHIER = N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA DB3_log.ldf' , TAILLE = 8192 Ko , FILEGROWTH = 65536KB ) ALLER

Vérifier les informations du fichier de base de données.

–Vérifier les informations du fichier DB sélectionnez * parmi DB1.sys.database_files; select * from DB2.sys.database_files; select * from DB3.sys.database_files;

–Vérifier les informations du fichier DB sélectionner * de DB1.sys.fichiers_base_de_données; sélectionner * de DB2.sys.fichiers_base_de_données; sélectionner * de DB3.sys.fichiers_base_de_données;

Il ressemblera à ceci:

Maintenant, nous faisons certaines choses préliminaires avant de déplacer des fichiers. Nous devons spécifier les bases de données que nous voulons déplacer. C'est une façon très simpliste de le faire – codé en dur dans une table temporaire.

/ ***************************************        Pré étapes **************************************** / –voir les bases de données que vous voulez déplacer SELECT * FROM sys.databases WHERE [name] IN ('DB1', 'DB2', 'DB3'); –définit les bases de données à détacher / attacher SÉLECTIONNER [name] INTO #DBsToMove FROM sys.databases WHERE [name] IN ('DB1', 'DB2', 'DB3'); –doit rassembler les informations sur le fichier AVANT de détacher la base de données SELECT database_id, [type_desc], [name], nom physique INTO #DBfiles FROM sys.master_files WHERE database_id IN (DB_ID ('DB1'), DB_ID ('DB2'), DB_ID ('DB3')); –check file configuration – pourrait vouloir copier ceci ailleurs pour garder des traces de notre travail SELECT * FROM #DBfiles;

1 2 3 4 5 6 7 8 9 dix 11 12 13 14 15 16 17 18

/ *************************************** Pré étapes **************************************** /

–Voir les bases de données que vous souhaitez déplacer SÉLECTIONNER * DE sys.bases de données OÙ [[[[prénom] DANS('DB1','DB2','DB3');

–définit les bases de données à détacher / attacher SÉLECTIONNER [[[[prénom] DANS #DBsToMove DE sys.bases de données OÙ [[[[prénom] DANS('DB1','DB2','DB3');

–doit rassembler les informations sur le fichier AVANT de détacher la base de données SÉLECTIONNER base_de_données, [[[[type_desc], [[[[prénom], nom_périphérique DANS #DBfiles DE sys.master_files OÙ base_de_données DANS(DB_ID('DB1'),DB_ID('DB2'),DB_ID('DB3'));

–check file configuration – pourrait vouloir copier ceci ailleurs pour garder des traces de notre travail SÉLECTIONNER * DE #DBfiles;

Maintenant que nous avons ce dont nous avons besoin, nous pouvons commencer à détacher les bases de données.

/ ************************************************ ***************************************** Méthode 1: détacher la base de données / déplacer des fichiers / créer une base de données pour l'attachement ************************************************* ***************************************** / –Détachez les bases de données DECLARE @DBName varchar (500); DÉCLARE @sql NVARCHAR (MAX); DECLARE curDetachDBs CURSOR FAST_FORWARD READ_ONLY POUR        SÉLECTIONNER [name] DE #DBsToMove OUVRIR curDetachDBs FETCH NEXT DE curDetachDBs DANS @DBName WHILE @@ FETCH_STATUS = 0 COMMENCER        CARTE D'IMPRESSION (13) + CHAR (10)        IMPRESSION '———————————————— ——— '        PRINT '-' + @DBName        IMPRESSION '———————————————— ——— '        SET @sql = 'ALTER DATABASE [‘ + @DBName + ‘] SET SINGLE_USER AVEC ROLLBACK IMMEDIATE; '        IMPRIMER @sql        EXEC sys.sp_executesql @sql        SET @sql = 'EXEC sp_detach_db' + @DBName + ',' 'true' ';'        IMPRIMER @sql        EXEC sys.sp_executesql @sql     FETCH NEXT DE curDetachDBs DANS @DBName FIN FERMER curDetachDBs DEALLOCATE curDetachDBs

1 2 3 4 5 6 7 8 9 dix 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

/ ************************************************ ***************************************** Méthode 1: détacher la base de données / déplacer des fichiers / créer une base de données pour l'attachement ************************************************* ***************************************** /

–Détachez les bases de données DÉCLARER @DBName varchar(500); DÉCLARER @sql NVARCHAR(MAX); DÉCLARER curDetachDBs LE CURSEUR AVANCE RAPIDE LECTURE SEULEMENT POUR SÉLECTIONNER [[[[prénom] DE #DBsToMove OUVRIR curDetachDBs FETCH SUIVANT DE curDetachDBs DANS @DBName TANDIS QUE @@FETCH_STATUS = 0 COMMENCER IMPRESSION CARBONISER(13) + CARBONISER(dix) IMPRESSION '—————————————————– ——– ' IMPRESSION '-' + @DBName IMPRESSION '—————————————————– ——– ' ENSEMBLE @sql = 'ALTER DATABASE['['[‘[‘ + @DBName + ']SET SINGLE_USER AVEC ROLLBACK IMMEDIATE; ' IMPRESSION @sql EXEC sys.sp_executesql @sql ENSEMBLE @sql = 'EXEC sp_detach_db' + @DBName + ',''vrai'';' IMPRESSION @sql EXEC sys.sp_executesql @sql FETCH SUIVANT DE curDetachDBs DANS @DBName FIN FERMER curDetachDBs DÉALLOCATE curDetachDBs

Vérifiez les modifications. Notez que les DB sont totalement partis. Vous ne les trouverez pas dans l'explorateur d'objets dans SSMS ou dans les tables système.

–verify changements SELECT * FROM sys.databases WHERE nom IN ('DB1', 'DB2', 'DB3'); – Les BD sont partis maintenant! c'est ce que détache fait et comment il est différent de la mise hors ligne

–verify changements SÉLECTIONNER * DE sys.bases de données OÙ prénom DANS('DB1','DB2','DB3'); – Les BD sont partis maintenant! c'est ce que détache fait et comment il est différent de la mise hors ligne

Vient maintenant le moment de déplacer les fichiers de base de données. Vous pouvez utiliser votre technique préférée pour déplacer des fichiers. J'aime utiliser robocopy dans un fichier .bat, mais il en existe beaucoup d'autres, tels que PowerShell, XCopy ou un clic droit pour couper / coller. Voici quelques actions de robocopy:

robocopy "C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DONNÉES" "G: MSSQL Data" "DB1.mdf" / L robocopy "C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DONNÉES" "G: MSSQL Data" "DB1_log.ldf" / L robocopy "C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DONNÉES" "G: MSSQL Data" "DB2.mdf" / L robocopy "C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DONNÉES" "G: MSSQL Data" "DB2_log.ldf" / L robocopy "C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DONNÉES" "G: MSSQL Data" "DB3.mdf" / L robocopy "C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DONNÉES" "G: MSSQL Data" "DB3_log.ldf" / L

robocopier "C: Program Files Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA" "G: MSSQL Data" "DB1.mdf" /L robocopier "C: Program Files Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA" "G: MSSQL Data" "DB1_log.ldf" /L robocopier "C: Program Files Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA" "G: MSSQL Data" "DB2.mdf" /L robocopier "C: Program Files Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA" "G: MSSQL Data" "DB2_log.ldf" /L robocopier "C: Program Files Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA" "G: MSSQL Data" "DB3.mdf" /L robocopier "C: Program Files Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA" "G: MSSQL Data" "DB3_log.ldf" /L

Le drapeau “/ L” est la première façon dont je gère les choses. En réalité, il ne copie rien, mais listera les fichiers. C'est une bonne pré-vérification avant d'exécuter un lot. Supprimez simplement le drapeau “/ L” et exécutez-le une fois vérifié. Voici la première partie de la sortie de l’exécution d’un fichier .bat avec des instructions robocopy:

Une fois que les fichiers sont physiquement déplacés vers le nouvel emplacement, nous pouvons continuer.

– attacher les bases de données DECLARE @targetMDF NVARCHAR (128); DECLARE @targetLDF NVARCHAR (128); DECLARE @DBName varchar (500); DÉCLARE @sql NVARCHAR (MAX); DECLARE @rowFile NVARCHAR (128); DECLARE @logFile NVARCHAR (128); DECLARE curAttachDBs CURSOR FAST_FORWARD READ_ONLY POUR        SELECT nom FROM #DBsToMove OUVRIR curAttachDBs FETCH NEXT FROM curAttachDBs INTO @DBName WHILE @@ FETCH_STATUS = 0 COMMENCER        SET @targetMDF = 'G: MSSQL Data';        SET @targetLDF = 'G: MSSQL Data';        CARTE D'IMPRESSION (13) + CHAR (10)        IMPRESSION '———————————————— ——— '        PRINT '-' + @DBName        IMPRESSION '———————————————— ——— '        SELECT @targetMDF + = '' + [name] + '.mdf' DE #DBfiles WHERE REMPLACE ([name], '_ log', '') = @DBName AND [type_desc] = 'ROWS';        SELECT @targetLDf + = '' + [name] + '.ldf' DE #DBfiles WHERE REMPLACE ([name], '_ log', '') = @DBName AND [type_desc] = 'LOG';         –attache la base de données        SELECT @sql = 'CREATE DATABASE [‘ + @DBName + ‘] SUR               (FILENAME = '' '                       + @targetMDF + '' '),               (FILENAME = '' '+ @targetLDF +' '')               POUR ATTACHER '        FROM #DBfiles;        PRINT @sql;        EXEC sys.sp_executesql @sql;        SET @targetMDF = '';        SET @targetLDF = '';     FETCH NEXT FROM curAttachDBs INTO @DBName FIN CLOSE curAttachDBs DEALLOCATE curAttachDBs

1 2 3 4 5 6 7 8 9 dix 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

– attacher les bases de données DÉCLARER @targetMDF NVARCHAR(128); DÉCLARER @targetLDF NVARCHAR(128); DÉCLARER @DBName varchar(500); DÉCLARER @sql NVARCHAR(MAX); DÉCLARER @rowFile NVARCHAR(128); DÉCLARER @fichier journal NVARCHAR(128); DÉCLARER curAttachDBs LE CURSEUR AVANCE RAPIDE LECTURE SEULEMENT POUR SÉLECTIONNER prénom DE #DBsToMove OUVRIR curAttachDBs FETCH SUIVANT DE curAttachDBs DANS @DBName TANDIS QUE @@FETCH_STATUS = 0 COMMENCER ENSEMBLE @targetMDF = 'G: MSSQL Data'; ENSEMBLE @targetLDF = 'G: MSSQL Data'; IMPRESSION CARBONISER(13) + CARBONISER(dix) IMPRESSION '—————————————————– ——– ' IMPRESSION '-' + @DBName IMPRESSION '—————————————————– ——– ' SÉLECTIONNER @targetMDF + = '' + [name] + '.mdf'FROM #DBfiles O REMPLACER ([name]'_bûche','') = @DBNAME AND [type_desc] = 'ROWS'; SELECT @targetLDf + = '' + [[[[prénom] + '.ldf' DE #DBfiles OÙ REMPLACER([[[[prénom],'_bûche','') = @DBName ET [[[[type_desc] = 'BÛCHE'; –attache la base de données SÉLECTIONNER @sql = 'CREATE DATABASE['['[‘[‘ + @DBName + ']SUR (FILENAME = ''' + @targetMDF + '''), (FILENAME = ''' + @targetLDF + ''') POUR ATTACHER ' DE #DBfiles; IMPRESSION @sql; EXEC sys.sp_executesql @sql; ENSEMBLE @targetMDF = ''; ENSEMBLE @targetLDF = ''; FETCH SUIVANT DE curAttachDBs DANS @DBName FIN FERMER curAttachDBs DÉALLOCATE curAttachDBs

Vérifiez les nouveaux emplacements de fichiers.

–Vérifier les informations du fichier DB sélectionnez * parmi DB1.sys.database_files; select * from DB2.sys.database_files; select * from DB3.sys.database_files;

–Vérifier les informations du fichier DB sélectionner * de DB1.sys.fichiers_base_de_données; sélectionner * de DB2.sys.fichiers_base_de_données; sélectionner * de DB3.sys.fichiers_base_de_données;

Auparavant, nos fichiers pointaient vers C: mais sont maintenant sur G: – la migration est terminée. Méthode 2: Définir la base de données hors connexion / déplacer des fichiers / modifier la base de données / définir en ligne Continuons dans ce sens, déplaçons les 3 exemples de bases de données que nous avons créées et que nous venons de revenir des disques G: à C:. Ici, nous commençons par définir nos bases de données et en définissant un nouveau chemin.

UTILISER le maître; ALLER / ***************************************        Pré étapes **************************************** / –voir les bases de données que vous voulez déplacer SELECT * FROM sys.databases WHERE [name] IN ('DB1', 'DB2', 'DB3'); –définit les bases de données à détacher / attacher SÉLECTIONNER [name] INTO #DBsToMove FROM sys.databases WHERE [name] IN ('DB1', 'DB2', 'DB3'); –doit rassembler les informations sur le fichier AVANT de détacher la base de données SELECT database_id, [type_desc], [name], nom physique INTO #DBfiles FROM sys.master_files WHERE database_id IN (DB_ID ('DB1'), DB_ID ('DB2'), DB_ID ('DB3')); alter table #DBfiles add NewPath varchar (500); – charger de nouveaux chemins DECLARE @targetMDF NVARCHAR (128); DECLARE @targetLDF NVARCHAR (128); SET @targetMDF = 'C: Fichiers de programme Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA '; SET @targetLDF = 'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA '; –SET @targetMDF = 'G: MSSQL Data '; –SET @targetLDF = 'G: MSSQL Data '; mettre à jour les fichiers définir NewPath = case quand [type_desc] = 'ROWS' puis @targetMDF + [name] + '.mdf' quand [type_desc] = 'LOG' puis @targetLDF + [name] + '.ldf' fin à partir des fichiers #DBfiles; –check file configuration – pourrait vouloir copier ceci ailleurs pour garder des traces de notre travail SELECT * FROM #DBfiles;

1 2 3 4 5 6 7 8 9 dix 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

UTILISATION maîtriser; ALLER

/ *************************************** Pré étapes **************************************** /

–voir les bases de données que vous voulez déplacer SÉLECTIONNER * DE sys.bases de données OÙ [[[[prénom] DANS('DB1','DB2','DB3');

–définit les bases de données à détacher / attacher SÉLECTIONNER [[[[prénom] DANS #DBsToMove DE sys.bases de données OÙ [[[[prénom] DANS('DB1','DB2','DB3');

–doit rassembler les informations sur le fichier AVANT de détacher la base de données SÉLECTIONNER base_de_données, [[[[type_desc], [[[[prénom], nom_périphérique DANS #DBfiles DE sys.master_files OÙ base_de_données DANS(DB_ID('DB1'),DB_ID('DB2'),DB_ID('DB3'));

modifier table #DBfiles ajouter Nouveau chemin varchar(500);

– charger de nouveaux chemins DÉCLARER @targetMDF NVARCHAR(128); DÉCLARER @targetLDF NVARCHAR(128); ENSEMBLE @targetMDF = 'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA '; SET @targetLDF = 'C:Programme Des dossiersMicrosoft SQL ServeurMSSQL13.MSSQLSERVERMSSQLLES DONNÉES'; –SET @targetMDF = 'G: MSSQL Data '; –SET @targetLDF = 'G: MSSQL Data ';

mettre à jour des dossiers ensemble Nouveau chemin = Cas quand [[[[type_desc] = 'ROWS' puis @targetMDF + [[[[prénom] + '.mdf' quand [[[[type_desc] = 'BÛCHE' puis @targetLDF + [[[[prénom] + '.ldf' fin de #DBfiles des dossiers;

–check file configuration – pourrait vouloir copier ceci ailleurs pour garder des traces de notre travail SÉLECTIONNER * DE #DBfiles;

Voici le résultat de #DBFiles:

Maintenant, au lieu de vous détacher comme dans l'exemple précédent, plaçons simplement les bases de données hors ligne.

/ ************************************************ ***************************************** Méthode 2: Définir la base de données hors connexion / déplacer des fichiers / modifier la base de données / définir en ligne ************************************************* ***************************************** / –set DBs hors ligne DECLARE @DBName varchar (500); DÉCLARE @sql NVARCHAR (MAX); DECLARE curDetachDBs CURSOR FAST_FORWARD READ_ONLY POUR        SÉLECTIONNER [name] DE #DBsToMove OUVRIR curDetachDBs FETCH NEXT DE curDetachDBs DANS @DBName WHILE @@ FETCH_STATUS = 0 COMMENCER        CARTE D'IMPRESSION (13) + CHAR (10)        IMPRESSION '———————————————— ——— '        PRINT '-' + @DBName        IMPRESSION '———————————————— ——— '        SET @sql = 'ALTER DATABASE [‘ + @DBName + ‘] SET OFFLINE; '        IMPRIMER @sql        EXEC sys.sp_executesql @sql     FETCH NEXT DE curDetachDBs DANS @DBName FIN FERMER curDetachDBs DEALLOCATE curDetachDBs –verify changements SELECT * FROM sys.databases WHERE nom IN ('DB1', 'DB2', 'DB3'); – Les BD sont toujours présentes – contrairement à détachement

1 2 3 4 5 6 7 8 9 dix 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

/ ************************************************ ***************************************** Méthode 2: Définir la base de données hors connexion / déplacer des fichiers / modifier la base de données / définir en ligne ************************************************* ***************************************** /

–set DBs hors ligne DÉCLARER @DBName varchar(500); DÉCLARER @sql NVARCHAR(MAX); DÉCLARER curDetachDBs LE CURSEUR AVANCE RAPIDE LECTURE SEULEMENT POUR SÉLECTIONNER [[[[prénom] DE #DBsToMove OUVRIR curDetachDBs FETCH SUIVANT DE curDetachDBs DANS @DBName TANDIS QUE @@FETCH_STATUS = 0 COMMENCER IMPRESSION CARBONISER(13) + CARBONISER(dix) IMPRESSION '—————————————————– ——– ' IMPRESSION '-' + @DBName IMPRESSION '—————————————————– ——– ' ENSEMBLE @sql = 'ALTER DATABASE['['[‘[‘ + @DBName + ']SET OFFLINE; ' IMPRESSION @sql EXEC sys.sp_executesql @sql FETCH SUIVANT DE curDetachDBs DANS @DBName FIN FERMER curDetachDBs DÉALLOCATE curDetachDBs

–verify changements SÉLECTIONNER * DE sys.bases de données OÙ prénom DANS('DB1','DB2','DB3'); – Les BD sont toujours présentes – contrairement à détachement

Les bases de données sont toujours présentes mais sont hors ligne. Ils ne peuvent pas être lus ou écrits.

Après avoir défini les bases de données hors ligne, déplacez les fichiers de données vers leur nouvel emplacement. Enfin, nous modifions les bases de données pour modifier leurs fichiers, nous les pointons vers le nouvel emplacement, puis nous remettons les bases de données en ligne.

–Alter DB pour modifier les fichiers puis les mettre en ligne DECLARE @DBName varchar (500); DÉCLARE @sql NVARCHAR (MAX); DECLARE @rowFile NVARCHAR (128); DECLARE @logFile NVARCHAR (128); DECLARE @fileType varchar (4); DÉCLARE @DBID int; DECLARE @NewPath varchar (500); DECLARE @fileName varchar (128); DECLARE curAttachDBs CURSOR FAST_FORWARD READ_ONLY POUR        SELECT database_id, [type_desc], [name], NewPath DE #DBfiles OUVRIR curAttachDBs FETCH NEXT FROM curAttachDBs IN @DBID, @fileType, @fileName, @NewPath WHILE @@ FETCH_STATUS = 0 COMMENCER SELECT @DBName = DB_NAME (@DBID); CARTE D'IMPRESSION (13) + CHAR (10) IMPRESSION '———————————————— ——— ' PRINT '-' + @DBName IMPRESSION '———————————————— ——— ' sélectionnez @DBID, @fileType, @fileName, @NewPath –changez les emplacements de fichiers SELECT @sql = 'ALTER DATABASE [‘ + @DBName + ‘] MODIFIER LE FICHIER ( NAME = '' '+ @fileName +' '', FILENAME = '' '+ @NewPath +' '' '' FROM #DBfiles; PRINT @sql; EXEC sys.sp_executesql @sql; –set DB en ligne SELECT @sql = 'ALTER DATABASE' + @DBName + 'SET ONLINE'; print @sql; EXEC sys.sp_executesql @sql; FETCH NEXT FROM curAttachDBs IN @DBID, @fileType, @fileName, @NewPath FIN CLOSE curAttachDBs DEALLOCATE curAttachDBs –Vérifier les informations du fichier DB sélectionnez database_id, DB_NAME (database_id) comme "DBName", [type_desc], [name], nom_ physiques, code_état de sys.master_files où DB_NAME (id_bdd) dans ('DB1', 'DB2', 'DB3');

1 2 3 4 5 6 7 8 9 dix 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

–Alter DB pour modifier les fichiers puis les mettre en ligne DÉCLARER @DBName varchar(500); DÉCLARER @sql NVARCHAR(MAX); DÉCLARER @rowFile NVARCHAR(128); DÉCLARER @fichier journal NVARCHAR(128); DÉCLARER @Type de fichier varchar(4); DÉCLARER @DBID int; DÉCLARER @Nouveau chemin varchar(500); DÉCLARER @nom de fichier varchar(128); DÉCLARER curAttachDBs LE CURSEUR AVANCE RAPIDE LECTURE SEULEMENT POUR SÉLECTIONNER base_de_données, [[[[type_desc], [[[[prénom], Nouveau chemin DE #DBfiles OUVRIR curAttachDBs FETCH SUIVANT DE curAttachDBs DANS @DBID, @Type de fichier, @nom de fichier, @Nouveau chemin TANDIS QUE @@FETCH_STATUS = 0 COMMENCER SÉLECTIONNER @DBName = DB_NAME(@DBID);

IMPRESSION CARBONISER(13) + CARBONISER(dix) IMPRESSION '—————————————————– ——– ' IMPRESSION '-' + @DBName IMPRESSION '—————————————————– ——– ' sélectionner @DBID, @Type de fichier, @nom de fichier, @Nouveau chemin –changez les emplacements de fichiers SÉLECTIONNER @sql = 'ALTER DATABASE['['[‘[‘ + @DBName + ']MODIFIER LE FICHIER ( NOM = ''' + @nom de fichier + ''', FILENAME = ''' + @Nouveau chemin + ''')' DE #DBfiles; IMPRESSION @sql; EXEC sys.sp_executesql @sql;

–set DB en ligne SÉLECTIONNER @sql = 'ALTER DATABASE' + @DBName + 'SET ONLINE'; impression @sql; EXEC sys.sp_executesql @sql;

FETCH SUIVANT DE curAttachDBs DANS @DBID, @Type de fichier, @nom de fichier, @Nouveau chemin FIN FERMER curAttachDBs DÉALLOCATE curAttachDBs

–Vérifier les informations du fichier DB sélectionner base_de_données, DB_NAME(base_de_données) comme 'DBName', [[[[type_desc], [[[[prénom], nom_périphérique, state_desc de sys.master_files où DB_NAME(base_de_données) dans('DB1','DB2','DB3');

Voici le résultat de la vérification ci-dessus. Il montre les fichiers de base de données dans un nouvel emplacement et en ligne.

Méthode 3: Sauvegarder / détacher et supprimer / restaurer ailleurs La sauvegarde sera comme toutes les autres.

UTILISER le maître; ALLER –Base de sauvegarde BASE DE DONNEES DE SAUVEGARDE [DB1] TO DISK = N'C: Program Files Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL Backup DB1.bak '; ALLER BASE DE DONNEES DE SAUVEGARDE [DB2] TO DISK = N'C: Program Files Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL Backup DB2.bak '; ALLER BASE DE DONNEES DE SAUVEGARDE [DB3] TO DISK = N'C: Program Files Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL Backup DB3.bak '; ALLER

UTILISATION maîtriser; ALLER

–Base de sauvegarde SAUVEGARDE BASE DE DONNÉES [[[[DB1] À DISQUE = N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL Backup DB1.bak'; ALLER

SAUVEGARDE BASE DE DONNÉES [[[[DB2] À DISQUE = N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL Backup DB2.bak'; ALLER

SAUVEGARDE BASE DE DONNÉES [[[[DB3] À DISQUE = N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL Backup DB3.bak'; ALLER

À ce stade, nous sommes libres de détacher les bases de données et de disposer des fichiers de base de données. Il serait peut-être bon de conserver les fichiers jusqu'à ce que nous ayons vérifié que la restauration a fonctionné. Comme il s’agit d’un exemple artificiel, passons à la restauration.

– Restaurer des bases de données RESTORE DATABASE [DB1] FROM DISK = N'C: Program Files Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL Backup DB1.bak ' WITH REPLACE, FILE = 1, DÉPLACEZ N'DB1 'À N'C: Program Files Microsoft Serveur SQL MSSQL13.MSSQLSERVER MSSQL DATA DB1.mdf', DÉPLACER N'DB1_log 'À N'C: Program Files Microsoft Serveur SQL MSSQL13.MSSQLSERVER MSSQL DATA DB1_log.ldf', NOUNLOAD, STATS = 5 ALLER RESTORE DATABASE [DB2] FROM DISK = N'C: Program Files Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL Backup DB2.bak ' WITH REPLACE, FILE = 1, DÉPLACEZ N'DB2 'À N'C: Program Files Microsoft Serveur SQL MSSQL13.MSSQLSERVER MSSQL DATA DB2.mdf', DÉPLACER N'DB2_log 'À N'C: Program Files Microsoft Serveur SQL MSSQL13.MSSQLSERVER MSSQL DATA DB2_log.ldf', NOUNLOAD, STATS = 5 ALLER RESTORE DATABASE [DB3] FROM DISK = N'C: Program Files Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL Backup DB3.bak ' WITH REPLACE, FILE = 1, DÉPLACEZ N'DB3 'À N'C: Program Files Microsoft Serveur SQL MSSQL13.MSSQLSERVER MSSQL DATA DB3.mdf', DÉPLACER N'DB3_log 'À N'C: Program Files Microsoft Serveur SQL MSSQL13.MSSQLSERVER MSSQL DATA DB3_log.ldf', NOUNLOAD, STATS = 5 ALLER

– Restaurer des bases de données RESTAURER BASE DE DONNÉES [[[[DB1] DE DISQUE = N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL Backup DB1.bak' AVEC REMPLACER, FICHIER = 1, BOUGE TOI N'DB1' À N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA DB1.mdf', BOUGE TOI N'DB1_log' À N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA DB1_log.ldf', NOUNLOAD, STATISTIQUES = 5 ALLER

RESTAURER BASE DE DONNÉES [[[[DB2] DE DISQUE = N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL Backup DB2.bak' AVEC REMPLACER, FICHIER = 1, BOUGE TOI N'DB2' À N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA DB2.mdf', BOUGE TOI N'DB2_log' À N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA DB2_log.ldf', NOUNLOAD, STATISTIQUES = 5 ALLER

RESTAURER BASE DE DONNÉES [[[[DB3] DE DISQUE = N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL Backup DB3.bak' AVEC REMPLACER, FICHIER = 1, BOUGE TOI N'DB3' À N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA DB3.mdf', BOUGE TOI N'DB3_log' À N'C: Programmes Microsoft SQL Server MSSQL13.MSSQLSERVER MSSQL DATA DB3_log.ldf', NOUNLOAD, STATISTIQUES = 5 ALLER

Branchez le nouveau chemin de la clause MOVE TO et la restauration y placera les fichiers. Après avoir migré certaines bases de données, nous voulons être certains que tout va bien et que le système est opérationnel. Quelques contrôles simples permettent de mettre vos parties prenantes à l'aise. Après la migration des fichiers de base de données, il est bon de procéder comme suit:

Vérifiez sys.database_files pour vous assurer qu'ils sont dirigés au bon endroit Exécuter DBCC CHECKDB sur les bases de données déplacées

Le déplacement des fichiers de base de données peut créer des problèmes qu'une vérification de cohérence rapide peut atténuer.

Chacune des méthodes présentées ci-dessus présente des avantages et des inconvénients pour déplacer des fichiers de base de données. Assurez-vous que, quelle que soit la technique que vous choisissez d'utiliser, vous la testez d'abord sur un serveur hors production. Ce n'est pas le point que vous voulez rencontrer des surprises inconnues. La méthode la plus sûre est probablement # 2 – mettre en mode hors connexion, déplacer des fichiers, modifier le fichier, le mettre en ligne. Le détachement risque de permettre la suppression des fichiers, mais si c'est ce que vous voulez, détachez comme il convient. La méthode de sauvegarde est probablement mieux conservée pour la veille chaud / froid, mais pourrait fonctionner en fonction de l'utilisation du système et des contraintes.

Comme ça: Comme Chargement…

Click to rate this post! [Total: 0 Average: 0]