Empfehlung Backupsoftware für MS-SQL-Datenbank

USE [master]
GO

/**** Object:  StoredProcedure [dbo].[sp_BackupDatabases]    Script Date: 04/02/2015 16:29:49 ****/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author: Microsoft
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================

CREATE PROCEDURE [dbo].[sp_BackupDatabases]
 @databaseName sysname = null,
 @backupType CHAR(1),
 @backupLocation nvarchar(200)
AS

 SET NOCOUNT ON;

 DECLARE @DBs TABLE
 (
 ID int IDENTITY PRIMARY KEY,
 DBNAME nvarchar(500)
 )

 -- Pick out only databases which are online in case ALL databases are chosen to be backed up
 -- If specific database is chosen to be backed up only pick that out from @DBs
 INSERT INTO @DBs (DBNAME)
 SELECT Name FROM master.sys.databases
 where state=0
 AND name=@DatabaseName
 OR @DatabaseName IS NULL
 ORDER BY Name

 -- Filter out databases which do not need to backed up
 IF @backupType='F'  
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')  
 END
 ELSE IF @backupType='D'  
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')  
 END
 ELSE IF @backupType='L'  
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')  
 END
 ELSE
 BEGIN
 RETURN
 END

 -- Declare variables
 DECLARE @BackupName varchar(100)
 DECLARE @BackupFile varchar(100)
 DECLARE @DBNAME varchar(300)
 DECLARE @sqlCommand NVARCHAR(1000)
 DECLARE @dateTime NVARCHAR(20)
 DECLARE @Loop int

 -- Loop through the databases one by one
 SELECT @Loop = min(ID) FROM @DBs

 WHILE @Loop IS NOT NULL
 BEGIN

-- Database Names have to be in [dbname] formate since some have - or _ in their name
 SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'  

-- Set the current date and time n yyyyhhmmss format
 SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/',) + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':',)  

-- Create backup filename in path\filename.extension format for full,diff and log backups
 IF @backupType = 'F'  
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',),']',)+ '_FULL_'+ @dateTime+ '.BAK'  
 ELSE IF @backupType = 'D'  
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',),']',)+ '_DIFF_'+ @dateTime+ '.BAK'  
 ELSE IF @backupType = 'L'  
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',),']',)+ '_LOG_'+ @dateTime+ '.TRN'  

-- Provide the backup a name for storing in the media
 IF @backupType = 'F'  
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',),']',) +' full backup for '+ @dateTime  
 IF @backupType = 'D'  
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',),']',) +' differential backup for '+ @dateTime  
 IF @backupType = 'L'  
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',),']',) +' log backup for '+ @dateTime  

-- Generate the dynamic SQL command to be executed

 IF @backupType = 'F'  
 BEGIN
 SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '+@BackupFile+ ' WITH INIT, NAME= ' +@BackupName+', NOSKIP, NOFORMAT'  
 END
 IF @backupType = 'D'  
 BEGIN
 SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '+@BackupFile+ ' WITH DIFFERENTIAL, INIT, NAME= ' +@BackupName+', NOSKIP, NOFORMAT'  
 END
 IF @backupType = 'L'  
 BEGIN
 SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+ ' TO DISK = '+@BackupFile+ ' WITH INIT, NAME= ' +@BackupName+', NOSKIP, NOFORMAT'  
 END

-- Execute the generated SQL command
 EXEC(@sqlCommand)

-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

END
GO

sqlcmd -S localhost\Instanzenname -i c:\sqldatei.sql -o d:\ausgabe.log -b

sqlcmd -S Servername\Instanz -U sa -P sa -i c:\sqldatei.sql -o d:\ausgabe.log -b

7za.exe a -r -t7z -mx9 -mmt2 d:\zielarchiv.7z d:\quellverzeichnis\*.*

use master;
go
DECLARE UserDatabases_CTE_Cursor Cursor
FOR
-- Selecting user database names.

select name as DatabaseName from sys.sysdatabases where ([dbid] > 4) and ([name] not like '$')  
OPEN UserDatabases_CTE_Cursor

DECLARE @dbName varchar(100);
DECLARE @backupPath varchar(100);
DECLARE @backupQuery varchar(500);

-- make sure that the below path exists
set @backupPath = 'C:\DBBACKUP\'  
Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

While (@@FETCH_STATUS <> -1)
BEGIN
-- Backup SQL statement
set @backupQuery =  'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + '_[' + REPLACE( convert(varchar, getdate(), 109), ':', '-') + '].bak'''  
-- Print SQL statement
print @backupQuery
-- Execute backup script
EXEC (@backupQuery)
-- Get next database
Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName
END

CLOSE UserDatabases_CTE_Cursor
DEALLOCATE UserDatabases_CTE_Cursor
GO

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 = 'C:\DBBACKUP\'    

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
   + '_'   
   + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')  

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')   
   AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.TRN'    
       --BACKUP LOG @name TO DISK = @fileName  WITH TRUNCATE_ONLY
	BACKUP LOG @name TO DISK = @fileName
	
       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor