Category: Microsoft Sql Server

Own Blog

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  
 
--Provide the path where all the databases needs to be backed up 
SET @path = 'MyBackupFilePath'   
 
--used to suffix the current date at the end of backup filename 
SELECT @fileDate = REPLACE(CONVERT(VARCHAR(20),GETDATE(),105),'-','')

DECLARE db_cursor CURSOR FOR   
 
--Use this for all database except the system databases and any exclusion you can make 
SELECT name  
FROM master.dbo.sysdatabases  
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')   
 
--Uncomment and use this for only specific databases. 
--Those database names you can provide under IN clause 
--SELECT name  
--FROM master.dbo.sysdatabases  
--WHERE name IN ('MyDB1','MyDB2')  
 
 
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 WITH STATS = 1   
 
       FETCH NEXT FROM db_cursor INTO @name    
END    
 
CLOSE db_cursor    
DEALLOCATE db_cursor

Sql server backup Script for All Database ddmmyyyy without compression

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

–Provide the path where all the databases needs to be backed up
SET @path = ‘c:\bck\’

–used to suffix the current date at the end of backup filename
SELECT @fileDate = REPLACE(CONVERT(VARCHAR(20),GETDATE(),105),’-‘,”)

DECLARE db_cursor CURSOR FOR

–Use this for all database except the system databases and any exclusion you can make
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’ReportServer’,’ReportServerTempDB’)

–Uncomment and use this for only specific databases.
–Those database names you can provide under IN clause
–SELECT name
–FROM master.dbo.sysdatabases
–WHERE name IN (‘MyDB1′,’MyDB2’)

 

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 WITH STATS = 1

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor