Tool: Script zum Kopieren einer NAV-Datenbank in SQL

Hallo Zusammen,

nach vielen Hinweisen und Erklärungen zum Ablauf, war ich das Mausgeschubse leid und habe mir zum Kopieren einer Datenbank innerhalb eines SQL-Server 2012 ein Script zusammen gebaut.

Was kann das Script ?
- getestet im SQL-Server 2012
- Es funktioniert mit Objekten aus Navision 2.00, 2.60, 2009
- Es erstellt ausgehend vom Namen der Ausgangsdatenbak eine passend benannte Kopie
- es besteht die Möglichkeit den Namen der Kopie um ein eigenes Kürzel zu erweitern
- die interne Logik der Datenbank und die Anzahl der Dateien ist egal
- Es werden in der Kopie alle Datenbank und Windows Anmeldungen gelöscht
- es werden im Script hinterlegte Anmeldung für Datenbank und Windows Anmeldung erzeugt


Somit reicht es abschliessend die Kopie der Datenbank in Nav aufzurufen, die Sicherheitseinstelungen zu synchronisieren und die Kopie ist einsatzbereit.
Netter Nebeneffekt: Ausser den im Script hinterlegten Usern sieht danach keiner mehr die Datenbank

Das Ganze ist als einfache Query gebaut.
Eintragungen in den Zeilen

Code:

set @SourceDB = N'<DB-Name>'
set @Person   = N'<eig. Kürzel>'

anpassen !!!

weitere Vorraussetzungen : siehe Script

Code:

-- SQL Server Mangement Studio 2012 SP1.
-- Kopie einer Datenbank innerhalb eines SQL-Server anlegen
-- Name der Kopie aus Original-Name abgeleitet
-- weitere Vorraussetzungen :
--        läuft bei mir unter dem SQL-User sa, der auch DatenbankOwner aller Datenbanken ist
--        nutzt das Verzeichnis d:\temp
--        xp_cmdshell   ist aktiviert (http://technet.microsoft.com/de-de/library/ms175046.aspx)
--        eventuell bestehende Zieldatenbank wird überschrieben
--        Alle Benutzer der Zieldatenbank werden gelöscht
--        eine Liste von UserID wird mit "super"-Rechten in die Zieldatenbank eingetragen
--
Declare @SourceDB varchar(250)
Declare @Person   varchar(20)
set @SourceDB = N'<DB-Name>'
set @Person   = N'<eig. Kürzel>'
--
-- berechnete Werte
Declare @TargetDB varchar(250)
set @TargetDB = N'Test_'+@Person+'_'+@SourceDB

Declare @TempPath varchar(250)
set @TempPath = N'd:\Temp\Kopiebasis_'+@SourceDB+'.bak'

-- Weitere Einmal-Werte
set nocount on
Declare @ManUserList table (UserID varchar(30),UserName varchar(30))
-- Beispiel 'UserID','UserName'
-- beliebig viele Einträge möglich
insert into @ManUserList values('UserId_1','User_ID_1_Name')

Declare @ManWindowsLoginList table (UserID varchar(80) )
-- Beispiel : '\\domäne\UserID'
-- beliebig viele Einträge möglich
insert into @ManWindowsLoginList values('\\domain\user_id')


-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

Declare @SIDOutput Table (Outputzeile varchar(80))
Declare curSIDOutput cursor for
    select Outputzeile from @SIDOutput

Declare curManUserList cursor for
    select UserID,Username from @ManUserList

Declare curManWindowsLoginList cursor for
    select UserId from @ManWindowsLoginList

declare @FileListTable table
(
    LogicalName          nvarchar(128),
    PhysicalName         nvarchar(260),
    [Type]               char(1),
    FileGroupName        nvarchar(128),
    Size                 numeric(20,0),
    MaxSize              numeric(20,0),
    FileID               bigint,
    CreateLSN            numeric(25,0),
    DropLSN              numeric(25,0),
    UniqueID             uniqueidentifier,
    ReadOnlyLSN          numeric(25,0),
    ReadWriteLSN         numeric(25,0),
    BackupSizeInBytes    bigint,
    SourceBlockSize      int,
    FileGroupID          int,
    LogGroupGUID         uniqueidentifier,
    DifferentialBaseLSN  numeric(25,0),
    DifferentialBaseGUID uniqueidentifier,
    IsReadOnl            bit,
    IsPresent            bit,
    TDEThumbprint        varbinary(32)
)

DECLARE curFileLIst CURSOR FOR
        SELECT 'MOVE N''' + LogicalName + ''' TO N''' +
             replace(PhysicalName, @SourceDB, @TargetDB) + ''''
          FROM @FileListTable

Declare @v_strTEMP varchar(4000)
DECLARE @spidstr varchar(8000)
Declare @DynAusdr varchar(4000)
Declare @MoveAusdr varchar(4000)
Declare @T1 varchar(80)
Declare @T2 varchar(80)
Declare @DomainName varchar(80)
Declare @DomainUser varchar(80)
Declare @DomainSID varchar(80)

Declare @ManCount table(Zaehler int)
Declare curManCount cursor for
    select Zaehler from @ManCount
Declare @VarManCount int

-- Rechtsklick auf die Quell-Datenbank
-- > Tasks
-- > Sichern
-- > Festplatte auswählen, DB sichern, fertig.
set @DynAusdr= 'BACKUP DATABASE ['+@SourceDB+']
    TO  DISK = N'''+@TempPath+'''
   WITH NOFORMAT, INIT,
   NAME = N''Nur für Kopiervorgang'',
   SKIP, NOREWIND, NOUNLOAD,STATS=10'
print @DynAusdr
exec(@DynAusdr)


-- Dann Rechtsklick auf Datenbanken
-- > Dateien und Dateigruppen wiederherstellen
-- > Wiederherzustellendes Ziel
-- > neue DB eintragen
-- > unten bei der Quelle wählst Du die Quell-Datenbank aus
-- > unten die Sicherung auswählen
-- > OK, fertig

if db_id(@TargetDB) is not null
begin    
    SET @spidstr = ''
   SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
        FROM master..sysprocesses WHERE dbid=db_id(@TargetDB)
   print @spidstr
    EXEC(@spidstr)
end



set @DynAusdr = 'RESTORE FILELISTONLY
    FROM DISK = N'''+@TempPath+'''
   WITH FILE = 1'
print @DynAusdr
insert into @FileListTable exec(@DynAusdr)

set @DynAusdr = 'RESTORE DATABASE ['+@TargetDB+']
    FROM DISK = N'''+@TempPath+'''
   WITH  FILE = 1,
   '
   OPEN curFileList
    FETCH NEXT FROM curFileList into @v_strTEMP
    WHILE @@Fetch_Status = 0
    BEGIN
        SET @DynAusdr = @DynAusdr + @v_strTEMP + ',
   '
        FETCH NEXT FROM curFileList into @v_strTEMP
    END
   CLOSE curFileList
   DEALLOCATE curFileList

SET @DynAusdr = @DynAusdr + 'NOUNLOAD,  REPLACE,STATS=10'
print @DynAusdr
exec(@DynAusdr)

set @DynAusdr = N'cmd.exe /c del '+ @TempPath
exec XP_cmdshell @DynAusdr , no_output


if object_id(@Targetdb+'.dbo.benutzer') is not NULL
begin
    print '1' + object_id(@Targetdb+'.dbo.benutzer')

    set @DynAusdr = 'Truncate table ['+@Targetdb+'].[dbo].[benutzer]'
    print @DynAusdr
    exec(@DynAusdr)
    set @DynAusdr = 'Truncate table ['+@Targetdb+'].[dbo].[Mitglied von]'
    print @DynAusdr
    exec(@DynAusdr)

   set @DynAusdr = 'select count(COLUMN_NAME) from ['+@Targetdb+'].INFORMATION_SCHEMA.Columns Where '+
                   'TABLE_NAME=''benutzer'' AND COLUMN_NAME=''Alternativbenutzer'''
   print @DynAusdr
   insert into @ManCount exec(@DynAusdr)
   OPEN curManCount
   FETCH NEXT FROM curManCount into @VarManCount   
   CLOSE curManCount   
   print N'Spalte vorhanden : '+cast(@VarManCount as varchar(5))
   open curManUserList
   FETCH NEXT FROM curManUserList INTO @T1,@T2

   if @VarManCount <> 0
   begin;
       -- Navision 2.00 Objekte
      WHILE @@FETCH_STATUS = 0
      BEGIN
         set @DynAusdr = 'insert into ['+@Targetdb+'].[dbo].[benutzer]'+
                        '([Benutzer Id],[Name],[Ablaufdatum],[Kennwort],[Alternativbenutzer])'+
                     ' values(N'''+@T1+''',N'''+@T2+''',''01.01.2999'','''','''')'
         print @DynAusdr
         exec(@DynAusdr)   
         set @DynAusdr = 'insert into ['+@Targetdb+'].[dbo].[Mitglied von]'+
                        '([Benutzer Id],[Gruppen ID],[Mandant])'+
                     ' values(N'''+@T1+''',N''SUPER'','''')'
         print @DynAusdr
         exec(@DynAusdr)
         FETCH NEXT FROM curManUserList INTO @T1,@T2
      end
   end else begin
       -- Navision 2.60 Objekte
      WHILE @@FETCH_STATUS = 0
      BEGIN
         set @DynAusdr = 'insert into ['+@Targetdb+'].[dbo].[benutzer]'+
                        '([Benutzer Id],[Name],[Ablaufdatum],[Kennwort])'+
                     ' values(N'''+@T1+''',N'''+@T2+''',''01.01.2999'','''')'
         print @DynAusdr
         exec(@DynAusdr)   
         set @DynAusdr = 'insert into ['+@Targetdb+'].[dbo].[Mitglied von]'+
                        '([Benutzer Id],[Rollen ID],[Mandant])'+
                     ' values(N'''+@T1+''',N''SUPER'','''')'
         print @DynAusdr
         exec(@DynAusdr)
         FETCH NEXT FROM curManUserList INTO @T1,@T2
      end
   end
   CLOSE curManUserList
   DEALLOCATE curManUserList
end
if object_id(@Targetdb+'.dbo.user') is not NULL
begin
    -- Nav 2009 Objekte
    print '2'
   print object_id(@Targetdb+'.dbo.user')
    set @DynAusdr = N'Truncate table ['+@Targetdb+'].[dbo].[user]'
    print @DynAusdr
    exec(@DynAusdr)   
    set @DynAusdr = N'Truncate table ['+@Targetdb+'].[dbo].[Member of]'
    print @DynAusdr
    exec(@DynAusdr)   
   open curManUserList
   FETCH NEXT FROM curManUserList INTO @T1,@T2
   WHILE @@FETCH_STATUS = 0
   BEGIN
      set @DynAusdr = 'insert into ['+@Targetdb+'].[dbo].[user]'+
                      '([User Id],[Name],[Expiration Date],[Password])'+
                  ' values(N'''+@T1+''',N'''+@T2+''',''01.01.2999'','''')'
      print @DynAusdr
      exec(@DynAusdr)   
      set @DynAusdr = 'insert into ['+@Targetdb+'].[dbo].[Member of]'+
                      '([User Id],[Role ID],[Company])'+
                  ' values(N'''+@T1+''',N''SUPER'','''')'
      print @DynAusdr
      exec(@DynAusdr)
      FETCH NEXT FROM curManUserList INTO @T1,@T2
   end
   CLOSE curManUserList
   DEALLOCATE curManUserList
end

if object_id(@Targetdb+'.dbo.Windows Login') is not NULL
begin
    -- scheint bei 2.00 , 2.60 und 2009 identisch zu sein
    print '3'
   print object_id(@Targetdb+'.dbo.Windows Login')
   set @DynAusdr = 'Truncate table ['+@Targetdb+'].[dbo].[Windows Login]'
    print @DynAusdr
    exec(@DynAusdr)
   set @DynAusdr = 'Truncate table ['+@Targetdb+'].[dbo].[Windows Access Control]'
    print @DynAusdr
    exec(@DynAusdr)

   open curManWindowsLoginList
   FETCH NEXT FROM curManWindowsLoginList INTO @T1
   WHILE @@FETCH_STATUS = 0
   BEGIN
      set @T1 = substring(@T1,3,LEN(@T1)-2)
      set @DomainName = substring(@T1,1,charindex('\',@T1)-1)

      set @DomainUser =substring(@T1,charindex('\',@T1)+1,LEN(@T1)-charindex('\',@T1))

        set @DynAusdr = N'wmic useraccount where (name='''+@DomainUser+''' and domain='''+@DomainName+''') get name,sid'
      print @DynAusdr
      insert into @SIDOutput exec XP_cmdshell @DynAusdr
      
      open curSIDOutput
         fetch next from curSIDOutput into @T1
         fetch next from curSIDOutput into @T1
         set @DomainSID = substring(@T1,charindex(' ',@T1)+2,len(@T1)-charindex(' ',@T1)-4)
         set @DynAusdr = 'insert into ['+@Targetdb+'].[dbo].[Windows Login](SID) values('''+@DomainSID+''')'
         print @DynAusdr
         exec(@DynAusdr)
         set @DynAusdr = 'insert into ['+@Targetdb+'].[dbo].[Windows Access Control]'+
                     '([Login SID],[Role ID],[Company Name]) values('+
                     ''''+@DomainSID+''',N''SUPER'','''')'
         print @DynAusdr
         exec(@DynAusdr)
      close curSIDOutput      
      Delete from @SIDOutput
        FETCH NEXT FROM curManWindowsLoginList INTO @T1
   end
   CLOSE curManWindowsLoginList
   DEALLOCATE curManWindowsLoginList   
end
GO