Script to backup all databases

Question

I'm working on automating backups of SQL Server databases on SQL 2008 R2 Express. These backups are initiated daily by the Windows Task Scheduler, which execute a batch file, which in turns executes a SQL Script. I cannot use the SQL Agent because this is the express edition.

Currently, I am writing a script which backs up each database one by one. However, this is quite high maintenance - every time a database is added, dropped, or renamed, I have to modify this script to reflect these changes. I would instead like to write one single SQL script which backs up all databases at once, without the need to explicitly backup each database one by one.

This is currently the script used to backup a single database:

Print 'Backing up MyDatabase...'
BACKUP DATABASE MyDatabase
TO DISK = 'F:\Backups\SQL Databases\MyDatabase.bak'
   WITH FORMAT,
      NAME = 'Full Backup of MyDatabase';
GO

How can I go about enumerating a list of all databases and performing a loop to do this backup to all databases?

Just some additional notes of the backup process:

• Scheduled Task in Windows executes a batch file at 9:00 PM daily
• Batch file calls OSQL which executes the SQL script
• The OSQL outputs a text file saving the results
• There are about 30 databases to be backed up
• Some of the databases are over 2GB (so it will take some time)
• A third-party backup service obtains the resulting backup files and pushes them off-site at midnight every night

Answer 1

I believe you can refer to ola.hallengren. SQL server Backup scripts.

As stated on how to use for SQL server Express:

SQL Server Express has no SQL Server Agent. Therefore, the execution of the stored procedures must be scheduled by using cmd files and Windows Scheduled Tasks. Follow these steps.

• Download MaintenanceSolution.sql.
• Execute MaintenanceSolution.sql. This script creates the stored procedures that you need.
• Create cmd files to execute the stored procedures; for example: sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup
  @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt
• In Windows Scheduled Tasks, create tasks to call the cmd files.
• Schedule the tasks.
• Start the tasks and verify that they are completing successfully.

Now you can modify this easily as per you're needs.

Note: In MaintenanceSolution, you can separately only download SQL server Backup scripts individually.

Answer 2

You can use SELECT Statement or CURSOR like this:

DECLARE @PathForBackUp VARCHAR(255)
SET @PathForBackUp = 'F:\Backup\User DB\'

SELECT 'BACKUP DATABASE [' + name + '] TO  DISK = N''' + @PathForBackUp + '' + name + '.bak''
WITH NOFORMAT, NOINIT,  NAME = N''' + name + '_FullBackUp'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 5'
FROM sys.databases
WHERE database_id > 4

OR

DECLARE @DBName VARCHAR(255)  
DECLARE @PathForBackUp VARCHAR(255) 
DECLARE @FileName VARCHAR(255)  
DECLARE @DateFile VARCHAR(255)
DECLARE @SQL NVARCHAR(2048) 
SET @PathForBackUp = 'F:\Backup\User DB\'  
SET @DateFile = REPLACE(REPLACE(CONVERT(VARCHAR(20),GETDATE(),120) ,' ','T'), ':','') 

DECLARE BACKUPING CURSOR FOR   
SELECT name  
FROM master.dbo.sysdatabases WHERE dbid > 4 

OPEN BACKUPING    
FETCH NEXT FROM BACKUPING INTO @DBName    
WHILE @@FETCH_STATUS = 0    

BEGIN    
        SET @FileName = @PathForBackUp + @DBName + '_' + @DateFile + '.BAK'  
    SET @SQL = 'BACKUP DATABASE '+@DBName+ ' TO DISK = '''+@FileName+''' WITH COMPRESSION ' 
    PRINT @SQL 
    EXECUTE sp_executesql @sql   
    FETCH NEXT FROM BACKUPING INTO @DBName  

END    

CLOSE BACKUPING    
DEALLOCATE BACKUPING 

Answer 3

Although sp_msforeachdb will be deprecated soon for Microsoft SQL Server this should work for you. Wrap this up in a stored procedure / job, make sure the account has access to write to the drive..

sp_msforeachdb @command1='
DECLARE @string NVARCHAR(500) 
SET @string = CONVERT(NVARCHAR(20),GETDATE(),120)
SET @string = REPLACE(REPLACE(REPLACE(@string,''
'',''''),'':'',''''),''-'','''')
SET @string = REPLACE(@string, '' '', '''')
SET @string = ''
BACKUP DATABASE # TO DISK = N''''F:\BACKUPS\SQL DATABASES\#_FULL_''+@string+''.bak''
SET @string = @string + ''''''
WITH FORMAT, NAME = N''''Full Backup of #''''''
IF ''#''not in(''master'', ''tempdb'',''msdb'',''model'')
begin PRINT @string  execute sp_executesql @string end' 
,@replacechar='#'

Example execution output:

BACKUP DATABASE DatabaseDuo TO DISK = N'F:\BACKUPS\SQL DATABASES\DatabaseDuo_FULL_20151019190410.bak'
WITH FORMAT, NAME = N'Full Backup of DatabaseDuo'

BACKUP DATABASE Databaaase TO DISK = N'F:\BACKUPS\SQL DATABASES\Databaaase_FULL_20151019190410.bak'
WITH FORMAT, NAME = N'Full Backup of Databaaase'

BACKUP DATABASE GarbageBase TO DISK = N'F:\BACKUPS\SQL DATABASES\GarbageBase_FULL_20151019190410.bak'
WITH FORMAT, NAME = N'Full Backup of GarbageBase'

Note that this will ignore master tempdb msdb and model databases. But will backup everything else up for you.