Déplacer les fichiers de base de données de SQL Server 2008 vers un nouvel emplacement de dossier

Question

Nom logique

• my_Data
• my_Log

Chemin :

• C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
• C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

Nom de fichier :

• my.MDF
• my_1.LDF

Quel serait le sql script pour déplacer ces fichiers vers un nouvel emplacement : D:\DATA

La base de données est active et je devrais donc fermer les connexions existantes.

Answer 1

Vous avez oublié de mentionner le nom de votre base de données (est-ce "mon" ?).

ALTER DATABASE my SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE my SET OFFLINE;

ALTER DATABASE my MODIFY FILE 
(
   Name = my_Data,
   Filename = 'D:\DATA\my.MDF'
);

ALTER DATABASE my MODIFY FILE 
(
   Name = my_Log, 
   Filename = 'D:\DATA\my_1.LDF'
);

Maintenant, vous devez déplacer manuellement les fichiers de leur emplacement actuel vers D:\Data\ (et n'oubliez pas de les renommer manuellement si vous les avez modifiés dans la commande MODIFY FILE) ... puis vous pourrez remettre la base de données en ligne :

ALTER DATABASE my SET ONLINE;

ALTER DATABASE my SET MULTI_USER;

Ceci suppose que le compte de service SQL Server dispose de privilèges suffisants sur le serveur D:\Data\ dossier. Sinon, vous recevrez des erreurs lors de la commande SET ONLINE.

Answer 2

Vous pouvez utiliser l'option Detach/Attach dans SQL Server Management Studio.

Regarde ça : Déplacement d'une base de données à l'aide de Detach et Attach

Answer 3

Pour ajouter les privilèges nécessaires aux fichiers, ajoutez et accordez le droit à l'utilisateur local suivant : SQLServerMSSQLUser$COMPUTERNAME$INSTANCENAME, où NOM DE COMPUTERIE y INSTANCENAME doit être remplacé par le nom de l'ordinateur et de l'instance MSSQL respectivement.

Answer 4

Il s'agit d'une procédure complète pour transférer la base de données et les logins d'une istance à une nouvelle, en scriptant les logins et en relocalisant les fichiers de données et les fichiers log sur la destination. Tout cela en utilisant des méta-scripts.

http://zaboilab.com/sql-server-toolbox/massive-database-migration-between-sql-server-instances-the-complete-procedure

Désolé pour la procédure hors site mais les scripts sont très longs. Vous devez :
- script se connecte avec le SID original et le mot de passe HASHED
- Créer script pour sauvegarder la base de données en utilisant des métascripts
- Créer script pour restaurer la base de données en passant les paramètres de relocalisation en utilisant à nouveau les métascripts.
- Exécuter les scripts générés sur l'instance source et l'instance destination.
Voir les détails et télécharger les scripts en suivant le lien ci-dessus.

Answer 5

Quelques notes pour compléter le processus ALTER DATABASE :

1) Vous pouvez obtenir une liste complète des bases de données avec les noms logiques et les chemins complets des fichiers MDF et LDF :

   USE master SELECT name, physical_name FROM sys.master_files

2) Vous pouvez déplacer manuellement les fichiers avec la commande CMD move :

Déplacement "Source" "Destination"

Exemple :

md "D:\MSSQLData"
Move "C:\test\SYSADMIT-DB.mdf" "D:\MSSQLData\SYSADMIT-DB_Data.mdf"
Move "C:\test\SYSADMIT-DB_log.ldf" "D:\MSSQLData\SYSADMIT-DB_log.ldf"

3) Vous devez changer le chemin par défaut de la base de données pour la création de nouvelles bases de données. Le chemin par défaut est obtenu à partir du registre de Windows.

Vous pouvez également modifier avec T-SQL, par exemple, pour définir la destination par défaut : D:\MSSQLData

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\MSSQLData'

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\MSSQLData'

GO

Extrait de : http://www.sysadmit.com/2016/08/mover-base-de-datos-sql-server-a-otro-disco.html