How to schedule and automate backups of all SQL Server databases in SQL Server.

Who is online?  0 guests and 1 members
Home  »  Blogs  »  Anil  »  How to schedule and automate backups of all SQL Server databases in SQL Server.
 
0
/5
Avg: 0/5: (0 votes)

Comments (2)

Anil
Anil said:

Also you can set the Job Agent to schedule the backup all database by simly using the T-sql script as :

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 
--specific location
SET @path 'C:\Backup\'  

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

Regards,

Anil Maharjan

3/1/2011
 · 
 
by
radu_bak
radu_bak said:
There is also an option of creating a maintenance plan which is more flexible, in the sense that you can add multiple tasks to run one after the other. A classic example would be doing a backup of your databases and then doing a cleanup operation on those backups that are very old, and which you don't need anymore. You can find this article here: http://sqlbak.com/blog/scheduling-backups/
3/23/2014
 · 
 
by
Blogs RSS Feed

Anil's latest blog posts

Blogs RSS Feed

Latest community blog posts