April 25, 2009 at 1:03 am
Hi All,
I have a database TFSVersionControl and the mdf file is TfsVersionControl.mdf which is about 7GB.
Now I want to take this mdf file backup onto a DVD.
How can I split this file into two files, And when I attach in a new server,How can I do that without any loss of data.
Regards,
Ramesh
Ramesh
Talk slowly but think quickly.:-P
April 25, 2009 at 2:28 am
Take the full backup of the DB,copy it and restore it on another server.You cannot split the mdf file.
If the backup is again >5GB,take striped backup.Refer BOL for this.
In 2008 you have compressed backup but not in 2005.
April 25, 2009 at 2:37 am
Thanks for the reply.
I can take backup and restore, But the Backup size remains same.
Is there a way to Specify multiple mdf files while attaching or restoring the database.
My main initension is to write the mdf file onto a DVD (for maintaining backups) which is abt 7GB. So I want the mdf file less than 4GB.
Is there any way to achieve this.
Regards,
Ramesh
Ramesh
Talk slowly but think quickly.:-P
April 25, 2009 at 3:03 am
Is there a way to Specify multiple mdf files while attaching or restoring the database
A database can have only one mdf file.So you cannot split this.
My main initension is to write the mdf file onto a DVD (for maintaining backups) which is abt 7GB. So I want the mdf file less than 4GB.
You can take striped backup i.e.,one backup distributed to many disks and thus each part of the backup will be of less size ...so copy all these and restore all of them.
April 25, 2009 at 6:55 am
This is what i did for a large database of 70G.B size for restoring it in UK DC. I took the backup of the database . I used winzip(do not remember Version) there is a option to split the file during zipping i split the file into 4G.b chunks .After zipping the file I got 4x4= 16G.B zip files
April 25, 2009 at 7:18 am
SQL Reddy (4/25/2009)
A database can have only one mdf file.So you cannot split this.
opss...I guess the information above is not correct.
It's possible to create more than one DATAFILE per DATABASE: Primary datafiles (only one) and secondary datafiles (N...)
The MDF or NDF file name extension is just a recommendation, it's not obrigatory. You can use your own file name extension if needed.
So, if you create another FILEGROUP and DATAFILE, you can MOVE some objects to the new place and split your DATABASE among datafiles and BACKUP FILEGROUPS instead of all database.
---------------------
Alex Rosa
http://www.keep-learning.com/blog
April 26, 2009 at 9:11 am
As above, zipping sounds like your best plan. Bak files will compress a fair bit.
April 26, 2009 at 9:40 am
ally (4/26/2009)
As above, zipping sounds like your best plan. Bak files will compress a fair bit.
If you go this route be sure to test your backup after it's been zipped. I know people that have had problems restoring zipped backups.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 26, 2009 at 5:08 pm
Alvin Ramard (4/26/2009)
ally (4/26/2009)
As above, zipping sounds like your best plan. Bak files will compress a fair bit.If you go this route be sure to test your backup after it's been zipped. I know people that have had problems restoring zipped backups.
Its not only in this case as a best practice you should always consider testing your backups.
April 27, 2009 at 6:26 am
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.
April 27, 2009 at 6:33 am
If you really wish to split to MDF into two files then you have only one solution and it has to be done on SOURCE Server/Database.
1) Add another Filegroup and create mulitple Files in the new file group.
2) Rebuild your clustered index on new File group. All data will move to new file group and data will be equally distributed among files in the Filegroup. Though non-clustered index will remain on the PRIMARY File group.
3) After rebuilding Clustered indexes , shrink the file(s).
4) detach db
5) copy files to DVD(s)
6) copy files from DVD to Server
7) Attach files
Above mentioned step can also be done by adding a file in PRIMARY File group and rebuiding CLUSTERED INDEX without adding a new filegroup
Deepak
April 27, 2009 at 10:39 am
I think striped backup would be a more practical approach instead of zipping etc. zipping them and copy it to DVD is fine, but you could have a problem while restoring....you never know.
April 27, 2009 at 12:32 pm
Alex Rosa (4/25/2009)
SQL Reddy (4/25/2009)
A database can have only one mdf file.So you cannot split this.opss...I guess the information above is not correct.
It's possible to create more than one DATAFILE per DATABASE: Primary datafiles (only one) and secondary datafiles (N...)
I think you didn't read what SQL Reddy wrote; even if you can have more than one datafile it is also true you can have just one mdf file.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 27, 2009 at 5:22 pm
PaulB (4/27/2009)
I think you didn't read what SQL Reddy wrote; even if you can have more than one datafile it is also true you can have just one mdf file.
Hi Paul,
Let me try to explain again my point of view.
It's not correct to say that it's possible to create just one MDF file, because the file name extension is only a recommendation.
The correct terms are: PRIMARY FILES and SECONDARY FILES.
For example, the script below create a database with two datafiles with the same name and extension:
CREATE DATABASE test
ON
PRIMARY
(NAME = test1,
FILENAME = 'c:\data1\test1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = test2,
FILENAME = 'c:\data2\test1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
(NAME = Archlog1,
FILENAME = 'c:\data1\test3.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
Off course, the directory is different because this is a Operating System restriction and not a SQL Server.
See you all.
---------------------
Alex Rosa
http://www.keep-learning.com/blog
April 27, 2009 at 9:58 pm
Hi sreenu,
U hav received the answers for u r question. Those are all good , In that answers there's one query for splitting the database backup.Hav u checked this one?Is it working perfectly?Can u pls send me the exact query for how to take the DB back up on two diff drives.
backup database DB_name to disk='path.file1',
disk='path.file2'
see my requirement is a DB named DB_Venus and i want to take this backup on two drives C and D .
Can u tell me how should be the Path.file1 is looks like?
Thanking You,
Venu Gopal.K
Software Engineer
INDIA
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply