How to split one mdf file into two

Question

How to split one mdf file into two

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

Ratheesh.K.Nair (4/27/2009)
You can divide the backup into different files(striped backup) like if u have a 2GB DB,take backup to two file like
backup database DB_name to disk='path.file1',
disk='path.file2'
Thus you will get backup of 2GB db in 2 files 1GB each.
You need both these files for restoring the DB.

Very cool... Didn't know you could do that. Learned something new today. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mohan Kumar VS SSCommitted Points: 1724 More actions · Answer 2

backup database [dbaScripts] to disk=N'C:\backup\dbaScripts_1.bak',

disk=N'd:\backup\dbaScripts_2.bak' with stats=10;

Rgds

Mohan Kumar VS

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

Mohan Kumar (4/27/2009)
backup database [dbaScripts] to disk=N'C:\backup\dbaScripts_1.bak',
disk=N'd:\backup\dbaScripts_2.bak' with stats=10;
Rgds
Mohan Kumar VS

Heh... 10 is the default. Why bother typing it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan Mallia SSCertifiable Points: 5192 More actions · Answer 4

You can always use winrar to compress and divide in parts of 4GB

srinumutyala SSC-Addicted Points: 436 More actions · Answer 5

Hi All,

Thank you for your great support. I came to know many things from your replies.

I proposed three ways

1. While zipping, Split and Zip and restore.

2. While taking backup, Spread it across the files(This will not work for me as I was not trying to store the backup file. I want to store the mdf)

3. Rebuilding the indexes.

I think 1 and 3 will match my requirement. I have not tested any of these things. I am waiting for the approval.

Once Again thanks all of you for your support.

I will give you another reply once I test the proposed options.

Regards,

Ramesh(Srinu)

Ramesh
Talk slowly but think quickly.:-P

BLADE-1043594 SSCrazy Points: 2576 More actions · Answer 6

Try compressing the file folder using third party tools

[font="Comic Sans MS"]+++BLADE+++[/font]:cool:

Stamey SSCertifiable Points: 5600 More actions · Answer 7

For large files, I have found WinRAR to be a better option than Winzip. Perhaps it does now, but when I started needing this Winzip could not handle a file over 1G in size. I have been needing to burn backups of a 12G DB to DVD to send to a vendor for years now, and WinRAR handles the large file better, and will allow you to specify the split size, if it is large enough to need to go to multiple disks. For my instance, I had to burn to 700M CDs, with that 12G backup file. WinRAR would compress it enough to fit ton 2 CDs.

You can check them both out and see which one serves your needs better.

Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.

DKG-967908 SSCrazy Points: 2345 More actions · Answer 8

Have any body used striped backup, or what Mohan specified is called striped backup?

venu_ksheerasagaram SSCommitted Points: 1911 More actions · Answer 9

HI Very good options are given by u all as for of the discussion of "How to split one MDF file into two"

Yeah tell me one thing , that how to restore the backup files while at the time of restoring , coz they all are split in to chunks ?

How to make them total as a single at the time of Restore?

Thank You,

Venu Gopal.K
Software Engineer
INDIA

Eswin SSCrazy Eights Points: 8320 More actions · Answer 10

Hi Venu

If you are restoring from a striped database backup then you will need to specify the name of all the members of the strip.

RESTORE DATABASE MyDatabase FROM

DISK='E:\Backups\MyDatabase1.bak',

DISK=’E:\Backups\MyDatabase2.bak’

WITH MOVE ‘MyDatabase_Data’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Data.mdf’,

MOVE ‘MyDatabase_Log’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Log.ldf’

Tanx 😀

DKG-967908 SSCrazy Points: 2345 More actions · Answer 11

Hi,

I have tried striped backup for my test database with::

backup database [mytest] to disk=N'C:\dbaScripts_1.bak',

disk=N'd:\dbaScripts_2.bak'

and tried to restore with below script::

RESTORE DATABASE [mytest] FROM

DISK='C:\dbaScripts_1.bak',

DISK='d:\dbaScripts_2.bak'

WITH MOVE 'mytest' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test1.mdf',

MOVE 'mytest_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test2.ldf'

Its giving below error::

Msg 3159, Level 16, State 1, Line 2

The tail of the log for the database "mytest" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

Also if i want to specify backup size e.g. for 10gb backup can i specify 4/4/2gb. As per my convenience to copy of DVD etc while taking the backup?

Eswin SSCrazy Eights Points: 8320 More actions · Answer 12

I don't think you can specify backup size it divides the db backup approximately to equal parts.

Tanx 😀

Eswin SSCrazy Eights Points: 8320 More actions · Answer 13

Only if you need to move the physical files to another location you need add a MOVE command for each file you want to move.

Tanx 😀

manolomalaga SSCommitted Points: 1697 More actions · Answer 14

You have this utility to split files http://hacha.en.softonic.com/

But, in my opinion, is not a good practice to make backups of productions DBs in DVD. You could use tape, sun, or HD devices instead.

DKG-967908 SSCrazy Points: 2345 More actions · Answer 15

Of couse this is not a good practive to take backup on DVDs, i just wanted to explore the stiped backup utility provided by MS.