SQL Server Veeam backups

Had a situation where we want to use differential backups due to the size and high availability of the database servers. I found out the Veeam backups are not truly copy only and interrupt the lsn chain of backups. This effect of this is you cannot take a differential backup after a Veeam SQL backup as it will say there is no reference backup.

Information I have found:

It appears there is an issue using both VEEAM and differential SQL backups.

This is our strategy to minimize slow performance times and data file sizes in Sharepoint and SAP QA and production.

Below is the recommendations based on the VEEAM forums:

http://forums.veeam.com/veeam-backup-replication-f2/sql-do-not-truncate-logs-ignored-t20054.html

http://forums.veeam.com/veeam-backup-replication-f2/sql-backup-jobs-t15803.html

The recommendation that makes the most sense to me is:

Hi uweiss

1. Disable application aware processing, either at the job level or at the individual VM level.
2. Ensure you're not using VMware Tools Quiescence (job level)
3. Respectfully, running databases in simple recovery mode, but then performing a diff every 3 hours - you're contradicting yourself. You either need the ability to restore to point in time or you don't. You will also be wasting a lot of space with diff backups ever 3 hours.

The general rule of thumb we use is
a) Full backups on a Sunday (say 9PM)
As part of the Full backup plan, add a Cleanup files / cleanup history - files/jobs/tasks older than 2 weeks
b) Diff backups Mon-Sat (again, 9PM)
c) Transaction Log backups every 15 minutes

This gives you the ability to restore to any point in time in the last 2 weeks. Making the cleanup task part of the full backup task means you never accidentally delete a full backup required as the start of a chain. Using diffs keeps the daily backup sizes down.

cheers
Lee.

More Info:

http://www.virtuallyimpossible.co.uk/sql-server-full-and-transaction-log-backups-completing-but-differential-backups-failing-because-of-veeam-br-configuration/

SQL User Backup Scipt Update and Fixed

/*
Fixed Backup script
The root path was not set causing the directories to not be set correctly.

Again the master.dbo.xp_create_subdir is only available in 2005 and 2008

*/

USE [master]
GO

DECLARE @rootpath NVARCHAR(100);
set @rootPath = N'\[Your backup locationand a]\';

DECLARE @tDatabases TABLE
( databaseId int
);

INSERT INTO @tDatabases ( databaseId )
SELECT DISTINCT
s_mf.database_id
from sys.master_files s_mf
where s_mf.state = 0 -- ONLINE
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND NOT db_name(s_mf.database_id) in ('tempdb', 'master', 'msdb', 'model'); --eliminate temp db


DECLARE cursor_usage CURSOR FOR SELECT databaseId FROM @tDatabases;

DECLARE @dbId int;
OPEN cursor_usage;

FETCH NEXT FROM cursor_usage INTO @dbId;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT db_name(@dbId);
DECLARE @Filepath NVARCHAR(500);
declare @dbname nvarchar(50)
set @dbname = db_name(@dbId);

SET @FilePath = @RootPath + @dbName;
PRINT @FilePath

EXECUTE master.dbo.xp_create_subdir @filepath;

declare @fileName nvarchar(128)
declare @fullPath nvarchar(255)

--set the date format yyyymmddhhmm for the file name
declare @date nvarchar(10)
declare @hour nvarchar(4)
declare @minute nvarchar(4)

--ensure hour is 2 digit
set @hour = CAST(datepart(hh,getdate()) AS nvarchar(2))
if len(@hour) = 1
set @hour = '0' + CAST(datepart(hh,getdate()) AS nvarchar(2))

--ensure hour is 2 digit
set @minute = CAST(DATEPART(mi, GetDate())AS nvarchar(2))

if len(@minute) = 1
set @minute = '0' + CAST(DATEPART(mi, GetDate())AS nvarchar(2))

--set the filename
select @filename = @dbName + N'_backup_' + CONVERT( nvarchar(30), GetDate(), 112 ) + @hour + @minute + N'.bak';
select @fullPath = @filePath + '\' + @fileName
print @fileName;

BACKUP DATABASE @dbname TO DISK = @fullpath WITH NOFORMAT, NOINIT, NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 5;


--run the verify
declare @backupSetId as int
select @backupSetId = position from msdb.dbo.backupset where database_name=@dbname and backup_set_id=(select max(backup_set_id) from msdb.dbo.backupset where database_name=@dbname )

if @backupSetId is null
begin
declare @errMsg nvarchar(128)
select @errMsg = N'Verify failed. Backup information for database ' + @dbname + N' not found.'
raiserror(@errMsg, 16, 1);
end

RESTORE VERIFYONLY FROM DISK = @fullpath WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

FETCH NEXT FROM cursor_usage INTO @dbId;
END;

CLOSE cursor_usage;
DEALLOCATE cursor_usage;

All Database full backup

/*
This script is to loop through all non-temp databases and create a full backup in the
designated directory. It also verifies that the backup when complete. Each database
will be place in their own directory.

The master.dbo.xp_create_subdir only works with SQL Server 2005. It can be removed for 2000 if the directory already exists.
*/

USE [master]

GO

set @filePath = N'\\Server Backup Location\' + @dbName;

DECLARE @tDatabases TABLE

( databaseId int

--, DATABASE_NAME varchar(25)

);

INSERT INTO @tDatabases ( databaseId )

SELECT DISTINCT s_mf.database_id

from sys.master_files s_mf

where s_mf.state = 0 -- ONLINE

AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access

