SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup databases depending on the size to multiple files


Backup databases depending on the size to multiple files

Author
Message
pehlebhiayatha
pehlebhiayatha
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 108
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
Alan Burstein
Alan Burstein
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32781 Visits: 8581
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)



-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001

Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138129 Visits: 18218
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" ;-)
pehlebhiayatha
pehlebhiayatha
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 108
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
pehlebhiayatha
pehlebhiayatha
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 108
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
Alan Burstein
Alan Burstein
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32781 Visits: 8581
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)



-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001

pehlebhiayatha
pehlebhiayatha
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 108
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'
Richard Moore-400646
Richard Moore-400646
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1616 Visits: 1640
You need to add exec(@sql). The listed code only prints the statement.
pehlebhiayatha
pehlebhiayatha
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 108
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'.
pehlebhiayatha
pehlebhiayatha
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 108
exec(@sql) but getting syntax error
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search