Saturday, 23 August 2014

How to Backup All Databases in SQL Server

Hi friends,in this article I will explain about How to Backup All Databases in SQL Server.
Below  is a SQL script to get all Databases Backup in specified folder.
Backup file name will be like "DataBaseName_{Date}-{Month}-{Year}.bak"

DECLARE @DBName VARCHAR(MAX) 
DECLARE @DBBackupFileName VARCHAR(MAX) 
DECLARE @FileDate VARCHAR(20) 
DECLARE @Path VARCHAR(MAX) 
SET @Path='D:\DBBackup\' 
SET @FileDate=CONVERT(VARCHAR(20),GETDATE(),105) 
DECLARE dbCursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','tempdb','model','msdb') 
OPEN dbCursor 
FETCH NEXT FROM dbCursor into @DBName 
WHILE @@FETCH_STATUS =
BEGIN 
SET @DBBackupFileName= @Path + @DBName + '_' + @FileDate + '.bak' 
BACKUP DATABASE @DBName TO DISK =@DBBackupFileName 
FETCH NEXT FROM dbCursor into @DBName 
END 
CLOSE dbCursor 
DEALLOCATE dbCursor


I have 3 databases.So,the output of the above code as shown in the below figure.


No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.