Migrer une base de données

Rédigé par Sozezzo - - Aucun commentaire

Migrer une base de données vers un nouveau serveur est assez simple…

si…

  • Il y a seulement une base de données;
  • Il n’y a pas des objets à migrer;
  • Il n’y a pas de dépendances physiques;

 

Alors, le SQL script proposé fait une partie du processus :

  • Ferme toutes les connexions vers la base de données à migrer;
  • Restreins l’accès à la base de données;
  • Fais une copie de sauvegarde de la base de données;
  • Restaure l’accès à la base de données à multi-utilisateur;
  • Mise hors ligne la base de données;

 

Le SQL script ajoute des scripts pour :

  • Change l’ancien utilisateur de la base de données;
  • Mise hors ligne les bases de données;
  • la restauration en ligne les bases de données;

 

Le script créé l’option :

  • Test : exécute seulement les copie de sauvegarde;
  • Migration : exécute tous les scripts;

Vous devez choisir le répertoire pour les fichiers de sauvegarde.

print '---------------------------------------'
PRINT '-- SQL Script to backup databases'
print '--';
print '-- Created : ' + cast(getdate() as nvarchar(30))
print '--';
print '-- Create from server: ' +@@servername
print '---------------------------------------'
GO
SET NOCOUNT ON;
print '';

 

BEGIN TRY DROP TABLE #db END TRY BEGIN CATCH END CATCH;
BEGIN TRY DROP TABLE #dbsave END TRY BEGIN CATCH END CATCH;
SELECT f.Name as databasename, suser_sname( owner_sid ) as databaseowner
INTO #db FROM sys.databases f
WHERE (f.database_id > 4)  -- no system databases
AND f.Name NOT LIKE 'WSS%' AND f.Name NOT LIKE 'Sharepoint%'
ORDER BY f.name

-- SELECT * FROM  sys.databases ORDER BY name

DECLARE @sql as nvarchar(max);
DECLARE @sql1 as nvarchar(max);
DECLARE @sql2 as nvarchar(max);
DECLARE @sql3 as nvarchar(max);
DECLARE @sql4 as nvarchar(max);

SET @sql = '';
SET @sql = @sql + '--  -- -- -- -- -- -- -- -- -- -- --' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'PRINT @@servername;' + CHAR(13) + CHAR(10);
SET @sql = @sql + '--  -- -- -- -- -- -- -- -- -- -- --' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'DECLARE @mode NVARCHAR(30);' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'DECLARE @pathServer nvarchar(1024);' + CHAR(13) + CHAR(10);
SET @sql = @sql + '-- *** make sure that the below path exists ***' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'set @pathServer = ''\\MyServer\MyFolder$'';' + CHAR(13) + CHAR(10);
SET @sql = @sql + '-- Set mode ' + CHAR(13) + CHAR(10);
SET @sql = @sql + '-- Mode migration/test ' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'SET @mode=''*'';            -- ** SET MODE **' + CHAR(13) + CHAR(10);
SET @sql = @sql + '-- SET @mode=''Test'';      -- Test' + CHAR(13) + CHAR(10);
SET @sql = @sql + '-- SET @mode=''Migration''; -- Migration' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'IF (@mode=''*'')' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'BEGIN' + CHAR(13) + CHAR(10);
SET @sql = @sql + '    PRINT ''SET Mode migration/test''' + CHAR(13) + CHAR(10);
SET @sql = @sql + '    RETURN;' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'END' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'DECLARE @sql as nvarchar(max);' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'DECLARE @ToKill AS NVARCHAR(max);' + CHAR(13) + CHAR(10);

SET @sql1= @sql;

SET @sql = '';
SET @sql = @sql + 'PRINT ''-- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** --'';' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'PRINT ''-- Database : [databasename] -- owner : [databaseowner] ''' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'PRINT ''USE [master];''' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'IF (@mode=''Migration'')' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'BEGIN' + CHAR(13) + CHAR(10);
SET @sql = @sql + '' + CHAR(13) + CHAR(10);
SET @sql = @sql + '    PRINT ''-- Kill all connection to [databasename]'';' + CHAR(13) + CHAR(10);
SET @sql = @sql + '    SET @ToKill = '''';' + CHAR(13) + CHAR(10);
SET @sql = @sql + '    SELECT @toKill = @ToKill + ''kill '' + cast(spid AS NVARCHAR(10)) + '';'' FROM master..sysprocesses WHERE spid <> @@spid AND dbid = DB_ID(''[databasename]'');' + CHAR(13) + CHAR(10);
SET @sql = @sql + '    EXEC (@tokill);' + CHAR(13) + CHAR(10);
SET @sql = @sql + '' + CHAR(13) + CHAR(10);
SET @sql = @sql + '    PRINT ''-- Set RESTRICTED_USER to [databasename]'';' + CHAR(13) + CHAR(10);
SET @sql = @sql + '    ALTER DATABASE [databasename] SET RESTRICTED_USER WITH NO_WAIT' + CHAR(13) + CHAR(10);
SET @sql = @sql + '' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'END' + CHAR(13) + CHAR(10);
SET @sql = @sql + '--  -- -- -- -- -- -- -- -- -- -- --' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'PRINT ''-- Backup database: [databasename]'';' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'SET @sql = '''';' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'SET @sql = @sql + ''backup database [databasename] to disk = ''''''+@pathServer+''\[databasename]_backup.bak'''' WITH NOFORMAT, INIT, NAME = N''''[databasename]-Full Database Backup for migration'''', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;'' + CHAR(13) + CHAR(10);' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'EXEC (@sql);' + CHAR(13) + CHAR(10);
SET @sql = @sql + '' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'IF (@mode=''Migration'')' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'BEGIN' + CHAR(13) + CHAR(10);
SET @sql = @sql + '    PRINT ''-- Set Offline database : [databasename]'';' + CHAR(13) + CHAR(10);
SET @sql = @sql + '    ALTER DATABASE [databasename] SET MULTI_USER WITH NO_WAIT' + CHAR(13) + CHAR(10);
SET @sql = @sql + '    ALTER DATABASE [databasename] SET OFFLINE WITH ROLLBACK IMMEDIATE;' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'END' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'PRINT '''';' + CHAR(13) + CHAR(10);
SET @sql = @sql + 'PRINT ''-- END script to Database : [databasename] -- owner : [databaseowner] ''' + CHAR(13) + CHAR(10);
SET @sql = @sql + '' + CHAR(13) + CHAR(10);
SET @sql2 = @sql;


SELECT * INTO #dbSave FROM #db;

PRINT @sql1;


DECLARE @databasename NVARCHAR(100);
DECLARE @databaseowner NVARCHAR(100);
SET @sql3 = '';
SET @sql4 = '';

SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
DELETE FROM #db WHERE @databasename = databasename;
WHILE (@databasename IS NOT NULL)
BEGIN
    SET @sql = @sql2;
    SET @sql = REPLACE(@sql, '[databasename]' , @databasename);
    SET @sql = REPLACE(@sql, '[databaseowner]', @databaseowner);
    PRINT @sql;
    SET @databasename  = NULL;
    SET @databaseowner = NULL;
    SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
    DELETE FROM #db WHERE @databasename = databasename;
END

PRINT ''
PRINT '/**********************************'
PRINT '-- SET change db owner to OLD db owner'
INSERT INTO #db SELECT * FROM #dbSave;
DECLARE @databasenamemaxlen int;
SELECT @databasenamemaxlen = MAX(LEN(databasename)) FROM #db;
SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
DELETE FROM #db WHERE @databasename = databasename;
WHILE (@databasename IS NOT NULL)
BEGIN
    SET @sql = '';
    SET @sql = @sql + 'USE [[databasename]];[space]EXEC dbo.sp_changedbowner @map = false, @loginame = N''[databaseowner]'';' + CHAR(13) + CHAR(10);
    SET @sql = REPLACE(@sql, '[space]' , SPACE(@databasenamemaxlen-LEN(@databasename)));
    SET @sql = REPLACE(@sql, '[databasename]' , @databasename);
    SET @sql = REPLACE(@sql, '[databaseowner]', @databaseowner);
    PRINT @sql;
    SET @databasename  = NULL;
    SET @databaseowner = NULL;
    SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
    DELETE FROM #db WHERE @databasename = databasename;
END
PRINT '***********************************/'
PRINT ''

PRINT '/**********************************'
PRINT '-- SET Offline databases'
INSERT INTO #db SELECT * FROM #dbSave;
SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
DELETE FROM #db WHERE @databasename = databasename;
WHILE (@databasename IS NOT NULL)
BEGIN
    SET @sql = 'ALTER DATABASE [databasename] [space]SET OFFLINE WITH ROLLBACK IMMEDIATE;' + CHAR(13) + CHAR(10);
    SET @sql = REPLACE(@sql, '[space]' , SPACE(@databasenamemaxlen-LEN(@databasename)));
    SET @sql = REPLACE(@sql, '[databasename]' , @databasename);
    SET @sql = REPLACE(@sql, '[databaseowner]', @databaseowner);
    PRINT @sql;
    SET @databasename  = NULL;
    SET @databaseowner = NULL;
    SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
    DELETE FROM #db WHERE @databasename = databasename;
END
PRINT '***********************************/'
PRINT ''
PRINT '/**********************************'
PRINT '-- SET online databases'
INSERT INTO #db SELECT * FROM #dbSave;
SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
DELETE FROM #db WHERE @databasename = databasename;
WHILE (@databasename IS NOT NULL)
BEGIN
    SET @sql = 'ALTER DATABASE [databasename] [space]SET ONLINE;' + CHAR(13) + CHAR(10);
    SET @sql = REPLACE(@sql, '[space]' , SPACE(@databasenamemaxlen-LEN(@databasename)));
    SET @sql = REPLACE(@sql, '[databasename]' , @databasename);
    SET @sql = REPLACE(@sql, '[databaseowner]', @databaseowner);
    PRINT @sql;
    SET @databasename  = NULL;
    SET @databaseowner = NULL;
    SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
    DELETE FROM #db WHERE @databasename = databasename;
END
PRINT '***********************************/'

Les commentaires sont fermés.