SQL Server - BackUp All Databases

Sometimes I need to backup all the dbs to one physical location. For example, moving  databases to another server.

The script is 2005\2008\2012 compatible .

You might want to consider adding the COPY keyword if you are using the script in 2005.  Read more on Copy only backup in SQL server 2005 and not break the backup chain  , dealing with the problem of adhoc backups breaking backup chains

You can alter the script to include system databases

For added flexibility create a  Backup database job with SQL Server Agent

--------------------------CODE START----------------------------------

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 = '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  WITH COPY_ONLY

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor
--------------------------CODE END-----------------------------------------------------

 Related Posts

Differential backup cannot be restored . BACKUP with COPY_ONLY

BACKUP failed to complete the command BACKUP LOG