none
SQLServer 2000 Backup on Multiple Disks RRS feed

  • Question

  • I have one of the database on SQL Server 2000. The database is close to 200 GB. Any single local disks I have are not free enough to accommodate the entire backup. Is there a T-SQL script that I can run to specify the multiple backup file location with size so I can distribute my backup across multiple disks. Or Any other workaround someone wants to suugest? I tried the USB disks , however SQL Server wont use that to save the backup file.

    Thanks

    Monday, May 5, 2014 5:54 PM

Answers

  • there is no way you can limit the size in the backup command but

    As you said the database is 200 GB, check the free space left the drive.

    Let's say f drive has 60, G Drive has 70 and H drive has 120 gb

    create 8 files...

    place 2 files  f drive ,2 files on G and 4 files on H. Data will spread using proportional fill algorithm.

    Once the backup is completed, you will find that each file is approximately 25 GB in size.

    Test for small database using the above logic

    --Prashanth

    Monday, May 5, 2014 6:48 PM

All replies

  • See

    http://www.mssqltips.com/sqlservertip/935/backup-to-multiple-files-for-faster-and-smaller-sql-server-files/


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, May 5, 2014 6:09 PM
  • Thanks...

    I tried that before posting here. Even with that it runs our of the space. I tried to spread the backup across 4 disks , however it just writes to 1st two disks then throws the error. That's why I wanted to see any option where I can specify the max. size with each backup part.

    Monday, May 5, 2014 6:22 PM
  • there is no way you can limit the size in the backup command but

    As you said the database is 200 GB, check the free space left the drive.

    Let's say f drive has 60, G Drive has 70 and H drive has 120 gb

    create 8 files...

    place 2 files  f drive ,2 files on G and 4 files on H. Data will spread using proportional fill algorithm.

    Once the backup is completed, you will find that each file is approximately 25 GB in size.

    Test for small database using the above logic

    --Prashanth

    Monday, May 5, 2014 6:48 PM