Tworzenie kopii zapasowej wszystkich baz danych w SQL Server

Mam serwer DB Microsoft SQL Server 2005. Na serwerze DB mam około 250 baz danych użytkowników. Muszę wykonać kopię zapasową wszystkich tych baz danych. Ponieważ ręczne tworzenie kopii zapasowej zajmuje dużo czasu, szukam skryptu Batch lub skryptu DB, który automatycznie wykona kopię zapasową wszystkich 250 baz danych. Czy ktoś może w tym pomóc?

Uwaga: najpierw utwórz folder na D:dysku. (np. D:\User_DataBackup\)

Krok 1: Utwórz procedurę podaną poniżej.

Create PROCEDURE [dbo].[UserDataBaseBackUp]
AS

BEGIN
SET NOCOUNT ON;

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'D:\User_DataBackup\' -- as same as your created folder' 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)
DECLARE db_cursor CURSOR FOR
  SELECT name
  FROM MASTER.dbo.sysdatabases
  WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
  BACKUP DATABASE @name TO DISK = @fileName
  FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END

Krok 2: Wykonaj powyższą procedurę.

 EXEC [UserDataBaseBackUp]

Możesz również zaplanować tę procedurę. Mam nadzieję, że ta procedura zostanie przetestowana.

Opracuj plan konserwacji

SQL Server ma tę niesamowitą funkcję, dzięki której stworzy skrypt i zadanie dla Ciebie

• Krok 1:
  Kliknij prawym przyciskiem myszy Maintenance PlanpodManagement

  

• Krok 2:
  Nazwij swój plan

  

• Krok 3:
  Wybierz zadanie kopii zapasowej bazy danych

  

• Krok 4:
  Skonfiguruj zadanie, wybierz bazy danych, lokalizację folderu, typ kopii zapasowej (pełny, różnicowy, dziennik transakcji) itp.

  

• Krok 5:
  Skonfiguruj harmonogram zadań

  

Polecam konieczności oddzielnego planu systemowych baz danych i swoich klientów użytkownika bazy danych , aby uniknąć jakiegokolwiek problemu

Linki pomocnicze:

• Konfigurowanie planu konserwacji do tworzenia kopii zapasowych baz danych
• Zadanie kopii zapasowej bazy danych (plan konserwacji)

Wiem na pewno, że prowadzenie wielu baz danych za pomocą SSMS może być nieco mylące, ale jest proste.

Mogę polecić bardziej skuteczny sposób, czyli użycie skryptu konserwacji Oli Hallengren . To jest bardzo fajne i bardzo skuteczne. I możesz zrobić znacznie więcej niż tylko tworzenie kopii zapasowych wszystkich baz danych, możesz wykonywać wszystkie rodzaje procedur konserwacyjnych.

Na przykład możesz wykonać kopię zapasową wszystkich baz danych, skompresować je i zaszyfrować za pomocą wybranego przez siebie certyfikatu, za pomocą takiego polecenia (i wszystkie są opcjonalne, a szyfrowanie i kompresja nie będzie działać na SQL Server 2005, ale myślę, że to będzie pokaż elastyczność i siłę skryptu Oli):

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'MyCertificate'

Pracując nad odpowiedzią JP, dodałem parametr, aby przekazać katalog docelowy (i całkiem możliwe, że dodam więcej opcji):

Create PROCEDURE [dbo].[UserDataBaseBackUp] (
  @OutputDir varchar(255)
) AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @name VARCHAR(50) -- database name
    DECLARE @path VARCHAR(256) -- path for backup files
    DECLARE @fileName VARCHAR(256) -- filename for backup
    DECLARE @fileDate VARCHAR(20) -- used for file name
    SET @path = @OutputDir
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)
    PRINT 'Starting Backups'
    DECLARE db_cursor CURSOR FOR
        SELECT name FROM MASTER.dbo.sysdatabases
            WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
        OPEN db_cursor
            FETCH NEXT FROM db_cursor INTO @name
            WHILE @@FETCH_STATUS = 0 BEGIN
                SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
                    PRINT 'Starting Backup For ' + @name
                    BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT
                FETCH NEXT FROM db_cursor INTO @name
            END
        CLOSE db_cursor
    DEALLOCATE db_cursor
    PRINT 'Backups Finished'
END
GO

Więc wtedy:

EXEC UserDataBaseBackUp @OutputDir = 'F:\Backups\SQL Databases\'

Możesz użyć instrukcji SELECT lub kursora w następujący sposób:

DECLARE @PathForBackUp VARCHAR(255)
SET @PathForBackUp = 'F:\Backup\User DB\'

SELECT 'BACKUP DATABASE [' + name + '] TO  DISK = N''' + @PathForBackUp + '' + name + '.bak''
WITH NOFORMAT, NOINIT,  NAME = N''' + name + '_FullBackUp'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 5'
FROM sys.databases
WHERE database_id > 4

LUB

DECLARE @DBName VARCHAR(255)  
DECLARE @PathForBackUp VARCHAR(255) 
DECLARE @FileName VARCHAR(255)  
DECLARE @DateFile VARCHAR(255)
DECLARE @SQL NVARCHAR(2048) 
SET @PathForBackUp = 'F:\Backup\User DB\'  
SET @DateFile = REPLACE(REPLACE(CONVERT(VARCHAR(20),GETDATE(),120) ,' ','T'), ':','') 

DECLARE BACKUPING CURSOR FOR   
SELECT name  
FROM master.dbo.sysdatabases WHERE dbid > 4 

OPEN BACKUPING    
FETCH NEXT FROM BACKUPING INTO @DBName    
WHILE @@FETCH_STATUS = 0    

BEGIN    
        SET @FileName = @PathForBackUp + @DBName + '_' + @DateFile + '.BAK'  
    SET @SQL = 'BACKUP DATABASE '+@DBName+ ' TO DISK = '''+@FileName+''' WITH COMPRESSION ' 
    PRINT @SQL 
    EXECUTE sp_executesql @sql   
    FETCH NEXT FROM BACKUPING INTO @DBName  

END    

CLOSE BACKUPING    
DEALLOCATE BACKUPING 

Możesz także użyć programu PowerShell, jak pokazano w sekcji Używanie programu PowerShell do tworzenia kopii zapasowych wszystkich baz danych użytkownika przez Buck Woody:

# Performs a Full backup followed by a transaction log backup on all user databases

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'BWOODY1\SQL2K8'
$bkdir = $s.Settings.BackupDirectory
$dbs = $s.Databases
$dbs | foreach-object {
    $db = $_

    if ($db.IsSystemObject -eq $False -and $db.IsMirroringEnabled -eq $False) {
        $dbname = $db.Name
        $dt = get-date -format yyyyMMddHHmmss
        $dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
        $dbbk.Action = 'Database'
        $dbbk.BackupSetDescription = "Full backup of " + $dbname
        $dbbk.BackupSetName = $dbname + " Backup"
        $dbbk.Database = $dbname
        $dbbk.MediaDescription = "Disk"
        $dbbk.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", 'File')
        $dbbk.SqlBackup($s)
        if ($db.DatabaseOptions.RecoveryModel -ne 'Simple') {
            $dt = get-date -format yyyyMMddHHmmss
            $dbtrn = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
            $dbtrn.Action = 'Log'
            $dbtrn.BackupSetDescription = "Trans Log backup of " + $dbname
            $dbtrn.BackupSetName = $dbname + " Backup"
            $dbtrn.Database = $dbname
            $dbtrn.MediaDescription = "Disk"
            $dbtrn.Devices.AddDevice($bkdir + "\" + $dbname + "_tlog_" + $dt + ".trn", 'File')
            $dbtrn.SqlBackup($s)
            }
        }     
    }