Backup large database to multiple files

• pehlebhiayatha

  Old Hand

  Points: 371

  More actions

  March 15, 2013 at 1:13 pm

  #275294

  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

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  March 15, 2013 at 3:48 pm

  #1597738

  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...

  "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