March 15, 2013 at 1:36 pm
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
-- specify database backup directory
SET @path = 'C:\Backup\'
-- specify filename format
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') -- exclude these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
March 15, 2013 at 3:03 pm
something like this...
DECLARE @sql VARCHAR(8000);
--Set your threshold here
DECLARE @big int=1000--mb
;WITH dbs([dbid],size) AS
(SELECT database_id,
SUM(size)/128
FROM sys.master_files
GROUP BY database_id )
SELECT @sql = COALESCE(@sql,'')+
CASE
WHEN size < @big
THEN 'BACKUP DATABASE '+d2.name+' TO DISK = ''C:\Backup\'+CONVERT(VARCHAR(20),GETDATE(),112)+ '.BAK'''+CHAR(13)
ELSE 'PRINT ''REPLACE Me with your code to backup db to multiple files'''+CHAR(13)
END
FROM dbs d1
JOIN sys.databases d2 ON d1.dbid=d2.database_id
WHERE d2.name NOT IN ('master','model','msdb','tempdb')
EXEC(@sql)
-- Itzik Ben-Gan 2001
March 16, 2013 at 7:28 am
why do you feel the need to backup to multiple files, do you have multiple backup devices?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 19, 2013 at 7:55 am
Alan,
Sorry for the late response. I tested the script and it works great. But I want to split the backup files to multiple files if the database size is > 50 GB. For example like this:
tes_20130319_1.bak
tes_20130319_2.bak
tes_20130319_3.bak
tes_20130319_4.bak
tes_20130319_5.bak
currently the script backsup the database(s) to single .bak file.
Thanks
March 19, 2013 at 7:58 am
Perry,
Currently we don’t have multiple devices or any third party tools to backup the large databases. I read in article if we strip the backup files to multiple .bak files, we can cut down the backup duration. Currently our backups are taking longer and creating only single .bak file for large database >200 GB
Thanks
March 19, 2013 at 11:34 am
pehlebhiayatha (3/19/2013)
Alan,Sorry for the late response. I tested the script and it works great. But I want to split the backup files to multiple files if the database size is > 50 GB. For example like this:
tes_20130319_1.bak
tes_20130319_2.bak
tes_20130319_3.bak
tes_20130319_4.bak
tes_20130319_5.bak
currently the script backsup the database(s) to single .bak file.
Thanks
That would be something like this:
DECLARE @sql VARCHAR(8000);
--Set your threshold here
DECLARE @big int=50000--mb
;WITH dbs([dbid],size) AS
(SELECT database_id,
SUM(size)/128
FROM sys.master_files
GROUP BY database_id )
SELECT @sql = COALESCE(@sql,'')+
CASE
WHEN size < @big
THEN 'BACKUP DATABASE '+d2.name+' TO DISK = ''C:\Backup\'+CONVERT(VARCHAR(20),GETDATE(),112)+ '.bak'''+CHAR(13)+CHAR(13)
ELSE
'BACKUP DATABASE '+d2.name+' '+CHAR(13)+
'TO DISK = ''C:\Backup\'+CONVERT(VARCHAR(20),GETDATE(),112)+ '1.bak'','+CHAR(13)+
'TO DISK = ''C:\Backup\'+CONVERT(VARCHAR(20),GETDATE(),112)+ '2.bak'','+CHAR(13)+
'TO DISK = ''C:\Backup\'+CONVERT(VARCHAR(20),GETDATE(),112)+ '3.bak'','+CHAR(13)+
'TO DISK = ''C:\Backup\'+CONVERT(VARCHAR(20),GETDATE(),112)+ '4.bak'','+CHAR(13)+
'TO DISK = ''C:\Backup\'+CONVERT(VARCHAR(20),GETDATE(),112)+ '5.bak'''+CHAR(13)+CHAR(13)
END
FROM dbs d1
JOIN sys.databases d2 ON d1.dbid=d2.database_id
WHERE d2.name NOT IN ('master','model','msdb','tempdb')
PRINT(@sql)
-- Itzik Ben-Gan 2001
March 19, 2013 at 12:03 pm
Thanks Alan. Getting lose. When I ran the above script, it displays this TSQL in output but doesn't backup these database.
BACKUP DATABASE Test1
TO DISK = 'C:\temp\20130319_1.bak',
TO DISK = 'C:\temp\20130319_2.bak',
TO DISK = 'C:\temp\20130319_3.bak',
TO DISK = 'C:\temp\20130319_4.bak',
TO DISK = 'C:\temp\20130319_5.bak'
BACKUP DATABASE Test2
TO DISK = 'C:\temp\20130319_1.bak',
TO DISK = 'C:\temp\20130319_2.bak',
TO DISK = 'C:\temp\20130319_3.bak',
TO DISK = 'C:\temp\20130319_4.bak',
TO DISK = 'C:\temp\20130319_5.bak'
March 19, 2013 at 12:15 pm
You need to add exec(@sql). The listed code only prints the statement.
March 19, 2013 at 12:19 pm
Thanks Alan. Just tried with exec (sql) but it’s giving some syntax error.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.
March 19, 2013 at 12:21 pm
exec(@sql) but getting syntax error
March 19, 2013 at 12:24 pm
The "TO" should only exist on the 1st line.
Backup Database TEST to
disk = '',
disk = '',
March 19, 2013 at 12:30 pm
This
BACKUP DATABASE Test1
TO DISK = 'C:\temp\20130319_1.bak',
TO DISK = 'C:\temp\20130319_2.bak',
TO DISK = 'C:\temp\20130319_3.bak',
TO DISK = 'C:\temp\20130319_4.bak',
TO DISK = 'C:\temp\20130319_5.bak'
Should be this
BACKUP DATABASE Test1
TO DISK = 'C:\temp\20130319_1.bak',
DISK = 'C:\temp\20130319_2.bak',
DISK = 'C:\temp\20130319_3.bak',
DISK = 'C:\temp\20130319_4.bak',
DISK = 'C:\temp\20130319_5.bak'
The multiple backup devices should ideally be on separate devices, i doubt you'll see any improvements.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 20, 2013 at 3:46 pm
pehlebhiayatha (3/19/2013)
Perry,Currently we don’t have multiple devices or any third party tools to backup the large databases. I read in article if we strip the backup files to multiple .bak files, we can cut down the backup duration. Currently our backups are taking longer and creating only single .bak file for large database >200 GB
Thanks
Either you missed part of the article, or it was incomplete.
Striping backups to multiple files is only an advantage if each stripe goes on a physically separate set of spindles, and then only if the physical write rate was a nontrivial bottleneck in the first place.
Striping backups to the same set of spindles will only do nothing or slow you down.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply