Showing posts with label backups. Show all posts
Showing posts with label backups. Show all posts

Thursday, 16 June 2011

Backup History Performance

I recently has cause to cleanup the backup history on a SQL 2000 box. Not an issue, standard DBA fare really. SQL 2000 never had history cleanup options in the maintainence plans. Therefore we have to create TSQL jobs to remove backup history.

Given there was several years of backup history I started to clear backup history using a nibble delete approach. This proved to be slower than I anticipated so I started investigating further.

The quickest option would have been to directly modify the system tables, truncating them. Whilst constraints prevent this, there are solutions out there that tell you in which order to clear down the underlying tables. Pradeep Adiga is one of many to blog on pruning backup history and the fact that only 4 indexes exist on the 8 backup tables in MSDB.

Of all the scripts for additional MSDB indexes, Geoff Hiten provides the most complete solution in his post MSDB Performance Tuning. A script for MSDB indexes is located there which greatly improves the time to perform housekeeping on the backup tables.

I should include it in my build scripts really, and roll the indexes out as standard.



Friday, 23 July 2010

Filegroup Backups

A quick runthrough experimenting with Filegroup Backups

BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY' 
TO DISK = 'c:\Adventureworks.BAK'

Msg 3004, Level 16, State 1, Line 1
The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

So , to use this technique, you can't use SIMPLE recovery mode.
Why am i using SIMPLE? - It's a development box!
Setting to FULL resolves this -

ALTER DATABASE [AdventureWorks] SET RECOVERY FULL WITH NO_WAIT
GO

BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY' 
TO DISK = 'c:\Adventureworks.BAK'

Processed 21280 pages for database 'Adventureworks', file 'AdventureWorks_Data' on file 2.
Processed 1 pages for database 'Adventureworks', file 'AdventureWorks_Log' on file 2.
BACKUP DATABASE...FILE= successfully processed 21281 pages in 8.921 seconds (18.636 MB/sec).

Using the system table sys.filegroups, i can build a list of backup commands to backup all filegroups, like this ...


DECLARE @backupPath VARCHAR(500)
SET @backupPath = 'd:\sqlbackups\' 

DECLARE @backuptimestamp VARCHAR(30)
SET @backuptimestamp =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),20),'-',''),':',''),' ','_')

SELECT 'BACKUP DATABASE [' + DB_NAME()+'] FILEGROUP = ''' + name + ''' TO DISK = ''' + @backupPath + @backuptimestamp + '_' + DB_NAME() + '_' + name +'.BAK''' + ' WITH COMPRESSION ' AS BACKUPCOMMAND
FROM sys.filegroups
 
NB : My commands feature the compression setting as am on SQL 2008 Enterprise.

Thursday, 3 June 2010

What files are in my backup?

An oversimplified example here , but revisiting a DBA basic as moving datafiles info a new configuration.

RESTORE FILELISTONLY
FROM DISK = 'c:\AdventureWorks2008.bak'


This lists the file locations and sizes of data files inside a backup.
By default, i.e. without specifying WITH MOVE, these are the locations that a RESTORE operation will move data back to.

My real scenario has a fact table partitioned into years and over multiple data files.


MS SQL Tips : What is in your SQL Server backup files?

Thursday, 20 August 2009

Testing SQL Version

A simple ammend to my backup stored procedure today.
If it's on a sql 2008 box, use compression and increase the BUFFERCOUNT.

IF CHARINDEX('Sql Server 2008', @@VERSION) <> 0
  BEGIN
  SET @Backupcommand = @Backupcommand + ' ,COMPRESSION, BUFFERCOUNT = 250 '
  END

Wednesday, 5 August 2009

SQL 2008 : Pushing Backups Further

I blogged here about using BUFFERCOUNT to speed up database backups.
Here, I attempted to use MAXTRANSFERSIZE to do more.

The command...

BACKUP DATABASE [ImportData]
TO  DISK = N'd:\ImportData.bak'
WITH NOFORMAT, INIT,  NAME = N'ImportData Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, COMPRESSION, BUFFERCOUNT = 150 , MAXTRANSFERSIZE = 1048576
GO


The default value for MAXTRANSFERSIZE is 1MB (1048576 bytes).
Possible values are multiples of 65536 bytes (64 KB) ranging up to 4MB (4194304 bytes).

The results >

MAXTRANSFERSIZE = 1048576
Processed 216 pages for database 'ImportData', file 'ImportData_log2' on file 1.
BACKUP DATABASE successfully processed 1330824 pages in 155.348 seconds (66.927 MB/sec).

MAXTRANSFERSIZE = 2097152
Processed 458 pages for database 'ImportData', file 'ImportData_log2' on file 1.
BACKUP DATABASE successfully processed 1331090 pages in 145.383 seconds (71.529 MB/sec).

