October 25, 2007 at 1:02 pm
I ran into a situation where I received a database cold backup from one of our customers that needed to be investigated for a serious production database issue. This was very high priority. I ran into dificulties attaching all the 31 files due to the 16 file max limitation of the SP_ATTACH_DB stored procedure. Here is the solution I used. Steve, you had mentioned that there is a restore method that can be used. Could you please illustrate based on my solution? Thank you.
Create database Method:
create database Mydb
on
(filename='c:\mssql7\data\Mydb_Primary.mdf'),
(filename='e:\Mylp\D_MydbData\MydbCM.ndf'),
(filename='e:\Mylp\D_MydbData\MydbCMIX.ndf'),
(filename='e:\Mylp\D_MydbData\MydbLP.ndf'),
(filename='e:\Mylp\D_MydbData\MydbLPIX.ndf'),
(filename='e:\Mylp\D_MydbData\MydbLP2.ndf'),
(filename='e:\Mylp\D_MydbData\MydbLP2IX.ndf'),
(filename='e:\Mylp\D_MydbData\MydbCC.ndf'),
(filename='e:\Mylp\D_MydbData\MydbCCIX.ndf'),
(filename='e:\Mylp\D_MydbData\MydbPC.ndf'),
(filename='e:\Mylp\D_MydbData\MydbPCIX.ndf'),
(filename='e:\Mylp\D_MydbData\MydbPC2.ndf'),
(filename='e:\Mylp\D_MydbData\MydbPC2IX.ndf'),
(filename='e:\Mylp\D_MydbData\MydbPT.ndf'),
(filename='e:\Mylp\D_MydbData\MydbPTIX.ndf'),
(filename='e:\Mylp\D_MydbData\MydbPTS.ndf'),
(filename='e:\Mylp\D_MydbData\MydbPTSIX.ndf'),
(filename='e:\Mylp\D_MydbData\MydbRptData01.ndf'),
(filename='e:\Mylp\D_MydbData\MydbSF.ndf'),
(filename='e:\Mylp\D_MydbData\MydbSFIX.ndf'),
(filename='e:\Mylp\D_MydbData\MydbTND.ndf'),
(filename='e:\Mylp\D_MydbData\MydbTNDIX.ndf'),
(filename='e:\Mylp\D_MydbData\MydbWHRS.ndf'),
(filename='e:\Mylp\D_MydbData\MydbWHRSIX.ndf'),
(filename='e:\Mylp\D_MydbData\MydbWHRS2.ndf'),
(filename='e:\Mylp\D_MydbData\MydbWHRS2IX.ndf'),
(filename='e:\Mylp\D_MydbData\MydbWRK.ndf'),
(filename='c:\mssql7\data\MydbLOG1.ldf'),
(filename='c:\mssql7\data\MydbLOG2.ldf'),
(filename='c:\mssql7\data\MydbLOG3.ldf'),
(filename='c:\mssql7\data\MydbLOG4.ldf')
for attach;
go
Best Regards,
~David
October 25, 2007 at 5:13 pm
Yikes, I'd forgotten about the 16 file limit. Sorry David.
I looked through the sp_attach_db code and there are only 16 parameters. I suspect you could copy the code, create your own version with 32 parameters, and give it a go. It's not that complicated.
I've also pinged a few people for workarounds.
If you get the chance, why do you have 31 files? Not that it matters, just curious.
October 25, 2007 at 5:22 pm
Steve pinged me - the only way to do this is as you have above CREATE DATABASE ... FOR ATTACH.
If none of the files have changed locations from when the database was detached, you only need to specify the primary file. If any have, you only have to specify the primary plus those files. Can't remember if this bit of the functionality is on 2000 or not.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 26, 2007 at 5:58 am
I thought about creating my own. The CREATE DATABASE ... FOR ATTACH worked well once I got the syntax down. This database was created long before I got here. Most of our customers are Oracle so all the files map to tablespaces in Oracle. 31 is a bit high and I am not sure atthis point of the intentions behind this. Thank you for all your help.
David
Best Regards,
~David
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply