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 '***********************************/'
Comments