Tipps und Tricks zum Backup eines SQL Server

Tägliches Backup

Backupdatei

Oft will man sämtliche Backups eines Tages übersichtlich zusammenfassen. Dies kann beispielsweise durch eine Backupdatei erreicht werden, die das aktuelle Datum trägt. Hier ein kleines Skript, das dies demonstriert (für MS SQL Server 7.0 und 2000):

DECLARE @backname VARCHAR(128)
SET @backname = 'c:\backup\backup' + CONVERT(VARCHAR, GETDATE(), 104) + '.bak'

BACKUP DATABASE Northwind TO DISK = @backname

Erläuterung: Es wird hier die Tatsache ausgenutzt, dass T-SQL das Zusammenbauen von Zeichenketten in einer Variablen unterstützt. Hier wird einfach das aktuelle Datum (Funktion "getdate()") in einen Dateipfad eingebaut und als Variable "@backname" an den Backup-Befehl übergeben. Die Datei heißt dann "backup24.02.2002.bak" und liegt in "C:\backup".

Backupmedium

Um hier flexibler zu sein, kann die Technik der Backup-Medien hilfreich sein: Ein Backup-Medium ist ein logischer Name für eine Backup-Datei oder ein Tape. Dieser logische Name ist unabhängig vom realen Speicherort. Daher kann man einfach täglich ein neues Medium unter demselben Namen anlegen, hinter dem sich aber ein neuer physischer Name verbirgt.

Das folgende Skript demonstriert diese Technik. Es prüft, ob bereits ein Medium namens "BackMedium" vorhanden ist. Falls ja, wird es gelöscht. Dann wird es mit dem aktuellen Namen neu angelegt.

IF EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = 'BackMedium')
        BEGIN
                EXEC sp_dropdevice 'BackMedium'
        END

DECLARE @medname VARCHAR(128)
SET @medname = 'c:\backup\media' + CONVERT(VARCHAR, GETDATE(), 104) + '.bak'

EXEC sp_addumpdevice 'disk', 'BackMedium', @medname

Erläuterung: Fast dieselbe Technik wie oben, nur wird hier der generierte Name an die Prozedur "sp_addumpdevice" übergeben, mit der ein logisches Backupmedium definiert wird (hier: "media24.02.2002.bak" in "C:\backup").

Automatisches Anlegen täglicher Backupmedien

Das Anlegen täglicher Backup-Medien kann mit dem SQL Server Agent automatisiert werden. Hier ist ein solcher Auftrag (das Skript wurde nachträglich mit dem Enterprise Manager generiert):

BEGIN TRANSACTION
  DECLARE @JobID BINARY(16)
  DECLARE @ReturnCode INT
  SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Auftrag mit demselben Namen löschen (falls vorhanden)
  SELECT @JobID = job_id
  FROM   msdb.dbo.sysjobs
  WHERE (name = N'DailyBackupMedia')
  IF (@JobID IS NOT NULL)
  BEGIN
  -- Überprüfen, ob es sich um einen Multiserverauftrag handelt
  IF (EXISTS (SELECT  *
              FROM    msdb.dbo.sysjobservers
              WHERE   (job_id = @JobID) AND (server_id <> 0)))
  BEGIN
    -- Dies ist der Fall, daher Skript abbrechen
    RAISERROR (N'Auftrag ''DailyBackupMedia'' kann nicht importiert werden, da bereits ein Multiserverauftrag mit diesem Namen vorhanden ist.', 16, 1)
    GOTO QuitWithRollback
  END
  ELSE
    -- [Lokalen] Auftrag löschen
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'DailyBackupMedia'
    SELECT @JobID = NULL
  END

BEGIN

  -- Auftrag hinzufügen
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'DailyBackupMedia', @owner_login_name = N'SQLSERV\nka', @description = N'Keine Beschreibung verfügbar.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 3, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  -- Auftragsschritte hinzufügen
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Create Backup Media'
          , @command = N'if exists (select name from master.dbo.sysdevices where name = ''BackMedium'')
                            begin
                                 exec sp_dropdevice ''BackMedium''
                            end

                         declare @medname varchar(128)
                         set @medname = ''c:\backup\media'' + convert(varchar, getdate(), 104) + ''.bak''

                         exec sp_addumpdevice ''disk'', ''BackMedium'', @medname'
          , @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  -- Auftragszeitpläne hinzufügen
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Daily', @enabled = 1, @freq_type = 4, @active_start_date = 20020224, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  -- Zielserver hinzufügen
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO   EndSave
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Erläuterung: Nein, das Skript muss man nicht auf Anhieb verstehen. Selbstverständlich kann man den Auftrag auch im Enterprise Manager manuell anlegen: Ein einzelner Schritt, der das Medium anlegt (Skript s. o.), Zeitplan: täglich 0:00 Uhr.