AND NOT db_name(s_mf.database_id) in ('tempdb'); --eliminate temp db

DECLARE cursor_usage CURSOR FOR

SELECT databaseId FROM @tDatabases;

DECLARE @dbId int;

OPEN cursor_usage;

FETCH NEXT FROM cursor_usage INTO @dbId;

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT db_name(@dbId);

declare @dbname nvarchar(50)

declare @filepath nvarchar(50)

set @dbname = db_name(@dbId);

EXECUTE master.dbo.xp_create_subdir @filepath;

declare @fileName nvarchar(128)

declare @fullPath nvarchar(255)

--set the date format yyyymmddhhmm for the file name

declare @date nvarchar(10)

declare @hour nvarchar(4)

declare @minute nvarchar(4)

--ensure hour is 2 digit

set @hour = CAST(datepart(hh,getdate()) AS nvarchar(2))

if len(@hour) = 1

set @hour = '0' + CAST(datepart(hh,getdate()) AS nvarchar(2))

--ensure hour is 2 digit

set @minute = CAST(DATEPART(mi, GetDate())AS nvarchar(2))

if len(@minute) = 1

set @minute = '0' + CAST(DATEPART(mi, GetDate())AS nvarchar(2))

--set the filename

select @filename = @dbName + N'_backup_' + CONVERT( nvarchar(30), GetDate(), 112 ) + @hour + @minute + N'.bak';

select @fullPath = @filePath + '\' + @fileName

print @fileName;

BACKUP DATABASE @dbname TO DISK = @fullpath WITH NOFORMAT, NOINIT, NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 5;

--run the verify

declare @backupSetId as int

select @backupSetId = position from msdb.dbo.backupset where database_name=@dbname and backup_set_id=(select max(backup_set_id) from msdb.dbo.backupset where database_name=@dbname )

if @backupSetId is null

begin

declare @errMsg nvarchar(128)

select @errMsg = N'Verify failed. Backup information for database ' + @dbname + N' not found.'

raiserror(@errMsg, 16, 1);

end

RESTORE VERIFYONLY FROM DISK = @fullpath WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

FETCH NEXT FROM cursor_usage INTO @dbId;

END;

CLOSE cursor_usage;

DEALLOCATE cursor_usage;

SQL Restore Database with Move

/*
This is my standard restore to a different location. This is basically the script from the help file so I don't have to find it any more.

The script assumes that the data base has been created and will be overwritten.

The first script gets the file lay out and names which needs to be inserted into the second call. It demonstrates more than one logical location in a mdf file. It can use UNC file navigation.
*/

Restore FileListonly from disk = '[File location][file name].BAK';

/*
Run the first query to get the logical name prior running the second

The file location must be relative the machine that the restore is being done so it must be a local drive as demonstrated in the sample. I have not found a way to restore from a remotely located drive.
*/

Restore Database [database name] FROM DISK = '[File location][file name].BAK'
WITH MOVE '[LogicalName]' TO 'C:\restored_databases\[filename].mdf'
, MOVE '[LogicalName_1]' To 'C:\restored_databases\[filename_1].mdf'
, MOVE '[LogicalName_log]' To 'C:\restored_databases\[filename_log].ldf'
, stats = 5
, REPLACE



----------------
Now playing: NPR - 0807195: NPR: 07-13-2008 Wait Wait... Don't Tell Me!
via FoxyTunes

SQL Purge old database backups

/*
This script works with SQL 2005 to remove old backups from a file location
xp_delete_file
is the key to this operation. This is an undocumented SQL server function.
It deletes all file older than the date given in the parameter.
I use this to implement 24 hour disk to disk backup

The TSQL assumes the full backups have an extension of BAK and transaction logs
have an extension of TRN. It will purge both transaction logs and full backups
*/

USE [master]
GO

DECLARE @tDatabases TABLE
( databaseId int
--, DATABASE_NAME varchar(25)
)

INSERT INTO @tDatabases ( databaseId )
SELECT DISTINCT s_mf.database_id
from sys.master_files s_mf
where s_mf.state = 0 -- ONLINE
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND s_mf.database_id <> 2 --eliminate temp db



DECLARE @dbId int;
Declare @DateToDelete datetime;
declare @DateString char(8);
DECLARE @filePath nvarchar(max);

SET @DateToDelete=DateAdd(day,-1,GetDate()); --The -1 in the date diff is 24 hours
set @DateString = Convert(nvarchar(30),@DateToDelete,1)+' '+ Convert(nvarchar(30),@DateToDelete,8); --Set up SQL string


SET @filePath = '\server_backup_directory' ;

DECLARE cursor_usage CURSOR FOR
SELECT databaseId FROM @tDatabases;

OPEN cursor_usage;

FETCH NEXT FROM cursor_usage INTO @dbId;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT db_name(@dbId);
DECLARE @execString nvarchar(max)
--Remove full backups
SET @execString = 'master.dbo.xp_delete_file 0, N''' + @filePath + db_name(@dbId) + '''' +',N''bak'' ,' + '''' + @DateString + ''''
PRINT @execString;
EXECUTE (@execString);
--Remove transaction logs
SET @execString = 'master.dbo.xp_delete_file 0, N''' + @filePath + db_name(@dbId) + '''' +',N''trn'' ,' + '''' + @DateString + ''''
PRINT @execString;
EXECUTE (@execString);

FETCH NEXT FROM cursor_usage INTO @dbId;
END;
CLOSE cursor_usage;
DEALLOCATE cursor_usage;