Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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? Pls suggest a method to do this.

share|improve this question

migrated from stackoverflow.com Apr 17 at 5:24

3 Answers

up vote 1 down vote

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.

share|improve this answer
up vote 1 down vote

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.

share|improve this answer
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. – darin strait Apr 17 at 10:50
He already has them mapped. That was not the question. – IvanP Apr 17 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 at 11:26
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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