Script to generate T-SQL sentence to backup all DBs in SQL Server

You can use the following script to generate an output that will help you to backup all existing databases in SQL Server. For it follow the following steps.

1. Connect to your instance using SQL Server Managment Studio
2. Click in New Query
3. Select the option to have the results in a text.
4. In the new query window please paste the following script:

/*----------------------------------------------------------------

-  Author: Ruben Carrillo Siañez                                --

-  Description: Script to generate backup script for all DB's   --

-  Version for SQL Server 2005 & 2008                           --

-  Created: 12/07/2010      Modified: 01/23/2012                --

----------------------------------------------------------------*/

SET NOCOUNT ON

DECLARE @BackupPath varchar(300)

DECLARE @name sysname,

      @devicename sysname,

      @msg varchar(300),

      @newbackuppath varchar(300),

      @retstatus int,

      @checkbackuppath varchar(100),

      @length smallint

--Change the variable value with the path that you going to use

SET @BackupPath = 'C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL.1\Backup'

DECLARE dbs_cursor CURSOR FOR

SELECT  name

FROM master..sysdatabases

WHERE name not in ('tempdb')

ORDER BY name

OPEN dbs_cursor

FETCH NEXT FROM dbs_cursor INTO @name

WHILE       @@fetch_status = 0

      BEGIN

        SET @devicename = rtrim(@name) + '_dmp'

        SET @newbackuppath = @backuppath

        PRINT 'backup database ' + @name + ' to disk = N''' + @backuppath + '\' + @name + '.bak'' with init, skip

GO

Print ''Database backed up was ' + @name + '''

GO'

        FETCH NEXT FROM dbs_cursor INTO @name

      END

CLOSE dbs_cursor

DEALLOCATE  dbs_cursor

You will see an output like this one:

backup database master to disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL.1\Backup\master.bak' with init, skip

GO

Print 'Database backed up was master'

GO

backup database model to disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL.1\Backup\model.bak' with init, skip

GO

Print 'Database backed up was model'

GO

backup database msdb to disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL.1\Backup\msdb.bak' with init, skip

GO

Print 'Database backed up was msdb'

GO

backup database ReportServer to disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL.1\Backup\ReportServer.bak' with init, skip

GO

Print 'Database backed up was ReportServer'

GO

backup database ReportServerTempDB to disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL.1\Backup\ReportServerTempDB.bak' with init, skip

GO

Print 'Database backed up was ReportServerTempDB'

GO

To use it you just need to copy the output and paste it in a new query window.

You can run it directly using sp_executesql but the idea is you can prevent any failure reviewing the code first.