Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Backup databases depending on the size to multiple files Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 1:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 1, 2016 2:29 PM
Points: 11, 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
Post #1431720
Posted Friday, March 15, 2013 3:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:15 PM
Points: 1,936, Visits: 6,465
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
Post #1431774
Posted Saturday, March 16, 2013 7:28 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Monday, July 25, 2016 4:31 AM
Points: 8,189, Visits: 16,353
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"
Post #1431893
Posted Tuesday, March 19, 2013 7:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 1, 2016 2:29 PM
Points: 11, 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
Post #1432657
Posted Tuesday, March 19, 2013 7:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 1, 2016 2:29 PM
Points: 11, 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
Post #1432666
Posted Tuesday, March 19, 2013 11:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:15 PM
Points: 1,936, Visits: 6,465
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
Post #1432809
Posted Tuesday, March 19, 2013 12:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 1, 2016 2:29 PM
Points: 11, 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'
Post #1432836
Posted Tuesday, March 19, 2013 12:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 7, 2016 1:35 PM
Points: 218, Visits: 1,633
You need to add exec(@sql). The listed code only prints the statement.
Post #1432844
Posted Tuesday, March 19, 2013 12:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 1, 2016 2:29 PM
Points: 11, 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'.


Post #1432848
Posted Tuesday, March 19, 2013 12:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 1, 2016 2:29 PM
Points: 11, Visits: 108
exec(@sql) but getting syntax error
Post #1432850
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse