October 29, 2007 at 10:27 am
I have a db that has 2 data files. I'd like to restore as one data file.
What's the best way to do this?
October 29, 2007 at 1:54 pm
What is the file extension you are holding??
If you have .mdf and .ldf files then you can use sp_attach_db stored procedure to attach the db
If you have .mdf file alone then you can use sp_attach_single_file_db stored procedure stored procedure to attach the db
Regards..Vidhya Sagar
SQL-Articles
October 29, 2007 at 1:59 pm
I have 2 data files...(I'd like the 2 data files to become 1
Data.mdf
Data2.ndf
1 log file
Log.ldf
October 29, 2007 at 2:41 pm
Try the following command.
sp_attach_db 'dbname','y:\xxx\Data.mdf','y:\xxx\Data2.ndf','y:\xxx\Log.ldf'
Before executing change the dbname with correct database name and change the path y:\xxx with the correct path where the file exists.
Regards..Vidhya Sagar
SQL-Articles
October 30, 2007 at 1:10 am
You have 2 data files one with extn ,mdf and other with .ndf. If so then you can combine them into one by doing the following
1. attach the database
2. create a copy of the database with out any structures.
3. move the objects and data from both data files into the new database which stores the data in a songle file
4. remove the older database
5. renmae this to be your database
hope i have clarified your doubt. incase if i have misunderstood you. let me know.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
October 30, 2007 at 1:33 am
I agree with Sugeesh on this issue.
October 30, 2007 at 12:07 pm
You can also potentially move all the objects from the NDF into the MDF and then remove the NDF from the database. You can move tables by moving the clustered indexes. Sprocs, functions, indexes will probably need to be dropped and recreated specifying the MDF as the location in the ON clause.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 31, 2007 at 7:01 am
Easiest way to do this if this is just another file in the same filegroup is to use the emptyfile option of shrinkfile:
use dbname
go
dbcc shrinkfile(data2.ndf,emptyfile)
alter database whatever remove file logical file name of .ndf
Backup database before you start!
may take a while if data file contains a lot of data
---------------------------------------------------------------------
October 31, 2007 at 9:08 am
I should have remembered that. Good idea, George!
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply