Microsoft SQL Server/Importer et exporter
Interfaces graphiques
modifierImporter
modifierDans SSMS, l'option d'importation de fichier Excel ou Access dans MSSQL est disponible par un clic droit sur la base de destination, Tâches..., Importer des données...[1].
Exporter
modifierDe même, il existe Tâches..., Exporter des données... ou Copier la base de données.
Il est par ailleurs possible d'exporter le résultat d'une requête. En effet, par défaut dans SSMS, le bouton Résultats dans des grilles est enfoncé. En cliquant sur Résultats dans un fichier, il devient possible d'exporter le contenu de tables dans un fichier .rpt avec une requête SQL.
Pour transformer une base MS-SQL en MySQL il existe MySQL Workbench[2]. Attention car la syntaxe des procédures stockées est différente[3].
xp_cmdShell
modifierxp_cmdShell
permet de d’interagir avec le système de fichier, en manipulant le shell du système d'exploitation : il comprend donc les commandes DOS.
Créer un fichier texte
modifierEXECUTE master.dbo.xp_cmdShell 'echo Hello World! > C:\Test.txt'
-- ou en abrégeant :
xp_cmdShell 'echo Hello World! > C:\Test.txt'
Pour le lire, il suffit de le charger dans une table avec BULK INSERT
.
Copier un fichier
modifierxp_cmdshell 'copy C:\Test.txt C:\Test2.txt'
Cacher un fichier
modifierxp_cmdshell 'attrib +h C:\Test.txt'
Supprimer un fichier
modifierxp_cmdshell 'del C:\Test2.txt'
Créer un dossier
modifierL'antislash de fin est facultatif :
xp_cmdShell 'mkdir C:\Test\'
Lister le contenu d'un dossier
modifierxp_cmdShell 'dir C:\Test\'
Supprimer un dossier
modifierxp_cmdShell 'rmdir C:\Test\'
OPENROWSET
modifierPour archiver vers d'autres formats que ceux de la base de données, il existe la fonction OPENROWSET[4]. Elle permet d'importer ou d'exporter des données aux formats MS-Access ou MS-Excel[5].
Si ce pilote XLS demande une activation[6], il faut configurer :
sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries'; -- affiche l'état avant configuration
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries'; -- affiche l'état après configuration
GO
Soient les fichiers C:\Test_OPENROWSET.csv, C:\Test_OPENROWSET.xls et C:\Test_OPENROWSET.xlsx existants, avec une feuille nommée "Feuil1", dont les en-têtes de colonnes sont "Nom" et "Prénom".
Insertions dans un tableur
modifierINSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Test_OPENROWSET.xls;', 'SELECT * FROM [Feuil1$]')
SELECT Nom, Prénom
FROM table1
Si le fichier XLS(X) a des en-têtes de colonnes, il faut les nommer au lieu d'employer *.
Sélections dans un tableur
modifierCSV
modifierOn définit le dossier puis le fichier. Il y a deux pilotes au choix :
SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\;','select * from Test_OPENROWSET.csv');
ou
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\;','SELECT * FROM [Test_OPENROWSET.csv]')
XLS
modifierPour lire un XLS ou ou l'importer dans une table, on définit le fichier puis la feuille :
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Test_OPENROWSET.xls;', 'SELECT * FROM [Feuil1$]')
Plusieurs options peuvent aussi être placées après le nom du fichier. Elles n'impactent pas le mode écriture, seulement le mode lecture.
- "HDR" (comme header) : les en-têtes de colonnes sont sur la première ligne (comportement par défaut) "HDR=YES". Si "HDR=NO", les colonnes sélectionnées s'appellent alors "F1", "F2", "F3"...
- "IMEX" (pour ImportMixedTypes)[7] :
- "=0" : Export mode, Excel devine les types des champs.
- "=1" : Import mode, les champs sont tous convertis en texte.
- "=2" : Linked mode.
- Types des données[8] :
- "DT_BOOL" : booléen.
- "DT_CY" : devise.
- "DT_DATE" : date et heure.
- "DT_NTEXT" : texte.
- "DT_R8" : numérique.
- "DT_WSTR" : chaîne de caractères.
On peut aussi créer un serveur lié pour l'occasion[9] :
EXEC sp_addlinkedserver
@server = 'ExcelServer1',
@srvproduct = 'Excel',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'C:\Test_OPENROWSET.xls',
@provstr = 'Excel 8.0;IMEX=1;HDR=YES;'
GO
SELECT * FROM ExcelServer1...[Sheet1$]
GO
SELECT * FROM OPENROWSET(ExcelServer1, 'SELECT * FROM [Sheet1$]')
XLSX
modifierPour un XLSX c'est un autre pilote :
select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=C:\Test_OPENROWSET.xlsx;', 'SELECT * FROM [Feuil1$]')
S'il faut enregistrer le pilote, l'installer depuis : https://www.microsoft.com/fr-FR/download/details.aspx?id=23734.
Modification d'un tableur
modifierLa fonction OPENROWSET
de SQL Server 2008 ne permet pas de modifier les propriétés des cellules d'un tableur, pour se faire se reporter au paragraphe sur sp_OACreate
. Dans tous les cas, il peut tout à fait mettre à jour ses valeurs comme si elles étaient dans des tables :
UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Test_OPENROWSET.xls;HDR=yes','SELECT * FROM [Feuil1$]')
SET F1 = '2'
WHERE F1 = '1'
DELETE
ne fonctionne pas sur des lignes Excel.BCP
modifierbcp (pour Bulk Copy) est un utilitaire d'importation et exportation de données avec des fichiers plats uniquement (XML ou CSV[10]).
DECLARE @cmd VARCHAR(255)
SET @cmd = 'bcp "select ''Hello'', ''World''" queryout "C:\Test_bcp.csv" -U MonCompte -P MonMotDePasse -c'
Exec xp_cmdshell @cmd
sp_OACreate
modifiermaster.dbo.sp_OAMethod
est une procédure stockée étendue permettant de manipuler des fichiers et des dossiers[11]. Elle n'est pas activée par défaut sous SQL Server 2008, il faut donc le faire ainsi :
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures'; -- affiche l'état avant configuration
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures'; -- affiche l'état après configuration
Voici un exemple de création puis de modification de fichier XLS (les numéros des couleurs sont les mêmes qu'en VBA[12]) :
DECLARE @r int, -- résultats des commandes
@FileName varchar(512),
@Excel int,
@WorkBooks int,
@WorkBook int,
@WorkSheet int,
@Cells int
-- 1) Création
SET @FileName = 'C:\Test_sp_OACreate.xls'
EXEC @r = sp_OACreate 'Excel.Application', @Excel output
IF @r=0 EXEC @r = sp_OAMethod @Excel, 'Workbooks', @WorkBooks OUTPUT
IF @r=0 EXEC @r = sp_OAMethod @WorkBooks, 'Add', @WorkBook OUTPUT, -4167
IF @r=0 EXEC @r = sp_OAMethod @WorkBook, 'Worksheets(1)', @WorkSheet output, 2
IF @r=0 EXEC @r = sp_OAMethod @WorkSheet, 'Activate'
IF @r=0 EXEC @r = sp_OASetProperty @WorkSheet, 'Name', 'Reporting'
IF @r=0 EXEC @r = sp_OASetProperty @WorkSheet, 'Cells(1,3).Value', 'Hello World!'
IF @r=0 EXEC @r = sp_OAMethod @WorkBook, 'SaveAs', NULL, @FileName
IF @r=0 EXEC @r = sp_OAMethod @WorkBook, 'Close'
-- 2) Une fois le fichier fermé on peut le remplir ici avec OPENROWSET
-- 3) Retouches
IF @r=0 EXEC @r = sp_OAMethod @Excel, 'WorkBooks.Open', @WorkBook output, @FileName
IF @r=0 EXEC @r = sp_OAMethod @WorkBook, 'Worksheets(1)', @WorkSheet output, 2
IF @r=0 EXEC @r = sp_OAMethod @WorkSheet, 'Activate'
EXEC @r = sp_OASetProperty @WorkSheet, 'Range("A1").Value', 'Hello World1!'
EXEC @r = sp_OAGetProperty @WorkSheet, 'Cells', @Cells OUTPUT, 2, 2 -- Position de la cellule B2
EXEC @r = sp_OASetProperty @Cells, 'Value', 'Hello World2!'
EXEC @r = sp_OASetProperty @Cells, 'Font.Bold', 1 -- gras
EXEC @r = sp_OASetProperty @Cells, 'Font.Colorindex', 3 -- rouge pour la police
EXEC @r = sp_OASetProperty @Cells, 'Interior.ColorIndex', 4 -- vert pour le fond
EXEC @r = sp_OASetProperty @Cells, 'Borders.ColorIndex', 5 -- bleu pour les bordures
EXEC @r = sp_OASetProperty @Excel, 'ActiveWorkbook.Worksheets(1).Cells(3,3).Value', 'Hello World3!'
EXEC @r = sp_OASetProperty @Excel, 'ActiveWorkbook.Worksheets(1).Cells(3,3).Font.Colorindex', 9
EXEC sp_OAMethod @Excel, 'ActiveWorkbook.Save'
EXEC sp_OAMethod @Excel, 'Workbooks.Close'
EXEC sp_OAMethod @Excel, 'Close'
EXEC sp_OADestroy @Cells
EXEC sp_OADestroy @WorkSheet
EXEC sp_OADestroy @WorkBook
EXEC sp_OADestroy @WorkBooks
EXEC sp_OADestroy @Excel
Sauvegardes .bak et .trn
modifierTout d'abord, il faut distinguer l'archivage des bases (copie d'un .mdf en .bak) de celui des logs (.ldf en .trn) appelés aussi journaux de transaction.
Backup des bases
modifierIl est recommandé d'effectuer automatiquement celui un backup des bases toutes les nuits, à l'aide d'un job (dans SSMS, en bas de l'arborescence, menu Travaux). Un deuxième pourra s'occuper de supprimer les .bak après une certaine durée de rétention qui peut dépendre de l'espace disponible sur le serveur.
Exemple de sauvegarde sur un disque dur du serveur[13] (et non pas un du client où SSMS est lancé) :
declare @chemin as varchar(255) = 'C:\' + CONVERT(char(10), GetDate(),126) + '-sugarcrm.bak'
BACKUP DATABASE sugarcrm
TO DISK = @chemin
WITH FORMAT,
MEDIANAME = '',
NAME = 'Full Backup';
GO
Journaux
modifierRequêtes
modifierPar défaut, SSMS ne permet pas de consulter les requêtes SQL envoyées au serveur.
Toutefois, les dernières requêtes sont stockées dans un fichier des traces .trc. Pour connaitre son nom :
SELECT * FROM ::fn_trace_getinfo(default)
Ensuite on peut en extraire les 10 dernières requêtes SQL envoyées au serveur :
SELECT top 10 StartTime, TextData
FROM fn_trace_gettable('D:\MSSQL10.MSSQLSERVER\MSSQL\Log\log_424.trc', default)
where ISNULL(convert(varchar,TextData,112), '')<>''
order by StartTime desc
Soit en une seule requête :
declare @logs varchar(255) = (SELECT top 1 convert(varchar(255),value,112) FROM ::fn_trace_getinfo(default) where property = 2)
SELECT top 10 StartTime, TextData
FROM fn_trace_gettable(@logs, default)
where ISNULL(convert(varchar,TextData,112), '')<>''
order by StartTime desc
Autre solution, passer par les statistiques de performance :
SELECT top 10 d.last_execution_time, e.text
FROM sys.dm_exec_query_stats d
CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS e
order by d.last_execution_time desc
La plupart des requêtes des traces sont différentes de celles des statistiques, plus exhaustive mais sans les backups.
Transactions
modifierIl existe une fonction pour lire les journaux des transactions non archivées :
SELECT * FROM fn_dblog(NULL, NULL)
Afin de gagner de la place, ces logs doivent par contre être supprimés régulièrement (c'est sans incidence sur l'utilisation du système). Il existe trois façons de les tronquer :
DBCC SHRINKFILE (N'MaBase_log' , 0, TRUNCATEONLY)
. DBCC est le sigle de DataBase Console Commands (commandes en console de base de données), c'est un ensemble de commandes sur les bases[14].- Clic droit sur la base, tâches, réduire... Fichiers, Type de fichier : Journal.
- Clic droit sur la base, tâches, détacher... (la base disparait ensuite de la liste), déplacer le .ldf, puis clic droit sur le serveur, joindre... En sélectionnant le .mdf, un nouveau .ldf vierge sera automatiquement créé avec.
Pour le définir automatiquement, il faut créer un job : Agent SQL server, Travaux, Clic droit : Nouveau travail[15].
Restaurations
modifierDans SSMS, pour restaurer une base de données, il faut faire un clic droit sur Bases de données, puis :
- Restaurer les fichiers : si le backup ne doit pas écraser la base originale.
- Restaurer la base de données... : pour remplacer une base (préalablement détachée) par sa sauvegarde. On doit ensuite choisir une base de données existante ou un fichier de backup (.bak) avec l'option "Périphérique".
Sinon en SQL ça donne[16] :
RESTORE DATABASE MaBase
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\2016-02-16-MaBase.bak'
WITH REPLACE
Références
modifier- ↑ http://blog.sqlauthority.com/2015/06/24/sql-server-fix-export-error-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/
- ↑ http://www.mysql.fr/products/workbench/
- ↑ http://www.thegeekstuff.com/2014/03/mssql-to-mysql-stored-procedure/
- ↑ https://msdn.microsoft.com/fr-fr/library/ms190312.aspx
- ↑ http://stackoverflow.com/questions/87735/how-do-you-transfer-or-export-sql-server-2005-data-to-excel
- ↑ http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm
- ↑ https://msdn.microsoft.com/fr-fr/library/ms141683.aspx
- ↑ https://msdn.microsoft.com/fr-fr/library/ms141036.aspx
- ↑ http://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm
- ↑ https://msdn.microsoft.com/fr-fr/library/ms162802(v=sql.100).aspx
- ↑ http://sqlindia.com/copy-move-files-folders-using-ole-automation-sql-server/
- ↑ https://msdn.microsoft.com/fr-fr/library/office/ff840443.aspx
- ↑ https://msdn.microsoft.com/fr-fr/library/ms186865%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396
- ↑ https://msdn.microsoft.com/fr-fr/library/ms188796(v=sql.120).aspx
- ↑ http://communitybi.blogspot.fr/2011/12/comment-creer-un-job-dans-sql-server.html
- ↑ https://msdn.microsoft.com/fr-fr/library/ms186858%28v=sql.100%29.aspx