Migrer une base de données
Rédigé par Sozezzo - - Aucun commentaireMigrer 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 '***********************************/'