Dynamin SQL script to return backup statements for all the databases on my production server

Take a look at this tip: http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

Thank you for your post Ray. however the query that i'm trying out should return the actual Backup statement that i will then execute for e.g the resultset should be something like: Backup 'db_name' from disk = 'backup path' for all the the user databases.

thanks,

Pinkett

You can take the script in the tip: http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/ and change it; e.g. create a temp table, insert each backup command into the temp table, then select everything from the temp table after the cursor is done.

Pinkett,

I assume you are looking for a command like this:

BACKUP DATABASE [Test1] TO DISK = 'C:\Temp\Backup\Test1_20120209.BAK' WITH INIT

Here is the code I used to generate it:

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
DECLARE @CMD1 varchar(2000) -- Final string

SET @path = 'C:\Temp\Backup\' 
SET @CMD1 = ''

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' 
       SET @CMD1 = 'BACKUP DATABASE [' + @name + '] TO DISK = ' + char(39) + @fileName + char(39) + ' WITH INIT'
       SELECT @CMD1

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

The original code is from this tip - http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/.

Let me know if this is what you are looking for.

Thank you,
Jeremy Kadlec

If the goal is to produce a resultset with all the backup commands, rather than execute the backup commands one at a time, there's no need for the cursor. 

SELECT  REPLACE(REPLACE(REPLACE(
            'BACKUP DATABASE [<n>] TO DISK = ''<p><n>_<t>.BAK'' WITH INIT ;',
            '<t>', DateStamp), '<p>', BackupPath), '<n>', name)
FROM (
    SELECT name FROM sys.databases
    WHERE state_desc = 'ONLINE' AND database_id > 4 ) db
CROSS JOIN (
    SELECT  BackupPath = 'C:\Temp\Backup\',
            DateStamp = CONVERT(CHAR(8), GETDATE(), 112)
                      + REPLACE(CONVERT(CHAR(8), GETDATE(), 8), ':', '') ) c

 I can't resist the temptation to accessorize this:

WITH misc AS (
    SELECT  BackupPath = '\\Fileserv\BackupShare\' + @@SERVERNAME,
            DateStamp = CONVERT(CHAR(8), GETDATE(), 112)
                      + REPLACE(CONVERT(CHAR(8), GETDATE(), 8), ':', ''),
            OldestToKeep = CONVERT(CHAR(10), DATEADD(wk, -2, GETDATE()), 120) )
SELECT  REPLACE(REPLACE('EXEC master.dbo.xp_create_subdir N''<p>'' ;
EXEC master.dbo.xp_delete_file 0,N''<p>'',N''BAK'',N''<o>'',1 ;',
        '<o>', OldestToKeep), '<p>', BackupPath)
FROM misc
UNION ALL
SELECT REPLACE(REPLACE(REPLACE('EXEC master.dbo.xp_create_subdir ''<p>\<n>'' ;
BACKUP DATABASE [<n>] TO DISK = ''<p>\<n>\<n>_<t>.BAK'' WITH INIT, CHECKSUM ;
RESTORE VERIFYONLY FROM DISK = ''<p>\<n>\<n>_<t>.BAK'' ;',
            '<t>', DateStamp), '<p>', BackupPath), '<n>', name)
FROM misc
CROSS JOIN (
    SELECT name FROM sys.databases
    WHERE  database_id > 4 AND state_desc = 'ONLINE') db