Migrer une base de données

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.

  1
  2print '---------------------------------------'
  3PRINT '-- SQL Script to backup databases'
  4print '--';
  5print '-- Created : ' + cast(getdate() as nvarchar(30))
  6print '--';
  7print '-- Create from server: ' +@@servername
  8print '---------------------------------------'
  9GO
 10SET NOCOUNT ON;
 11print '';
 12
 13BEGIN TRY DROP TABLE #db END TRY BEGIN CATCH END CATCH;
 14BEGIN TRY DROP TABLE #dbsave END TRY BEGIN CATCH END CATCH;
 15SELECT f.Name as databasename, suser_sname( owner_sid ) as databaseowner
 16INTO #db FROM sys.databases f
 17WHERE (f.database_id > 4)  -- no system databases
 18AND f.Name NOT LIKE 'WSS%' AND f.Name NOT LIKE 'Sharepoint%'
 19ORDER BY f.name
 20
 21-- SELECT * FROM  sys.databases ORDER BY name
 22
 23DECLARE @sql as nvarchar(max);
 24DECLARE @sql1 as nvarchar(max);
 25DECLARE @sql2 as nvarchar(max);
 26DECLARE @sql3 as nvarchar(max);
 27DECLARE @sql4 as nvarchar(max);
 28
 29SET @sql = '';
 30SET @sql = @sql + '--  -- -- -- -- -- -- -- -- -- -- --' + CHAR(13) + CHAR(10);
 31SET @sql = @sql + 'PRINT @@servername;' + CHAR(13) + CHAR(10);
 32SET @sql = @sql + '--  -- -- -- -- -- -- -- -- -- -- --' + CHAR(13) + CHAR(10);
 33SET @sql = @sql + 'DECLARE @mode NVARCHAR(30);' + CHAR(13) + CHAR(10);
 34SET @sql = @sql + 'DECLARE @pathServer nvarchar(1024);' + CHAR(13) + CHAR(10);
 35SET @sql = @sql + '-- *** make sure that the below path exists ***' + CHAR(13) + CHAR(10);
 36SET @sql = @sql + 'set @pathServer = ''\\MyServer\MyFolder$'';' + CHAR(13) + CHAR(10);
 37SET @sql = @sql + '-- Set mode ' + CHAR(13) + CHAR(10);
 38SET @sql = @sql + '-- Mode migration/test ' + CHAR(13) + CHAR(10);
 39SET @sql = @sql + 'SET @mode=''*'';            -- ** SET MODE **' + CHAR(13) + CHAR(10);
 40SET @sql = @sql + '-- SET @mode=''Test'';      -- Test' + CHAR(13) + CHAR(10);
 41SET @sql = @sql + '-- SET @mode=''Migration''; -- Migration' + CHAR(13) + CHAR(10);
 42SET @sql = @sql + 'IF (@mode=''*'')' + CHAR(13) + CHAR(10);
 43SET @sql = @sql + 'BEGIN' + CHAR(13) + CHAR(10);
 44SET @sql = @sql + '    PRINT ''SET Mode migration/test''' + CHAR(13) + CHAR(10);
 45SET @sql = @sql + '    RETURN;' + CHAR(13) + CHAR(10);
 46SET @sql = @sql + 'END' + CHAR(13) + CHAR(10);
 47SET @sql = @sql + 'DECLARE @sql as nvarchar(max);' + CHAR(13) + CHAR(10);
 48SET @sql = @sql + 'DECLARE @ToKill AS NVARCHAR(max);' + CHAR(13) + CHAR(10);
 49
 50SET @sql1= @sql;
 51
 52SET @sql = '';
 53SET @sql = @sql + 'PRINT ''-- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** -- ** --'';' + CHAR(13) + CHAR(10);
 54SET @sql = @sql + 'PRINT ''-- Database : [databasename] -- owner : [databaseowner] ''' + CHAR(13) + CHAR(10);
 55SET @sql = @sql + 'PRINT ''USE [master];''' + CHAR(13) + CHAR(10);
 56SET @sql = @sql + 'IF (@mode=''Migration'')' + CHAR(13) + CHAR(10);
 57SET @sql = @sql + 'BEGIN' + CHAR(13) + CHAR(10);
 58SET @sql = @sql + '' + CHAR(13) + CHAR(10);
 59SET @sql = @sql + '    PRINT ''-- Kill all connection to [databasename]'';' + CHAR(13) + CHAR(10);
 60SET @sql = @sql + '    SET @ToKill = '''';' + CHAR(13) + CHAR(10);
 61SET @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);
 62SET @sql = @sql + '    EXEC (@tokill);' + CHAR(13) + CHAR(10);
 63SET @sql = @sql + '' + CHAR(13) + CHAR(10);
 64SET @sql = @sql + '    PRINT ''-- Set RESTRICTED_USER to [databasename]'';' + CHAR(13) + CHAR(10);
 65SET @sql = @sql + '    ALTER DATABASE [databasename] SET RESTRICTED_USER WITH NO_WAIT' + CHAR(13) + CHAR(10);
 66SET @sql = @sql + '' + CHAR(13) + CHAR(10);
 67SET @sql = @sql + 'END' + CHAR(13) + CHAR(10);
 68SET @sql = @sql + '--  -- -- -- -- -- -- -- -- -- -- --' + CHAR(13) + CHAR(10);
 69SET @sql = @sql + 'PRINT ''-- Backup database: [databasename]'';' + CHAR(13) + CHAR(10);
 70SET @sql = @sql + 'SET @sql = '''';' + CHAR(13) + CHAR(10);
 71SET @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);
 72SET @sql = @sql + 'EXEC (@sql);' + CHAR(13) + CHAR(10);
 73SET @sql = @sql + '' + CHAR(13) + CHAR(10);
 74SET @sql = @sql + 'IF (@mode=''Migration'')' + CHAR(13) + CHAR(10);
 75SET @sql = @sql + 'BEGIN' + CHAR(13) + CHAR(10);
 76SET @sql = @sql + '    PRINT ''-- Set Offline database : [databasename]'';' + CHAR(13) + CHAR(10);
 77SET @sql = @sql + '    ALTER DATABASE [databasename] SET MULTI_USER WITH NO_WAIT' + CHAR(13) + CHAR(10);
 78SET @sql = @sql + '    ALTER DATABASE [databasename] SET OFFLINE WITH ROLLBACK IMMEDIATE;' + CHAR(13) + CHAR(10);
 79SET @sql = @sql + 'END' + CHAR(13) + CHAR(10);
 80SET @sql = @sql + 'PRINT '''';' + CHAR(13) + CHAR(10);
 81SET @sql = @sql + 'PRINT ''-- END script to Database : [databasename] -- owner : [databaseowner] ''' + CHAR(13) + CHAR(10);
 82SET @sql = @sql + '' + CHAR(13) + CHAR(10);
 83SET @sql2 = @sql;
 84
 85SELECT * INTO #dbSave FROM #db;
 86
 87PRINT @sql1;
 88
 89DECLARE @databasename NVARCHAR(100);
 90DECLARE @databaseowner NVARCHAR(100);
 91SET @sql3 = '';
 92SET @sql4 = '';
 93
 94SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
 95DELETE FROM #db WHERE @databasename = databasename;
 96WHILE (@databasename IS NOT NULL)
 97BEGIN
 98    SET @sql = @sql2;
 99    SET @sql = REPLACE(@sql, '[databasename]' , @databasename);
100    SET @sql = REPLACE(@sql, '[databaseowner]', @databaseowner);
101    PRINT @sql;
102    SET @databasename  = NULL;
103    SET @databaseowner = NULL;
104    SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
105    DELETE FROM #db WHERE @databasename = databasename;
106END
107
108PRINT ''
109PRINT '/**********************************'
110PRINT '-- SET change db owner to OLD db owner'
111INSERT INTO #db SELECT * FROM #dbSave;
112DECLARE @databasenamemaxlen int;
113SELECT @databasenamemaxlen = MAX(LEN(databasename)) FROM #db;
114SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
115DELETE FROM #db WHERE @databasename = databasename;
116WHILE (@databasename IS NOT NULL)
117BEGIN
118    SET @sql = '';
119    SET @sql = @sql + 'USE [[databasename]];[space]EXEC dbo.sp_changedbowner @map = false, @loginame = N''[databaseowner]'';' + CHAR(13) + CHAR(10);
120    SET @sql = REPLACE(@sql, '[space]' , SPACE(@databasenamemaxlen-LEN(@databasename)));
121    SET @sql = REPLACE(@sql, '[databasename]' , @databasename);
122    SET @sql = REPLACE(@sql, '[databaseowner]', @databaseowner);
123    PRINT @sql;
124    SET @databasename  = NULL;
125    SET @databaseowner = NULL;
126    SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
127    DELETE FROM #db WHERE @databasename = databasename;
128END
129PRINT '***********************************/'
130PRINT ''
131
132PRINT '/**********************************'
133PRINT '-- SET Offline databases'
134INSERT INTO #db SELECT * FROM #dbSave;
135SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
136DELETE FROM #db WHERE @databasename = databasename;
137WHILE (@databasename IS NOT NULL)
138BEGIN
139    SET @sql = 'ALTER DATABASE [databasename] [space]SET OFFLINE WITH ROLLBACK IMMEDIATE;' + CHAR(13) + CHAR(10);
140    SET @sql = REPLACE(@sql, '[space]' , SPACE(@databasenamemaxlen-LEN(@databasename)));
141    SET @sql = REPLACE(@sql, '[databasename]' , @databasename);
142    SET @sql = REPLACE(@sql, '[databaseowner]', @databaseowner);
143    PRINT @sql;
144    SET @databasename  = NULL;
145    SET @databaseowner = NULL;
146    SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
147    DELETE FROM #db WHERE @databasename = databasename;
148END
149PRINT '***********************************/'
150PRINT ''
151PRINT '/**********************************'
152PRINT '-- SET online databases'
153INSERT INTO #db SELECT * FROM #dbSave;
154SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
155DELETE FROM #db WHERE @databasename = databasename;
156WHILE (@databasename IS NOT NULL)
157BEGIN
158    SET @sql = 'ALTER DATABASE [databasename] [space]SET ONLINE;' + CHAR(13) + CHAR(10);
159    SET @sql = REPLACE(@sql, '[space]' , SPACE(@databasenamemaxlen-LEN(@databasename)));
160    SET @sql = REPLACE(@sql, '[databasename]' , @databasename);
161    SET @sql = REPLACE(@sql, '[databaseowner]', @databaseowner);
162    PRINT @sql;
163    SET @databasename  = NULL;
164    SET @databaseowner = NULL;
165    SELECT TOP 1 @databasename = databasename, @databaseowner = databaseowner FROM #db;
166    DELETE FROM #db WHERE @databasename = databasename;
167END
168PRINT '***********************************/'

Migrer Une Base De Données

Contents

Copyright

Comments

Migrer Une Base De Données

Contents

Copyright

Related Posts

Comments