SQL Server Native Backup with 2000 Databases

Question

SQL Server Native Backup with 2000 Databases

Reo

Hall of Fame

Points: 3774
More actions
September 9, 2010 at 2:01 am

#229086

Hi All,
I have around 2000 databases on an Instance of SQL Server 2008 Web Edition 64bit. All databases running under Simple recovery Model.
I am using SQL Server Native Backup to take the entire database's backup on local disk. I do take Full backup every day at 00 hrs; o archive it I have a written T-SQL in SQL Agent Job like below:
--------------------------------------------------------------------
DECLARE @name VARCHAR(256) -- database name
DECLARE @DomainName VARCHAR(256) -- database name
DECLARE @path VARCHAR(300) -- path for backup files
DECLARE @fileName VARCHAR(300) -- filename for backup
DECLARE @fileDate VARCHAR(450) -- used for file name
DECLARE @cmd VARCHAR(500) -- used for DOS CMD
DECLARE db_cursor CURSOR FOR
SELECT [name],dbo.fnReturnDomainName(dbid)As DomainName FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('Tempdb')
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND DATABASEPROPERTY(name, 'IsInload') = 0
AND DATABASEPROPERTY(name, 'IsInRecovery') = 0
AND DATABASEPROPERTY(name, 'IsInStandBy') = 0
AND DATABASEPROPERTY(name, 'IsNotRecovered') = 0
AND DATABASEPROPERTY(name, 'IsEmergencyMode') = 0
ORDER BY 2
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name,@DomainName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @path = 'D:\SQLDatabaseBackup\' + Host_Name() + '_'
+ CONVERT(VARCHAR(20),GETDATE(),112) + '\' + @DomainName + '\'
SET @cmd= 'MD ' + @Path
EXEC master.dbo.xp_cmdshell @cmd ,no_output -- Create Direcotry
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
SET @fileName = @path + @fileDate + '_' + Upper(@name) + '.FUL'
BACKUP DATABASE @name TO DISK = @fileName
--WITH BUFFERCOUNT = 7, MAXTRANSFERSIZE = 65536
FETCH NEXT FROM db_cursor INTO @name,@DomainName
END
CLOSE db_cursor
DEALLOCATE db_cursor
-----------------------------------------------------------------
Funcation fnReturnDomainName returns the Folder Name in which .mdf & .ldf files exists.
But problem is that sometime this script takes Full Backup of all database but some time it not. and also it completed within 7 Minutes without any error. but when I check last database backup time it indicate that this job should take 1 hrs to coplete.
------------------------------------------------------------------
Should I take backup using Database Maintenance Plan and what’s wrong with my script? I have observed that web edition is not offer to perform online Backup/Restore!
With Respectfully,
Ram
MSSQL DBA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Reo (9/9/2010)
But problem is that sometime this script takes Full Backup of all database but some time it not. and also it completed within 7 Minutes without any error. but when I check last database backup time it indicate that this job should take 1 hrs to coplete.

Try running it manually and printing out the commands instead of running them and then check that everything is as it should be.

I have observed that web edition is not offer to perform online Backup/Restore!

All editions of SQL allow online backups, even Express. In fact, there's no offline backups in SQL.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Reo Hall of Fame Points: 3774 More actions · Answer 2

Ok!

What I understand that when SQL Agent Job Executes the T-SQL then the cursor issues the cmds "BACKUP DATABASE <dbNam> ...” for all databases and cursor loop do not wait for complete the first database backup and then move to next and so on. That’s why it completes within 7 min and without any error.

Ram
MSSQL DBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

T-SQL runs synchronously. If you issue a BACKUP DATABASE, the code waits until the backup has finished, then goes on. The backups don't run in parallel.

Run the code in management studio and see what messages get returned.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Reo Hall of Fame Points: 3774 More actions · Answer 4

In SSMS the same T-SQL taking around 1 Hour to complete the all 2000 databases backup and once first database backup completed it disply the result BACKUP DATABASE successfully processed 225 pages in 0.104 seconds (16.902 MB/sec). .... and then move to next and so on and also waiting for first backup completed and tehn moves to next.

In SSMS it is working as you are saying it taking his full time to take the backup. but what heppens when it run by SQL Server Agent. the Job completed just 5-7 minutes. I am attaching Job History

Thanks your nice and expertise reply:-)

Ram
MSSQL DBA