A daily full backup that creates multiple files that are seperated for each day

richa1960
richa1960 used Ask the Experts™
on
I'd like to take a current multi file sql backup and have the process run and create the 3 files but not in an appended fashion.
but with each days run being separated, or shown. the code I'm using is below but it only appends the backup into each files
not separated by date.

BACKUP DATABASE [IFS_FSM] TO  DISK = N'C:\SQL_backup_tst\IFS_FSM_FILE1.BAK',
  DISK = N'C:\SQL_backup_tst\IFS_FSM_FILE2.BAK',
   DISK = N'C:\SQL_backup_tst\IFS_FSM_FILE3.BAK'
   WITH NOFORMAT, NOINIT,  NAME = N'IFS_FSM-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
you can use this MS code -example

T-SQL script to backup all user databases with time stamped backup file.
http://blogs.msdn.com/b/ai/archive/2012/05/29/t-sql-script-to-backup-all-user-databases-with-time-stamped-backup-file.aspx

use master;
go
 
 
DECLARE UserDatabases_CTE_Cursor Cursor
FOR
 
-- Selecting user database names.
select name as DatabaseName
from sys.sysdatabases
where ([dbid] > 4) and ([name] not like '$')
 
OPEN UserDatabases_CTE_Cursor
DECLARE @dbName varchar(100);
DECLARE @backupPath varchar(100);
DECLARE @backupQuery varchar(500);
 
-- make sure that the below path exists
set @backupPath = 'c:\SQLBackupFolder\'
 
Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName
While (@@FETCH_STATUS <> -1)
 
BEGIN
-- Backup SQL statement
set @backupQuery =  'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + '_[' + REPLACE( convert(varchar, getdate(), 109), ':', '-') + '].bak'''
 
-- Print SQL statement
print @backupQuery
 
-- Execute backup script
EXEC (@backupQuery)
 
-- Get next database
Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName
END
 
CLOSE UserDatabases_CTE_Cursor
DEALLOCATE UserDatabases_CTE_Cursor
GO
 

Open in new window

Author

Commented:
I'm only dealing with a couple vary large databases that I want to backup but to speed up the process create split backup files, so instead of one large backup file of 100+gb it breaks it down into 25GB 4 file set. there is a way to do it with the code I'd sent but that will append for each run and not separate out each days run for the 4 files

Commented:
or try just for your DB  (use INIT to overwrite)
code
use master;
go
 

DECLARE @dbName varchar(100)='IFS_FSM'
DECLARE @backupPath varchar(100);
DECLARE @backupQuery varchar(500);
DECLARE @DT varchar(50)

set @DT =   REPLACE(REPLACE( convert(varchar, getdate(), 109), ':', '-') ,' ','_')
-- make sure that the below path exists
set @backupPath = 'C:\SQL_backup_tst\'
 

-- Backup SQL statement
set @backupQuery =  'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + '_FILE1_'+@DT+'.bak,   disk = ''' + @backupPath + @dbName + '_'+@DT+'.bak,
 disk = ''' + @backupPath + @dbName + '_FILE2_'+@DT+'.bak, disk = ''' + @backupPath + @dbName + '_FILE3_'+@DT+'.bak
   WITH NOFORMAT, INIT,  NAME = N''IFS_FSM-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'''
 
-- Print SQL statement
print @backupQuery
 
-- Execute backup script
EXEC (@backupQuery)
 

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mohammed KhawajaManager - Infrastructure:  Information Technology

Commented:
I believe the following script should do it for you:

USE master
GO

EXEC sp_addumpdevice 'disk', 'File1', 'C:\SQL_backup_tst\IFS_FSM_FILE1.BAK'
EXEC sp_addumpdevice 'disk', 'File2', 'C:\SQL_backup_tst\IFS_FSM_FILE2.BAK'
EXEC sp_addumpdevice 'disk', 'File3', 'C:\SQL_backup_tst\IFS_FSM_FILE3.BAK'

BACKUP DATABASE [IFS_FSM]
TO File1, File2, File3
WITH NOINIT

Author

Commented:
with the 'EXEC sp_adddumpdevice ' I end up with a file already exists error when it runs again, so I wouldn't be able to maintain multiple days of the file grouping.

Author

Commented:
EugeneZ I got the following error, so I'm picking through it to see where the issue is.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.
Msg 132, Level 15, State 1, Line 2
The label 'C' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 105, Level 15, State 1, Line 3
Unclosed quotation mark after the character string ''.
Commented:
please try this one

use master;
go
 

DECLARE @dbName varchar(100)='dw'
DECLARE @backupPath varchar(100);
DECLARE @backupQuery varchar(500);
DECLARE @DT varchar(50)

set @DT =   REPLACE(REPLACE( convert(varchar, getdate(), 109), ':', '-') ,' ','_')
-- make sure that the below path exists
set @backupPath = 'C:\SQL_backup_tst\'
 

-- Backup SQL statement
set @backupQuery =  'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + '_FILE1_'+@DT+'.bak'',
 disk = ''' + @backupPath + @dbName + '_FILE2_'+@DT+'.bak'', disk = ''' + @backupPath + @dbName + '_FILE3_'+@DT+'.bak''
   WITH NOFORMAT, INIT,  NAME = N''IFS_FSM-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
 
-- Print SQL statement
print @backupQuery
 
-- Execute backup script
EXEC (@backupQuery)
 
 

Open in new window

Author

Commented:
Works like a charm, thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial