Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 9, Visits: 72
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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 581, Visits: 2,709
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1431774
Posted Saturday, March 16, 2013 7:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 6,351, Visits: 13,680
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
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 9, Visits: 72
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
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 9, Visits: 72
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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 581, Visits: 2,709
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1432809
Posted Tuesday, March 19, 2013 12:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 9, Visits: 72
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-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:19 PM
Points: 199, Visits: 1,501
You need to add exec(@sql). The listed code only prints the statement.
Post #1432844
Posted Tuesday, March 19, 2013 12:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 9, Visits: 72
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
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 9, Visits: 72
exec(@sql) but getting syntax error
Post #1432850
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse