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