登入
 
首頁文件庫學習園地下載支援社群論壇
IT 專業人員的技術資源 >
> 
> 
Is there any job that can split large database backup file into several smaller files

Answered Is there any job that can split large database backup file into several smaller files

所有回覆

  • 2012年2月25日 上午 04:32
    版主
     
     
    登入以投票
    0

    Hi Wallace,

    You could use something like WinRAR program to split the large backup file into smaller chunks. I use this method to transfer large backup files from environment to another and works fairly well.



    Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/

     
  • 2012年2月25日 下午 03:16
     
     已答覆
    登入以投票
    1

    Hi Wallace Chan,

    You can use the below method, to spilt the large database SQL backup file as: For reference please find the attached snap shot.

    NOTE ***: If you are using below to SQL Server version 2005, please dont use COMPRESSION option.. it will allows compress SQL Backups only SQL Server 2008 (or) higher to 2008 versions.

     

    BACKUP DATABASE [satish] TO 

    DISK = N'D:\SQL_Test_Backups\Backup\satish1.bak', 

    DISK = N'D:\SQL_Test_Backups\Backup\satish2.bak', 

    DISK = N'D:\SQL_Test_Backups\Backup\satish3.bak'

    WITH NOFORMAT, NOINIT,  NAME = N'satish-Full Database Backup',

    SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

    GO

    declare @backupSetId as int

    select @backupSetId = position from msdb..backupset

    where database_name=N'satish' and backup_set_id=(select max(backup_set_id)

    from msdb..backupset where database_name=N'satish' )

    if @backupSetId is null

    begin raiserror(N'Verify failed. Backup information for database ''satish'' not found.', 16, 1) end

    RESTORE VERIFYONLY

    FROM  DISK = N'D:\SQL_Test_Backups\Backup\satish1.bak', 

    DISK = N'D:\SQL_Test_Backups\Backup\satish2.bak', 

    DISK = N'D:\SQL_Test_Backups\Backup\satish3.bak'

    WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

    GO

    Thanks,

    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

     
  • 2012年2月25日 下午 05:07
    版主
     
     
    登入以投票
    0
    One note on the method provided by Satish is that you have to have all the files to restore.  If one file is missing you are screwed, so make sure that if you stripe your backups across multiple files, that you always maintain those files as a set for recovery.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

     
  • 2012年2月26日 上午 10:43
     
     已答覆
    登入以投票
    0

    This method of striping your backuo to multiple files is done for performance reasons when IO is an issue.

    Read this article.

    Backup to multiple files for faster and smaller SQL Server files

    This shows how you do that in SSMS GUI and through script.

    Keep note of what Jonathan said.

    Hope this answers your question.

    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    • 已標示為解答 Wallace Chan 2012年2月28日 上午 01:42
    •  
     
  • 2012年2月26日 上午 10:53
    解答者
     
     
    登入以投票
    0
    What version are you using ? EE SQL Server 2008 has compressed backup feature which works very well... What is the purpose of splitting the .bak ? 

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

     
  • 2012年2月27日 上午 10:19
     
     
    登入以投票
    0

    Satish is right but as jonathan states you need to have all the files to restore... you cannot miss files...

    but why do you want to split the backup firstly???

    how big is the database???

    which version of sql server are you using???(if 2008 you could compress the size of the backups by upto 90%)

    you could also go in for 3rd party tools...cant suggest you one coz i've never usd one...

     
  • 2012年2月28日 上午 01:42
     
     
    登入以投票
    0

    Satish is right but as jonathan states you need to have all the files to restore... you cannot miss files...

    but why do you want to split the backup firstly???

    >>>>>Since we want to file transfer smaller backup files to other machines

    how big is the database???

    >>>>> over 200 GB

    which version of sql server are you using???(if 2008 you could compress the size of the backups by upto 90%)

    >>>> 2008 standard version, is it using COMPRESS options?

    you could also go in for 3rd party tools...cant suggest you one coz i've never usd one...