Catégories: Entretien, Batch, Maintenance, sauvegarde_restauration

Vidéo Microsoft sur la migration 2000-2005 :
http://www.microsoft.com/france/vision/WebcastTechNetTechDays.aspx?EID=fd12de03-6bb2-435b-a135-da0db259d8d3

Tutorial sur le conseiller de mise à niveau 2000-2005 :
http://www.asp-php.net/tutorial/sql-server/sqlupgradeadvisor.php

La migration 2000-2005 par christian Robert :
http://blogs.codes-sources.com/christian/archive/2007/03/27/sql-server-migrer-de-2000-2005-les-commandes-indispensables.aspx

Tutorial - La migration par sauvegarde-restauration 2000-2005 :
http://www.technos-sources.com/tutorial-restaurer-base-provenant-backup-moteur-sql-server-2000-37.aspx

Tutorial supinfo - La migration 2000-2005 par Copy Database : http://www.laboratoire-microsoft.org/articles/server/migration-sql-server-2000-2005/

Dans cet article, j'aborderais l'utilisation des unités de sauvegarde, un outil trés pratique pour gérer toutes ses sauvegardes au même endroit dans le cas d'une sauvegarde sur bande.

» Lire la suite!

Concernant la gestion des lots de INSERT,UPDATE,DELETE sur votre base de données, vous devez appliquer les précautions suivantes.

» Lire la suite!

De nombreuses entreprises ont encore leurs serveurs de production sur sql serveur 2000, voir même dans certains cas, sur sql serveur 7. Il devient important de migrer vers sql serveur 2005. D'abord, sql serveur 2008 sortira en août. Ensuite, Microsoft a annoncé la fin de son support sur sql serveur 2000 pour avril 2008.

» Lire la suite!

Une des taches d' administration les plus simple que le développeur oublie systématiquement de mettre en place et qui entraîne des pertes de performance importante après plusieurs mois de mise à jour de la base.

Ptit Dje nous fournit un script qui utilise DBCC INDEX DEFRAG et évite de faire chuter les performances de la base lors de l'application. On définit le seuil de fragmentation MAXFRAG à partir de laquelle s'applique la réindexation.

  /*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL   -- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 10.0   -- Declare cursor
DECLARE TABLES CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE' --and table_name not in ('table_act_entry','table_case')   -- Create the table
CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)   -- Open the cursor
OPEN TABLES   -- Loop through all the tables in the database
FETCH NEXT
   FROM TABLES
   INTO @tablename   WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   FETCH NEXT
      FROM TABLES
      INTO @tablename
END   -- Close and deallocate the cursor
CLOSE TABLES
DEALLOCATE TABLES   -- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0   -- Open the cursor
OPEN indexes   -- loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag   WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')'
   EXEC (@execstr)       FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
END   -- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
DROP TABLE #fraglist
GO

Une solution plus lourde utilisant DBCC Reindex donc entrainant une perte de performance du serveur mais trés efficace car elle recree la totalité des index

USE Mabase
go
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FORSELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
go 

Le seul moyen de s'assurer qu'une sauvegarde est bonne est d'appliquer la restauration sur une nouvelle base.

nom base initial : test
nom base finale : test2

USE master
GO
SELECT 'kill',spid
FROM sysprocesses
WHERE dbid=db_id('test2')
GO
/* Exécution de l'output de la commande ci-dessus afin de libérer la base */
GO
RESTORE DATABASE test2 FROM DISK=N'C:\BACKUP\2007711TEST.bak' WITH
MOVE N'TEST' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test2.mdf',
MOVE N'TEST_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Test2_Log.ldf', REPLACE
ALTER DATABASE test2 MODIFY FILE (NAME=N'TEST', NEWNAME=N'test2')
ALTER DATABASE test2 MODIFY FILE (NAME=N'TEST_log', NEWNAME=N'test2_log')
GO

cette technique de restauration en ligne de commande n'est pas tres utilise. Dans l'article suivant, Romelard Fabrice nous explique comment restaurer une base sur une autre à l'aide de sql serveur manager : http://www.technos-sources.com/tutorial.aspx?ID=37

Pour connaitre les processus sur le serveur :

EXEC SP_WHO

Merci à serge934 de la communauté developpez.com (http://www.developpez.net/forums/showthread.php?t=372323)

je te joins un script que j'utilise pour mes backups que j'ai mis dans une PSlancée par un travail

USE Master go
--drop table #BckDatabases
CREATE TABLE #BckDatabases ( databasename sysname)
SET nocount ON
declare @BckPath varchar(255)
declare @Prefix varchar(50)
declare @Extension varchar(10)
/******/
/* Paramétrage */
/******/
SET @BckPath = 'chemin_backup'
SET @Prefix = cast(year(getdate()) AS varchar)+cast(month(getdate()) AS varchar)+cast(day(getdate()) AS varchar)
SET @Extension = '.bak'
INSERT INTO #BckDatabases values('base1')
INSERT INTO #BckDatabases values('base2') etc...
/******/
PRINT ''
PRINT 'Liste des bases à Backuper :'
SELECT * FROM #BckDatabases
declare @CurrentDB sysname
declare @sql varchar(2000)
declare @fileName varchar(255)
SET nocount off
Declare curDB Cursor FOR
SELECT databasename FROM #BckDatabases
open curDBFetch
next FROM CurDB INTO @CurrentDB
while @@fetch_status = 0
begin SET @FileName = @bckPath +@Prefix +@CurrentDB + @Extension
PRINT '====================================================================' PRINT ' '+@CurrentDB + ' Backup Started on '+Cast(GetDate() AS Varchar)
SET @sql = 'BACKUP DATABASE '+@CurrentDB + ' TO DISK=N''' + @filename + ''''
print @sql
exec (@sql)
PRINT '** '+@CurrentDB + ' Backup Ended on '+Cast(GetDate() AS Varchar)
PRINT '====================================================================' PRINT ''
Fetch next FROM CurDB INTO @CurrentDB
end
deallocate CurDB
DROP TABLE #BckDatabases

tes sauvegardes s'appelleront
20070704_base1.bak
20070704_base2.bak
20070705_base1.bak
20070705_base2.bak
etc..
ensuite j'ai un travail qui supprime les fichiers dont la date est inférieure a 8 jours de la date du jour.

pour s'assurer que la base est bien sauvegarde, il faut :

tu peux le faire en faisant un "restore verifyonly" après ta sauvegarde. En fait ce n'est pas une restauration mais juste une verif.regardes
http://technet.microsoft.com/fr-fr/library/ms188902.aspx

quelques conseils:

le plan doit contenir:
-reindexations des tables les plus usitées DBCC DBREINDEX ...
-sauvegarde du journal de transaction ( et que lui !) après-test place dispo sur tes devices
-test des jobs qui pourraient se lancer pendant ta sauvegarde
-compactage SANS replacement en tête de fichier
-sauvegarde de tes bases COMPLETES (les differentielles plantent toujours a la restauration sauf chez oracle )
-test avec VERIFYONLY
-zip des .bak
-archivages des bak d'un coté et des zip d'un autre (sur un disque DIFFERENT)
PS: attention si tu es sous RAID5, ne garde que le dernier journal de transaction, supprime (del) les anciens.

Pour automatiser un backup :

declare
@filename varchar(255),
@heure char(2),
@min char(2),
@date char(8)
set @date=convert(char(8),getdate(),112)
set @heure=datepart(hh,getdate())
set @min=datepart(mi,getdate())
if @heure<=9
set @heure='0'+@heure
if @min<=9
set @min ='0'+@minset @filename='G:BACKUPmabase_tran_'+@date+'_'+@heure+'h'+@min+'.trn’
backup log mabase to @filename
go

Pour découvrir les nouvelles fonctionnalités de SQL SERVER 2005 SP2.

Télécharger :

http://www.microsoft.com/downloads/details.aspx?familyid=D07219B2-1E23-49C8-8F0C-63FA18F26D3A&displaylang=en

Tutorial :

http://www.technos-sources.com/tutorial-installation-service-pack-sql-server-2005-43.aspx

Attention! A ce jour, un SP3 existe pour sql server 2005

Un mode de récupération est une option de configuration de bases de données qui contrôle la façon dont les transactions ( insert,update,delete ) sont journalisés dans le fichier de log, s'il est effectué une sauvegarde du journal des transactions et quelles sont les options de récupération disponible. Le mode de récupération retenu pour votre base de données possède des implications à la fois sur la récupération de la base de données et sur les performances, en fonction de la journalisation effectuée ou non par le mode.

SQL Server 2005 propose trois mode de récupération pour les bases de données :
- complet
- simple
- journalisé en bloc

Dans le mode de récupération complet, le moteur de bases de données journalise toute les opérations sans jamais les tronquer. Ce mode permet de restaurer une base de données au point de défaillance. Il s'agit du mode recommandé pour une base OLTP fortement sollicité. Le journal est vidé à chaque sauvegarde. Toutes les opérations sont journalisés dans le journal.

Dans le mode de récupération simple, le moteur de base de données limite la journalisation de la majorité des transactions et tronque le journal de transaction ( suppression des données ) après chaque point de vérification ( toutes les secondes ). Ce mode ne permet pas de sauvegarder et de restaurer le journal de transaction. Il s'agit d'un mode facile à maintenir car on ne gère guère le journal de transaction, par contre, il ne permet pas de revenir à l'instant t de la défaillance ce que permet la sauvegarde régulière du journal.

Dans le mode de récupération journalisé en bloc, le moteur de base de données journalise de façon minimal les opérations de masse comme select into et bulk insert ( BCP également ). Dans ce mode, si une sauvegarde de journal contient une quelconque opération en bloc, vous pouvez restaurer la base de données à la fin de la sauvegarde du journal, pas à un point déterminé comme dans la journalisation complète. Ce mode doit être employé lors de grosses opérations en bloc.C'est le mode recommandé pour la business intelligence.

EXEMPLE :

ALTER DATABASE MaBase SET RECOVERY { FULL | BULK_LOGGED | SIMPLE }

Export des données d'une table vers un fichier.

-S précise le nom de l'instance
-T utilise le mode d'authentification windows
-c fichier origine au format texte

exemple :

bcp ssms.dbo.clients out c:clients.txt -c -S Serveur -T
bcp Adventureworks.person.contact out "d:sql serveuradventureworks_person_contact.txt" -S Pluton -U SA -P ******* -T -c

Exporter le résultat d'une requête avec bcp

exemple :

bcp "select ville from ssms.dbo.clients" queryout c:lesvilles.txt -c -S Serveur -U id_utilisateur -P Password

Importer des données avec bcp.

-F La première ligne contient le nom de colonnes, la deuxième ligne les données.
-t précise la tabulation de separation.

exemple :

bcp ssms.dbo.codepostaux in c:codepostal.txt -c -F 2 -t ; -S Serveur -T

Tout DBA de production qui se respecte exécute DBCC CHECKDB avant chaque sauvegarde de nuit.


La commande DBCC CHECKDB effectue différents contrôles sur une base de données afin de vérifier l'allocation, l'intégrité structurelle et l'intégrité logicielle de tous les objets de la base de données.


exemple :


DBCC CHECKDB WITH PHYSICAL_ONLY pour une vérification matériel de la base de données.
DBCC CHECKDB pour une vérification complète.


Sachant qu'il n'existe aucun bug pouvant endommager une base de données sql serveur, on peut généralement se limiter à la première option qui détecte les erreurs matérielle pouvant survenir sur la base. Attention, une corruption de base est un évènement dramatique qui doit être détecté au plus tôt afin d'être réparé.


En cas d'erreur sur la base, vous avez deux option:


La plus prudente, c'est de faire appel à une sauvegarde récente et de la restaurer... si l'erreur se reproduit fréquemment, mettez en doute votre matériel!


La deuxième solution, la moins bonne, c'est de réparer la base avec DBCC CHECKDB. Vous devez savoir que si vous procédez ainsi, vous allez perdre des données pour récupérer l'intégrité du fichier.

Pour cela, je vous renvoie à la documentation de référence : http://msdn.microsoft.com/fr-fr/library/ms176064.aspx