July 13, 2010 at 12:21 pm
Hi,
I', using the below stored procedure to backup the databases
USE [master]
GO
/****** Object: StoredProcedure [dbo].[BackupDB] Script Date: 07/12/2010 13:11:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BackupDB]
@DBName VARCHAR(100),
@BackupPath VARCHAR(1000),
@BackupType VARCHAR(4) = 'FULL'
AS
BEGIN
/*
Aim: To be able to take full, Differential and log backup of given database on given file path.
Date : 07/07/2010
Author:Mamata
*/
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
Backup the databases via a SQL Agent backup job
SQL Agent Job Step:
EXEC [dbo].[BackupDatabase] 'SharedServices1_DB','Z:\Backups\FULL\','FULL'
GO
EXEC [dbo].[BackupDatabase] 'SharedServices1_Search_DB','Z:\Backups\FULL\','FULL'
GO
And this SQLAgent job writes the output a text file. In that text file, I'm seeing the below stats and it is improper for one database and its fine for other database:
Why the Stats value is different (25, 38,51, 63..100 ) instead of 10,20,30...100???
Is this normal? Where I'm doing wrong? Please advice me...
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 1218232 pages for database 'SharedServices1_DB', file 'SharedServices1_DB' on file 1. [SQLSTATE 01000]
100 percent processed. [SQLSTATE 01000]
Processed 3 pages for database 'SharedServices1_DB', file 'SharedServices1_DB_log' on file 1. [SQLSTATE 01000]
BACKUP DATABASE successfully processed 1218235 pages in 149.754 seconds (66.641 MB/sec). [SQLSTATE 01000]
The backup set on file 1 is valid. [SQLSTATE 01000]
Full_BackupPath_of_The_Database
------------------------------------------------------------------------------------------------------------------
Z:\Backups\FULL\SharedServices1_Search_DB_07-12-2010.bak
(1 rows(s) affected)
12 percent processed. [SQLSTATE 01000]
25 percent processed. [SQLSTATE 01000]
38 percent processed. [SQLSTATE 01000]
51 percent processed. [SQLSTATE 01000]
63 percent processed. [SQLSTATE 01000]
76 percent processed. [SQLSTATE 01000]
83 percent processed. [SQLSTATE 01000]
90 percent processed. [SQLSTATE 01000]
Processed 1000 pages for database 'SharedServices1_Search_DB', file 'SharedServices1_Search_DB' on file 1. [SQLSTATE 01000]
100 percent processed. [SQLSTATE 01000]
Processed 1 pages for database 'SharedServices1_Search_DB', file 'SharedServices1_Search_DB_log' on file 1. [SQLSTATE 01000]
BACKUP DATABASE successfully processed 1001 pages in 0.259 seconds (31.660 MB/sec). [SQLSTATE 01000]
The backup set on file 1 is valid. [SQLSTATE 01000]
July 13, 2010 at 12:32 pm
these 10% stats works good with large databases. For small database with less than like 50 MB it will never show 10,20,......100. Try it for large DB with at least 500 MB or more and it will show right.
SQL DBA.
July 13, 2010 at 1:04 pm
Thank you Sanjay..
Its very helpful..Does this documented in BOL? If yes, could you please point me to that link..
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply