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 MannCommented Mar 13, 2015 at 14:00
-
sql server 2008– suneethCommented Mar 13, 2015 at 14:02
-
9You 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 BertrandCommented Mar 13, 2015 at 14:03
-
1Expanding 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 HudsonCommented Mar 13, 2015 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 BertrandCommented Mar 13, 2015 at 14:34
|
Show 4 more comments
1 Answer
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
- 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
- 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/...
- 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.
-
2The 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. Commented Mar 13, 2015 at 18:39