Article to manage SQL Server 2000 transaction log growth http://www.mssqltips.com/tip.asp?tip=950
Advertisements
Managing SQL Server 2000 Transaction Log Growth
Reply
Category Archives: Backup
DBCC CHECKDB
How to see backup history through the back-end?
By querying msdb.dbo.backupset and msdb.obackupmediafamily one can see backup history like start and end time, type of backup, location of the backup.
select bs.database_name
, bs.type
, bs.backup_start_date
, bs.backup_finish_date
, bmf.physical_device_name
FROM msdb.dbo.backupset bs
, msdb.dbo.backupmediafamily bmf
where bmf.media_set_id = bs.media_set_id
and backup_start_date > '2010-01-22'
testDB D 2010-01-22 00:00:02.000 2010-01-22 05:09:32.000 c:\Backup\testdb_full.bak
testDB I 2010-01-23 02:26:21.000 2010-01-23 02:30:28.000 C:\Backup\testdbdiff20100123_022621.bak
Type – D = Database, I – Differential Database, L – Log
Documentation on backupset:
SQLServer 2008 – http://technet.microsoft.com/en-us/library/ms186299.aspx
SQLServer 2005 – http://technet.microsoft.com/en-us/library/ms186299%28SQL.90%29.aspx
SQLServer 2000 – http://technet.microsoft.com/en-us/library/aa260602%28SQL.80%29.aspx
Documentation on backupmediafamily:
SQLServer 2008 http://technet.microsoft.com/en-us/library/ms190284.aspx
SQLServer 2005 – http://technet.microsoft.com/en-us/library/ms190284%28SQL.90%29.aspx
SQLServer 2000 – http://technet.microsoft.com/en-us/library/aa260605%28SQL.80%29.aspx
How to create differential backup?
Using the following T-SQL one can create differential backup or make it part of scheduled job.
declare @datetimestamp varchar(20)
declare @filename nvarchar(100)
declare @name nvarchar(100)
SELECT @datetimestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), '-', ''), ' ', '_'), ':', '')
SELECT @filename = 'C:\testDBBackup\testDBbdiff' + @datetimestamp + '.bak'
SELECT @name = 'testDB Diff Backup ' + @datetimestamp
BACKUP DATABASE testDB
TO DISK = @filename
WITH DIFFERENTIAL,INIT, NAME = @name