MAXTRANSFERSIZE = 3145728
Processed 201 pages for database 'ImportData', file 'ImportData_log2' on file 1.
BACKUP DATABASE successfully processed 1330913 pages in 145.512 seconds (71.456 MB/sec).

MAXTRANSFERSIZE = 4194304
BACKUP DATABASE successfully processed 1330780 pages in 148.087 seconds (70.206 MB/sec).

The conclusion >

A lot of messing about to only save 10 seconds.
Oh, and performing the backups in this way stressed the server out and it refused connections during the backup period.
Think I'll leave well alone...

Monday, 20 July 2009

SQL 2008 : Backup Compression Optimizations

Inspired by this SQL Cat article I decided to see how quickly I could potentially perform backups.
I'm using the NUL output so backups are not written to disk.

1st test : Default Compression settings >
BACKUP DATABASE MyDatabase TO DISK = 'NUL' WITH COMPRESSION

Processed 1222736 pages for database 'MyDatabase ', file 'MyDatabase ' on file 1.
Processed 506 pages for database 'MyDatabase ', file 'MyDatabase_log2' on file 1.
BACKUP DATABASE successfully processed 1223242 pages in 160.234 seconds (59.641 MB/sec).

2nd test : Up Buffercount to 50 >
BACKUP DATABASE MyDatabase TO DISK = 'NUL' WITH COMPRESSION, BUFFERCOUNT = 50

Processed 1222768 pages for database 'MyDatabase ', file 'MyDatabase ' on file 1.
Processed 259 pages for database 'MyDatabase ', file 'MyDatabase_log2' on file 1.
BACKUP DATABASE successfully processed 1223027 pages in 107.928 seconds (88.530 MB/sec).

3rd test : Up Buffercount to 150
BACKUP DATABASE MyDatabase TO DISK = 'NUL' WITH COMPRESSION, BUFFERCOUNT = 150

Processed 1222800 pages for database 'MyDatabase ', file 'MyDatabase ' on file 1.
Processed 167 pages for database 'MyDatabase ', file 'MyDatabase_log2' on file 1.
BACKUP DATABASE successfully processed 1222967 pages in 98.587 seconds (96.913 MB/sec).

4th test : Up Buffercount to 250 >
BACKUP DATABASE MyDatabase TO DISK = 'NUL' WITH COMPRESSION, BUFFERCOUNT = 250

Processed 1222832 pages for database 'MyDatabase ', file 'MyDatabase ' on file 1.
Processed 263 pages for database 'MyDatabase ', file 'MyDatabase_log2' on file 1.
BACKUP DATABASE successfully processed 1223095 pages in 96.788 seconds (98.725 MB/sec).

Miniscule Improvement (2s) on my system between BufferCount of 150 and 250

Links :
http://sqlcat.com/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx

http://blogs.msdn.com/sqlcat/archive/2008/03/02/backup-more-than-1gb-per-second-using-sql2008-backup-compression.aspx

Friday, 22 August 2008

Backup / Restore Corruption

" Server: Msg 3013, Level 16, State 1, Line 1 The backup data at the end of 'devicename' is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets. Server: Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. "

The backup file being restored/written is damaged due to an error (write error during the backup or physical media error).
* This can occur when restoring from a network source too, hence maybe worth attempting to copy locally first.

If you're trying to backup to an existing file and don't care about the existing data (as you are overwriting it anyway) , replace it like this >

BACKUP DATABASE db1 TO DISK='C:\db1.bak' with FORMAT

If you're attempting a restore (gulp), it's a 2 step process.
1) Get the file number of the backup set within the backup file >

RESTORE HEADERONLY FROM DISK='C:\db1.bak'

2) Having identified a backup set (in this case number 3), try and restore it from the backup set manually >

RESTORE DATABASE db1 FROM DISK='C:\db1.bak WITH FILE = 3

Wednesday, 4 October 2006

2005 Maintenance Plan - Error 14234

Attempting to set up a simple backup job on another team's sql box. Went through all the wizard steps to create a backup job for them. Failed at the final hurdle, with >

Create maintenance plan failed.Additional information:Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The specified '@subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error 14234)

This is due to not having Integration Services installed.

Bizarre that maintenance plans should need them, but there you go! Mind you, it was an unpatched system that I'm not responsible for, so maybe this error becomes more obvious in a later release.

http://support.microsoft.com/kb/909036

Friday, 7 July 2006

Restore TSQL

RESTORE DATABASE [databasename]
FROM DISK = N'c:\database.bak'
WITH MOVE 'databasename' TO 'D:\data\databasename.mdf',
MOVE 'databasename_log' TO 'E:\logs\databasename.ldf', NOUNLOAD, STATS = 10
GO

Thursday, 1 June 2006

Removing Database Backup History

The msdb database stores (amongst other things) the history of backups performed on the sql instance.
Once a backup has been archived, i.e. moved away from the location it was originally written to, there is no need to keep the record of the backup. Infact, these records build up, increasing the size of the msdb database (quickly if frequent transaction log backups are performed).

To see the date range of backup history you have, us this query -
use msdb
SELECT 
  a.name
, MIN(b.backup_finish_date) EarliestSuccessfulBackup
, MAX(b.backup_finish_date) LatestSuccessfulBackup
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
GROUP BY a.name
ORDER BY a.name
To prevent this occuring, use system stored procedure sp_delete_backuphistory and pass it the date of the oldest record you want to keep.
use msdb
go
exec sp_delete_backuphistory '1/1/2006'
If a database is removed altogether, zap it's backup history like this -
exec msdb.dbo.sp_delete_database_backuphistory 'Adventureworks'
If backups are archived regularly, schedule a job to remove excess history records, like this -
-- Calculate date to be used when removing records,
-- This example deletes records over a month old.
DECLARE @dtOldest_date DATETIME
SET @dtOldest_date = dateadd(month, -1, getdate())
EXEC msdb..sp_delete_backuphistory @dtOldest_date
NB :SQL 2005+ addresses this by providing cleanup tasks in the Maintainence Plans (if you use them).

Thursday, 11 May 2006

Backup Stored Procedures

Script from a colleague.
This could be scheduled to backup code.
Particularly useful in development environments -


CREATE PROCEDURE dbo.StoredProcBackup_sp

@DatabaseName as varchar(200) = 'DATABASENAME IN HERE',
@DestinationDir as varchar(200) = 'D:\SQLDATA\MSSQL\BACKUP\StoredProcedures\AND THE REST OF YOUR PATH',
@User as varchar(20)= 'USER',
@Password as varchar(20) = 'PASSWORD'
AS

set nocount on

declare @dircreatecmd as varchar(210)
declare @Error int
declare @RootDir varchar(210)
declare @RootDay varchar(215)
declare @CheckDircmd varchar(300)

set @CheckDircmd = 'dir ' + @DestinationDir
EXEC @Error =  master..XP_CMDSHELL @CheckDircmd
If @Error = 1
 Begin
 --Directory does not exist so create it
 set @RootDir =  @DestinationDir
 set @dircreatecmd = 'mkdir ' + @RootDir
 EXEC master..XP_CMDSHELL @dircreatecmd
 End 
Declare @Day varchar(2)
Declare @Month varchar(2)
set @Day = convert(varchar(2),DATEPART(dd,getDate()))
set @Month = convert(varchar(2),DATEPART(mm,getDate()))

set @RootDay = @DestinationDir +'\'+ @Month + '\' + @Day + '\' 
declare @CheckRootDaycmd varchar(320)

set @CheckRootDaycmd = 'Dir ' + @RootDay
EXEC @Error =  master..XP_CMDSHELL @CheckRootDaycmd
If @Error = 1
 Begin
 --Directory does not exist so create it
 set @RootDir =  @RootDay
 set @dircreatecmd = 'mkdir ' + @RootDir
 EXEC master..XP_CMDSHELL @dircreatecmd
 End 

Declare @ProcName varchar(100)
Declare @ProcText varchar(8000)
Declare @filename varchar(104)
Declare @FilePath varchar(310)
DECLARE @cmd varchar (8000), @var varchar (8000)

-- backup all stored procs into single file.
EXEC master..xp_cmdshell 'osql -U?? -P?????? -Q"SELECT rtrim(ltrim(text))  FROM DATABASENAME.dbo.sysobjects so join DATABASENAME.dbo.syscomments sc on so.id = sc.id where type = ''p''" -dDATABASE -oD:\SQLDATA\MSSQL\BACKUP\StoredProcedures\REST OF YOUR PATH\AllStoredProcs_CURRENT.txt'


Declare BackupTables_cur cursor for

-- select all the stored procedures from the given database

SELECT name, text FROM DATABASENAME.dbo.sysobjects  so,  DATABASENAME.dbo.syscomments sc
where so.id = sc.id
and type = 'p'

Open BackupTables_cur

Fetch Next from BackupTables_cur into @ProcName, @ProcText
While @@Fetch_Status = 0
Begin
 Print @ProcName
 Print  @ProcText
 Print @Day
 Set @filename = @ProcName + '.txt'
 set @FilePath = @DestinationDir +'\'+ @Month + '\' + @Day + '\' + @filename

  
 DECLARE @FS int, @OLEResult int, @FileID int
 
 
 EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
 IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
 
 --Open a file
 execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FilePath, 8, 1
 IF @OLEResult <> 0 PRINT 'OpenTextFile'
 
 --Write Text1
 execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @ProcText
 IF @OLEResult <> 0 PRINT 'WriteLine'
 
 EXECUTE @OLEResult = sp_OADestroy @FileID
 EXECUTE @OLEResult = sp_OADestroy @FS

 Fetch Next from BackupTables_cur into @ProcName, @ProcText
End
Close BackupTables_cur
Deallocate BackupTables_cur

set nocount off
GO