2

I have a database of size 290 GB running on SQL Server 2005 (compression is not available) and disk space is z: drive 250 GB and Y: drive is 80 GB.

How can I take a full backup in this scenario? How can I split full backup into different files in this ratio?

Please suggest a method to do this.

3 Answers 3

2

If you have access to another server on your network with larger disks, you can do database backup to a network share:

backup database YOURDATABASE 
to disk = '\\NETWORKSERVER\SHARENAME\YOURDATABASE_yyyymmdd.bak' 
with init

For this to work, the share would need to have Write permissions from the domain account under which your SQL Server is running.

If you don't have access to another server with enough disk space, you may want to get an inexpensive Network Attached Storage type of device (e.g. synology) and perform your backups to it.

1

You don't have to use local storage for backup ( and you should not ). Also, by database size did you mean only data, because if your log file is large backup will be that much smaller.

To split:

BACKUP DATABASE DataB TO
DISK = 'Z:\DBBackups\DataB\DataB_Split1.bak'
,DISK = 'Z:\DBBackups\DataB\DataB_Split2.bak'
,DISK = 'Y:\DBBackups\DataB\DataB_Split3.bak'

This will split database in 3 equal sized parts.

3
  • How would the OP create those drive mappings? Mapping drive letters is a frequent source of confusion for people don't realize SQL is running as another user and drive mappings are not shared between users. Apr 17, 2013 at 10:50
  • He already has them mapped. That was not the question.
    – IvanP
    Apr 17, 2013 at 11:05
  • @darinstrait you can map using a system user and then they'll be available for all users. Or, like Arsen's answer, you don't even need a mapped drive, you can back up directly on a shared folder.
    – Marian
    Apr 17, 2013 at 11:26
1

There are third party products, such as SQL Lightspeed, that provide backup compression for Standard editions of SQL Server. There is a good chance that the purchase and installation of such a product will allow you to back up locally. The size of the nacho file depend on how much data is actually stored in the database and it's compress ability.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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