MSSQL Generere detach og attach script til flytte mange databaser

Har du behov for flytte mange databaser i samarbejde med migrering til en nyere MSSQL Server version eller fordi du blot vil skifte data lokation til et andet drev, så kan du generere detach og attach script for alle dine databaser.

F.eks. Plan:

  • Jeg migrerer alle 200 databaser fra en SQL2008 til SQL2012 server.
  • Nuværende drev hedder: C og F, da jeg har databaser begge steder liggende.
  • SQL2012 har E: Drev hvor alle databaserne skal kopieres til.
  • Jeg generere først en liste over alle databaser, som skal køres på SQL2008.
  • Jeg generere en detach script for alle databaserne, som skal køres på SQL2008.
  • Jeg generere en attach script for alle databaserne, som skal køres på SQL2012.
  • Jeg har sat New Query til at skrive ud til en Tekst fil, så jeg nemt kan benytte dem.
    I punkt. 2, 3 og 4 efter du har trykket på New Query så i menuen Query -> Result To -> Results to Text.

 

  1. Login til din SQL2008 server og start SQL Management.
  2. Vi skal generere en liste over alle databaser, tryk på New Query og skriv og tryk Execute.
    SELECT DISTINCT DB_NAME(dbid)
    FROM master.dbo.sysaltfiles
    WHERE SUBSTRING(filename,1,1) IN ('C','F')
    GO
    SELECT DISTINCT DB_NAME(dbid)

    FROM master.dbo.sysaltfiles
    WHERE SUBSTRING(filename,1,1) IN ('C','F')
    AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
    GO
    IF EXISTS (SELECT 1
    FROM master.dbo.sysaltfiles
    WHERE SUBSTRING(filename,1,1) IN ('C','F')
    AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
    AND DB_NAME(dbid) IN ('master','tempdb','msdb','model')
    )
    BEGIN
    SELECT DISTINCT DB_NAME(dbid) AS 'There are system databases on these drives:'
    FROM master.dbo.sysaltfiles
    WHERE SUBSTRING(filename,1,1) IN ('C','F')
    AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
    AND DB_NAME(dbid) IN ('master','tempdb','msdb','model')
    END
    GO
    SELECT DISTINCT DB_NAME(dbid)

    FROM master.dbo.sysaltfiles
    WHERE SUBSTRING(filename,1,1) IN ('C','F')
    AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
    AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')
    GO
  3. Vi skal generere en detach liste over din databaser, tryk på New Query og skriv og tryk Execute.
    SELECT DISTINCT'exec sp_detach_db ''' + DB_NAME(dbid) + ''';'
    FROM master.dbo.sysaltfiles
    WHERE SUBSTRING(filename,1,1) IN ('C','F')
    AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
    AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')
    GO
  4. Vi skal generere en attach liste over din databaser, tryk på New Query og skriv og tryk Execute.
    SET NOCOUNT ON
    DECLARE @cmd VARCHAR(MAX),
    @dbname VARCHAR(200),
    @prevdbname VARCHAR(200)
    SELECT @cmd = '', @dbname = ';', @prevdbname = ''
    CREATE TABLE #Attach
    (Seq INT IDENTITY(1,1) PRIMARY KEY,
    dbname SYSNAME NULL,
    fileid INT NULL,
    filename VARCHAR(1000) NULL,
    TxtAttach VARCHAR(MAX) NULL
    )
    INSERT INTO #Attach
    SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid, filename, CONVERT(VARCHAR(MAX),'') AS TxtAttach
    FROM master.dbo.sysaltfiles
    WHERE dbid IN (SELECT dbid FROM master.dbo.sysaltfiles
    WHERE SUBSTRING(filename,1,1) IN ('C','F'))
    AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
    AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')
    ORDER BY dbname, fileid, filename
    UPDATE #Attach
    SET @cmd = TxtAttach =
    CASE WHEN dbname <> @prevdbname
    THEN CONVERT(VARCHAR(200),'exec sp_attach_db @dbname = N''' + dbname + '''')
    ELSE @cmd
    END +',@filename' + CONVERT(VARCHAR(10),fileid) + '=N''' + filename +'''',
    @prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END,
    @dbname = dbname
    FROM #Attach WITH (INDEX(0),TABLOCKX)
    OPTION (MAXDOP 1)
    SELECT TxtAttach
    FROM
    (SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach
    GROUP BY dbname) AS x
    DROP TABLE #Attach
    GO
  5. Nu Har vi generet detach og attach script, nu skal vi detach:
    åben punkt.3 Tekst fil og Copy/Paste indholdet i New Query.
    det skulle se sådan ud Eks:
    exec sp_detach_db 'UMDB01';
    exec sp_detach_db 'UMDB02';
    exec sp_detach_db 'UMDB03';
    exec sp_detach_db 'UMDB04';
  6. Nu skulle alle databaserne være detached og du skal kopiere alle din databaser (MDF og LDF) til ny server og drev.
  7. Efter punkt 6 er fuldført, så skal du Attache alle databaser, men før attach skal du åbne din attach script fra punkt. 4 i notepad og skift drev/stig til MDF og LDF ud til den nye drev/stig.
    du kan evt. benytte notepads søg og erstat funktion. (CTRL+H).
  8. Efter punkt 7 er fuldført så kopier indholdet fra din Attach tekst-fil, og Copy/Paste det i New Query på din SQL2012 server.
    det skulle se sådan ud Eks.
    exec sp_attach_db @dbname = N'UMDB01',@filename1=N'E:\Databases\UMDB01.mdf',@filename2=N'E:\Databases\UMDB01.ldf'
    exec sp_attach_db @dbname = N'UMDB02',@filename1=N'E:\Databases\UMDB02.mdf',@filename2=N'E:\Databases\UMDB02.ldf'
    exec sp_attach_db @dbname = N'UMDB03',@filename1=N'E:\Databases\UMDB03.mdf',@filename2=N'E:\Databases\UMDB03.ldf'
    exec sp_attach_db @dbname = N'UMDB04',@filename1=N'E:\Databases\UMDB04.mdf',@filename2=N'E:\Databases\UMDB04.ldf'

Skriv et svar

Din e-mailadresse vil ikke blive publiceret. Krævede felter er markeret med *