All Database Backup – SQL Express

A user who is using SQL Server express edition, came to me and ask how to take All Database Backup at a time. Here is a simple script to take all database backup at a specified location.

DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) ––– path for backup files
DECLARE @fileName VARCHAR(256) –– filename for backup
DECLARE @fileDate VARCHAR(20) –– used for file name
SET @path = ‘D:\DBBACKUP\’
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN (‘master’,‘model’,‘msdb’,‘tempdb’)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name  TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Know Your Backup Status

Hi Guys, As a DBA its happend so many times, You started your Database Backup from any remote PC/Server or it may be your Maintenance Plan for Backup Database(s) and you want to know your backup progress status.

Its possible using system SP / DMV, The Query for same will be as

SELECT A.NAME ‘DATABASE NAME’,B.TOTAL_ELAPSED_TIME/60000 AS ‘RUNNING SINCE (Minutes)’,
B.ESTIMATED_COMPLETION_TIME/60000 AS ‘REMAINING TIME(Minutes)’,B.PERCENT_COMPLETE as ‘BACKUP % COMPLETED’ ,B.COMMAND ‘COMMAND TYPE’,(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS
‘COMMAND TEXT’, UPPER(B.STATUS) STATUS,B.SESSION_ID ‘SPID’, B.START_TIME, B.BLOCKING_SESSION_ID, B.LAST_WAIT_TYPE, B.READS, B.LOGICAL_READS, B.WRITES from
MASTER..SYSDATABASES A , sys.dm_exec_requests B
WHERE A.DBID=B.DATABASE_ID AND B.COMMAND LIKE ‘%BACKUP%’
order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc

Output result will be as below