Link to home
Create AccountLog in
Avatar of richa1960
richa1960Flag for United States of America

asked on

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
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

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

Avatar of richa1960

ASKER

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
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

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
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.
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 ''.
ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Works like a charm, thanks.