1

We have a SQL Server 2008 database of size over 200GB. There is no disk space on the server to take a backup. I really need to take a backup or make another copy of this database. what would be the best way to achieve this?

Thanks

  • What version of SQL Server are you running? – Queue Mann Mar 13 '15 at 14:00
  • sql server 2008 – suneeth Mar 13 '15 at 14:02
  • 9
    You don't have any server on your entire network that can host a 200GB backup? (Also note that the backup won't necessarily be 200GB, especially if you use compression.) Maybe you need to head to Best Buy and get an external USB3 drive or something... – Aaron Bertrand Mar 13 '15 at 14:03
  • 1
    Expanding on Aaron's answer because this may not be obvious to everyone, but backups don't have to be written locally. They can be written to any network location (assuming necessary permissions). – Bryan Hudson Mar 13 '15 at 14:14
  • 5
    @Rafael No, I'm not talking about shrinking the database. Don't shrink your databases. Backups will only save actual data (not empty pages), so often a backup will already be smaller than the source database. Adding compression on top of that will squeeze more space out of the existing pages. – Aaron Bertrand Mar 13 '15 at 14:34
5

If you do not have enough diskspace first confirm that you have backup compression enabled. This will greatly decrease the size of your database backup. If you still do not have enough space on your server to put your backup.

I see 3 options of achieving this

  1. Try backing up to a network share which has sufficient diskspace to store your database backup

--> If you do not have enough space on a networkshare

  1. Try backing up to different files on different drives/networkshares so you split the total size of the backup and store all the parts on different locations. This will also increase the speed of your backup (Check out http://www.mssqltips.com/sqlservertip/935/backup-to-multiple-files-for-faster-and-smaller-sql-server-files/)

--> If you still do not have enough space on any server/share/...

  1. If you can physically reach your server, go buy a USB disk with sufficient space and connect this to the server and backup to this location.
  • 2
    The only thing I would add here is considering using cloud space. There are security and throughput concerns, but if the problem is actually space this might be an option. – Sean Long Mar 13 '15 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.