Thursday, 16 June 2011
Backup History Performance
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
BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY' TO DISK = 'c:\Adventureworks.BAK'
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'
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?
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
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
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
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
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/909036Friday, 7 July 2006
Restore TSQL
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
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.nameTo 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_dateNB :SQL 2005+ addresses this by providing cleanup tasks in the Maintainence Plans (if you use them).
Thursday, 11 May 2006
Backup Stored Procedures
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