1. Sauvegarde

Lors d'une migration de SQL server 2000 vers 2005 dans la même langue, il n'y a pas de recommandations particulières, il suffit d'installer la version 2005 par dessus la version 2000. (cf.  copie de base de données).La méthode de migration side-by-side est le scénario le plus simple pour passer de SQL server 7.0/2000 à SQL 2005.  En revanche lors d'une migration de SQL server en Français vers SQL server en Anglais des problèmes de compatibilité peuvent apparaître. Dans cet article, la méthode de migration consiste à sauvegarder puis à restaurer les bases de données tout en conservant les logins/mots de passes de SQL Server.

Pour sauvegarder la base de données il suffit d'ouvrir Entreprise Manager puis de sauvegarder la base de données via l'assistant  ou alors de simplement exécuter l'ordre SQL vu ci-dessus.
Exemple : BACKUP database "AdventureWorks " to disk='D:\BackupDB_MyServer\AdventureWorks.BAK'
Vous pouvez aussi personnaliser la commande BACKUP afin de sauvegarder les fichiers de transactions ou un groupe de fichiers.

Plus d'informations sur la commande BACKUP (Transac-SQL)

Lors de transfert d'une base de données entre deux instances de SQL Server, il faut penser également à recréer les logins et mot de passes afin que les applications qui accèdent à la base puissent continuer à fonctionner normalement. Lorsque qu'une seule base de données est hébergée avec un ou deux logins l'opération peut se faire manuellement. Cependant, avec de nombreux logins l'opération de création des logins/mot de passes devient très fastidieuse et longue. Pour sauvegarder les logins avec leurs mot de passes respectifs nous avons utilisés une version modifiée de la commande sp_help_revlogin qui permet l'utilisation des nouvelles commandes DDL de SQL Server 2005.

Le script sp_help_revlogin  doit être exécuté dans SQL Management Studio. Vous pouvez installer SQL Management Studio Express (SSMSE) qui est gratuit. Il vous permet de vous connecter à des serveurs SQL 2000 et SQL Server 2005, toutefois cet outil est en anglais.
Télécharger SQL Management Studio Express 

Script sp_help_revlogin pour SQL Server 2005

USE master
GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin
FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )
WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin
FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )
WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr

IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked =
CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked =
CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name )
+ ' WITH PASSWORD = ' + @PWD_string
+ ' HASHED, SID = ' + @SID_string
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login has exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO 

Vous pouvez télécharger ce script : ScriptSQLLOGIN2K5_sp_help_revlogin.sql

Il suffit ensuite d'exécuter sp_help_revlogin.sql dans SQL Management Studio. L'exécution de ce script va nous fournir en sortie les différents logins, les SID associés et les mot de passes sous forme hashées.

Il faut donc sauvegarder le résultat de ce script pour la restauration et de ne pas le laisser à la portée de tout le monde.

Exemple de script généré par sp_helprevlogin

-- Login: SQLUser
CREATE LOGIN SQLUser WITH PASSWORD =0x010058758533930C53711c9537957A06A7D379A12364A1A2B02BF00B672C9534981E8DFC16168DF28E7B2AA844BD HASHED,SID=0xEBE4B4FE18C7F94DBF7a7FA1234C1633

Vous avez donc sauvegardé les bases de données ainsi que les logins/mot de passes pour les transférer à la nouvelle instance de SQL Server 2005. Passons maintenant à la restauration !