Backup large database to multiple files

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Backup large database to multiple files

Backup large database to multiple files

Rate Topic

Display Mode

Topic Options

Author

Message

pehlebhiayatha

Posted Friday, March 15, 2013 1:13 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:36 PM
Points: 8, Visits: 31

Hi everyone,
I manage 100+ SQL servers and some of them contain over 120 – 800 GB + databases. I googled for backup script that backs up database depending on the size and strips the .bak file to multiple files. So far no luck. The closest one I got to is this script. Is there a way I can backup smaller databases in one .bak file and 60 + GB databases to multiple .bak (stripped) files dynamically in the same backup script? This way I can standardize it across all the servers. Thanks in advance or your help.

Hi everyone,
I manage 100+ SQL servers and some of them contain over 120 – 500 GB + databases. I googled for backup script that backs up database depending on the size and strips the .bak file to multiple files. So far no luck. The closest one I got to is this script. Is there a way I can backup smaller databases in one .bak file and 60 + GB databases to multiple .bak (stripped) files? Thanks in advance or your help.

DECLARE @Baksql VARCHAR(8000)
DECLARE @BackupFolder VARCHAR(100)
DECLARE @BackupFile VARCHAR(100)
DECLARE @BAK_PATH VARCHAR(4000)
DEclare @BackupDate varchar(100)
DEclare @Size varchar(100)

-- Setting value of backup date and folder of the backup
SET @BackupDate = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551
SET @BackupFolder = 'C:\temp\'
SET @Baksql = ''
-- Declaring cursor
DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR
SELECT NAME FROM SYS.DATABASES
WHERE state_desc = 'ONLINE' -- Consider databases which are online
AND database_id > 4 -- Exluding system databases

-- Opening and fetching next values from sursor
OPEN c_bakup
FETCH NEXT FROM c_bakup INTO @BackupFile
WHILE @@FETCH_STATUS = 0
BEGIN

SET @BAK_PATH = @BackupFolder + @BackupFile

-- Creating dynamic script for every databases backup
SET @Baksql = 'BACKUP DATABASE ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_FullBackup_'+@BackupDate+'.bak'' WITH INIT;'
-- Executing dynamic query
PRINT (@Baksql)
EXEC(@Baksql)

-- Opening and fetching next values from sursor
FETCH NEXT FROM c_bakup INTO @BackupFile
END

-- Closing and Deallocating cursor
CLOSE c_bakup
DEALLOCATE c_bakup

Post #1431706

Alan.B

Posted Friday, March 15, 2013 3:48 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:06 AM
Points: 223, Visits: 1,137

Please don't double post.

I inlcuded a better script for handling this
in your original post that does not include any cursors or loops.

P.S. if you search online for a way to do something in SQL and you find a solution that inlcudes a cursor or loop - I'd suggest that you keep searching...

-- AJB
xmlsqlninja.com

Post #1431811

« Prev Topic | Next Topic »

Permissions