Serveur d'impression

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

Le 2 mai 2019 - 24 minutes de lecture

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.

Oiseaux migrateurs

Une fois que nous avons le plan de match, nous pouvons commencer à le mettre en œuvre. Regardons quelques détails.

Sommaire

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

dix

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:

Informations sur le fichier de base de données pour les bases de données que nous allons déplacer

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;

dix

/ ***************************************

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;

Sortie de #DBFiles

Les bases de données avant de se détacher

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

dix

/ ************************************************ *****************************************

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:

robocopier les fichiers

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

dix

– 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;

Nouveaux emplacements de fichiers

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;

dix

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:

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

dix

/ ************************************************ *****************************************

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.

Les bases de données sont maintenant hors ligne

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');

dix

–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.

Les DBs 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

Bases de données de sauvegarde

À 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
1. 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.

Articles relatifs:

Commentaires

Laisser un commentaire

Cookie	Durée	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

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

Migration – Manières générales de déplacer des fichiers de base 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

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

Méthode 3: Sauvegarder / détacher et supprimer / restaurer ailleurs

Comme ça:

Articles relatifs:

Tutos Gameserver respecte votre vie privée et vos données personnelles