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

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
richa1960Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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

richa1960Author 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
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
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
richa1960Author 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.
richa1960Author 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 ''.
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
richa1960Author Commented:
Works like a charm, thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.