June 13, 2011 at 12:42 pm
Hi,
We are using SQL Server 2005 EE with SP3. We are backing up (Full backup) 200 GB SharePoint Content database using the native SQL backup.
I'm seeing difference in time duration for the backup job completion timings in backup job "View history" and the backup log as below:
In view history, it says 01:43:53 (1 hr 43 mins) and in the backup log, it says 3050.662 seconds (53 mins).
Could you please clarify me which one is correct?
View history:
Date6/12/2011 10:00:00 PM
LogJob History (FULL_BACKUP)
Step ID0
ServerSQL1
Job NameFULL_BACKUP
Step Name(Job outcome)
Duration01:43:53
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
The job succeeded. The Job was invoked by Schedule 23 (Daily). The last step to run was step 1 (Full_Backup).
From the backup log:
Backup log:
Job 'FULL_BACKUP' : Step 1, 'Full_Backup' : Began Executing 2011-06-12 22:00:00
Full_BackupPath_of_The_Database
Z:\Backups\FULL\WSS_Content_PROD_06-12-2011.bak
---------------------------------------------------------
(1 rows(s) affected)
10 percent processed. [SQLSTATE 01000]
20 percent processed. [SQLSTATE 01000]
30 percent processed. [SQLSTATE 01000]
40 percent processed. [SQLSTATE 01000]
50 percent processed. [SQLSTATE 01000]
60 percent processed. [SQLSTATE 01000]
70 percent processed. [SQLSTATE 01000]
80 percent processed. [SQLSTATE 01000]
90 percent processed. [SQLSTATE 01000]
Processed 12905800 pages for database 'WSS_Content_PROD', file 'WSS_Content_PROD' on file 1. [SQLSTATE 01000]
Processed 12899632 pages for database 'WSS_Content_PROD', file 'WSS_Content_PROD_1' on file 1. [SQLSTATE 01000]
100 percent processed. [SQLSTATE 01000]
Processed 1445 pages for database 'WSS_Content_PROD', file 'WSS_Content_PROD_log' on file 1. [SQLSTATE 01000]
BACKUP DATABASE successfully processed 25806877 pages in [highlight=#ffff11]3050.662 seconds[/highlight] (69.299 MB/sec). [SQLSTATE 01000]
The backup set on file 1 is valid. [SQLSTATE 01000]
Thanks
June 13, 2011 at 12:46 pm
Are there other things in that job step? A verify perhaps? CheckDB? Another backup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2011 at 3:52 pm
No. I did check again and there is only one database backing in that job step. Nothing else.
Job Step:
EXEC [dbo].[BackupDB] 'WSS_Content_PROD','Z:\Backups\FULL\','FULL'
GO
Please advice...
June 13, 2011 at 3:58 pm
And what does dbo.BackupDB do?
From the backup output it's clear there's more than simply a backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2011 at 5:54 pm
dbo.BackupDB is stored procedure used to backup a database and there is NO other step inthe backup job.
Just backing up the 200 GB content database.
June 13, 2011 at 6:11 pm
Can you post the code?? There's clearly at least a verify on top of the backup, maybe there's something else we don't see.
June 13, 2011 at 11:50 pm
Ninja's_RGR'us (6/13/2011)
There's clearly at least a verify on top of the backup, maybe there's something else we don't see.
Exactly. There's a verify at least (clear from the output you posted) and that will be counted in the time for the step, not the time for the backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2011 at 9:47 am
Yes, Verify backup is there in the backup script.
Here is my Backup Stored procedure:
Questions:
1. Is my Backup procedure good enough to take a backup of a Production database?
2. Please advice if we can improve this procedure?
USE [master]
GO
/****** Object: StoredProcedure [dbo].[BackupDB] Script Date: 06/14/2011 08:37:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BackupDB]
@DBName VARCHAR(100),
@BackupPath VARCHAR(2000),
@BackupType VARCHAR(4) = 'FULL'
AS
BEGIN
DECLARE @BackupName VARCHAR(255)
DECLARE @Prefix VARCHAR(50)
DECLARE @backupSetId AS INT
-- Make sure database exists on server
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysdatabases WHERE name = @DBName)
BEGIN
SELECT 'Database name does not exist'
RETURN(1)
END
-- Make sure one one is trying to take tempdb backup
IF @DBName = 'tempdb'
BEGIN
SELECT 'tempdb cannot be backedup'
RETURN(1)
END
-- full, Differential and log backups are allowed, other wise do not do any thing.
IF @BackupType NOT IN ('FULL', 'LOG', 'DIFF')
BEGIN
PRINT 'Invalid type of Backup selected, only FULL and LOG backup is allowed'
RETURN(1)
END
-- If user did not give the back slash, add one.
IF RIGHT(@BackupPath,1) <> '\'
SET @BackupPath = @BackupPath + '\'
IF @BackupType = 'FULL'
BEGIN
SET @BackupName = @DBName + ' Full Backup'
SET @BackupPath = @BackupPath + @DBName +'_'+ CONVERT(VARCHAR(10),GETDATE(),110)+'.bak'
SELECT @BackupPath AS Full_BackupPath_of_The_Database
BACKUP DATABASE @DBName
TO DISK = @BackupPath
WITH NOFORMAT, NOINIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName
AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset
WHERE database_name = @DBName )
IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed for database', 16, 1)
END
RESTORE VERIFYONLY
FROM DISK = @BackupPath
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
END
IF @BackupType = 'DIFF'
BEGIN
SET @BackupName = @DBName + ' Differential Backup'
SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE(),120)
SELECT @Prefix = REPLACE(@Prefix, '-', '')
SELECT @Prefix = REPLACE(@Prefix, ':', '')
SELECT @Prefix = REPLACE(@Prefix, ' ', '')
SET @BackupPath = @BackupPath + @DBName + '_' + @Prefix +'.bak'
SELECT @BackupPath AS Diff_Backup_Path
BACKUP DATABASE @DBName
TO DISK = @BackupPath
WITH DIFFERENTIAL ,NOFORMAT, INIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName
AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset
WHERE database_name = @DBName )
IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed for database', 16, 1)
END
RESTORE VERIFYONLY
FROM DISK = @BackupPath
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
END
IF @BackupType = 'LOG'
BEGIN
SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE(),120)
SELECT @Prefix = REPLACE(@Prefix, '-', '')
SELECT @Prefix = REPLACE(@Prefix, ':', '')
SELECT @Prefix = REPLACE(@Prefix, ' ', '')
SET @BackupName = @DBName + ' Log Backup'
SET @BackupPath = @BackupPath + @DBName + '_Log' + @Prefix + '.trn'
SELECT @BackupPath AS Log_Backup_Path
BACKUP log @DBName
TO DISK = @BackupPath
WITH NOFORMAT, NOINIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName
AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset
WHERE database_name = @DBName )
IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed for database', 16, 1)
END
RESTORE VERIFYONLY
FROM DISK = @BackupPath
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
END
END
Thanks
June 15, 2011 at 7:35 am
gmamata7 (6/13/2011)
In view history, it says 01:43:53 (1 hr 43 mins) and in the backup log, it says 3050.662 seconds (53 mins).Could you please clarify me which one is correct?
Maybe I'm reading this wrong, but it looks to me that you're comparing apples to oranges. The View History, if I understand what you're saying, is the length of time that it takes the job to complete. The Backup log is the amount of time it takes the backup itself to complete.
Please note that in your code you are declaring and setting variables, plus doing a whole bunch of other things. It's that "other things" that appears to be the difference in the times you are seeing.
June 17, 2011 at 10:13 am
In my backup procedure, Restore verifyonly taking much time once the backup is done.
RESTORE VERIFYONLY FROM DISK = @BackupPath
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
Question: Is my backup procedure good enough to take backup of Production database?
Thanks
June 17, 2011 at 10:30 am
gmamata7 (6/17/2011)
Question: Is my backup procedure good enough to take backup of Production database?
Yes and no.
Yes, because it's taking your backups.
No, because to truly verify your backups, it's best to verify them by actually restoring them (not just checking the header).
I have heard rumors that Restore Verify Only might say everything's fine, but when a person tries to a restore with that same backup, it has failed due to misc. reasons. I don't know how many of these stories are true, but I always prefer to test my backups with daily production copies down to our QA environment. It serves 2 purposes. I get my verification and the our developers can troubleshoot production bugs against the restored db. Win / Win.
June 17, 2011 at 11:17 am
Brandie Tarvin (6/17/2011)
I have heard rumors that Restore Verify Only might say everything's fine, but when a person tries to a restore with that same backup, it has failed due to misc. reasons.
Yup. These backups aren't been taken with checksum, so all restore verifyonly is doing is verifying that the backup header is intact. The backup itse4lf can be absolutely destroyed, the file overwritten with 0 for half it's length. It'll verify, it won't restore.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2011 at 2:37 pm
Yup. These backups aren't been taken with checksum, so all restore verifyonly is doing is verifying that the backup header is intact. The backup itse4lf can be absolutely destroyed, the file overwritten with 0 for half it's length. It'll verify, it won't restore
In this case, I need to copy 200 GB backup file to another server and restore every day to make sure the backup file can be restored? Is that practical to copy & restore the .bak file everyday on another server?
June 17, 2011 at 2:41 pm
Which would you prefer?
1) Finding out the backup is damaged and unrestorable right after making it, when you can make another
2) Finding out the backup is damaged after the server crashes when the database is unrecoverable and all the big managers are standing around demanding to know when things are going to be fixed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2011 at 5:04 am
GilaMonster (6/17/2011)
Which would you prefer?1) Finding out the backup is damaged and unrestorable right after making it, when you can make another
2) Finding out the backup is damaged after the server crashes when the database is unrecoverable and all the big managers are standing around demanding to know when things are going to be fixed.
I know which one I go for. @=)
EDIT: You don't have to copy to another server. You can restore as another DB name to your current server & current instance if you want to. I just don't recommend it because it takes CPU cycles away from other production processing.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply