On today’s menu – T-SQL script for a full backup

Right – we have Symantec doing all our SQL server backups but it has come to our attention:

that when the Symantec backup job runs it kills (nearly) all our connected clients.
They are getting stuck, systems are extremely slow. Non responsive.

Running a factory 24/7 requiring real time data, reports and statistics from several SQL hunger software and web applications, the net effect is.

Somebody is gonna get fired

A few Google searches and we have T-SQL script that does the job in less than 10 minutes.
(Needless to say Symantec took about 2 hours).

You all are waiting for it…

Here is the T-SQL script:

DECLARE @db_name VARCHAR(50) -- variable to hold database name 
DECLARE @backup_path VARCHAR(256) -- output path for the backups; remember your trailing path delimiter: "\"
DECLARE @file_name VARCHAR(256) -- filename for backup 
DECLARE @file_date VARCHAR(20) -- used for file name

SET @backup_path = 'D:\Backups Full A\' 
SELECT @file_date = CONVERT(VARCHAR(20),GETDATE(),112) -- appending date time to filename

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb'); -- exclude the rdms related d

OPEN db_cursor; 
FETCH NEXT FROM db_cursor INTO @db_name;

WHILE @@FETCH_STATUS = 0 
BEGIN 
 SET @file_name = @backup_path + @db_name + '_' + @file_date + '.BAK'; 
 BACKUP DATABASE @db_name TO DISK = @file_name; 
 FETCH NEXT FROM db_cursor INTO @db_name; 
END
CLOSE db_cursor 
DEALLOCATE db_cursor'

Suggestions to Consider:

• Create a Stored Procedure with “backup_path” as an parameter
• Run the Stored Procedure from a SQL Job and assign parameter for output.

This way you can, for example, have 2 SQL Jobs running:

Job A = “backup_path” = C:\Backup Full A
Job B = “backup_path” = C:\Backup Full B

No harm when you have redundancy in terms of full backups.
Never know when one might get lost, corrupted.

There is still much more to a good backup strategy than this “memblog” descibes.
But this was a quick solution.

Incremental Backups, Differential Backups etc.

You go read about those and come share with me 🙂

Cheers!