attach all the datababses
-
I am wondering how can i attach all the datababses when each and every
database are in differnt folders.I dont have any kind of records in any of
the system tables for these databases.Each and every database is in
different folders.Here is the structure what i have
F:\data\a\a_data.mdf;F:\data\b\b_data.mdf;....F:\data\z\z_data.mdf.
Now i need to attach all these dbs to a server.Pls suggest,with any
scripts;if possible
-
Hi,
The basic syntax is the same. You will just need to modify a couple of parameters for each statement. Following your structure, here is a couple of statements to get you started...
exec sp_attach_single_file_db 'a_data', 'F:\Data\a\a_data.mdf'
exec sp_attach_single_file_db 'b_data', 'F:\Data\b\b_data.mdf'
The log files will be created in the data file location specified at installation. At least I think that is the case.
If you know the location of the log files, use
exec sp_attach_db 'a_data', 'F:\Data\a\a_data.mdf', '<Log File Location>'
-
... or you can do this through Enterprise Manager. Point to Databases then All Tasks and then Attach Database...
If you have a lot of DBs and need to attach them periodically then you should think about a cursor. But if there is one time job I think you shoud follow cstrong or my suggestions and attach them one by one
Good luck!
-
I have the log files.The main problem is there is a seperate folder for each and every database.the log file exist eith the datafile.I need to attach all these databases which are in their corresponding folders
-
Why is the separate folder being the main problem for you? Why cann't you simply write particular .mdf and .ldf files location in a script as it was suggested previously? Maybe I missed something...
-
Detailed explanation of your problem:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_03_89pv.asp
-
My two cents...
One way would be to write a little code, use the filesystem object to loop through the files and folders, attach any mdf/ldf found. Lots of variations possible.
Another would be to use explorer to find all the mdf/ldf on the drive, then move them to a single folder (is there really a reason to have them in separate folders?), would make it easier to attach if all in same place.
You might also take a look at this article - http://www.sqlservercentral.com/columnists/awarren/reattachingdatabases-somecodeandacontest!.asp
-
If you are trying to attach all the files to a simillar location as the current server, you can use the script below. It will print out the script with sp_attach_db statements for all the databases. It doesn't look for secondary files and you can update the script accordingly.
--Script to attach all User databases to original location.
declare curname cursor for select dbid from sysdatabases where dbid > 4
open curname
declare @dbid int
fetch next from curname into @dbid
while @@fetch_status = 0
begin
declare @sql varchar(500)
declare @datafile varchar(100)
declare @logfile varchar(100)
select @datafile=filename from sysaltfiles where dbid = @dbid and groupid = 1
select @logfile=filename from sysaltfiles where dbid = @dbid and groupid = 0
--print @logfile
set @sql = 'sp_attach_db ' + db_name(@dbid) + ',''' + ltrim(rtrim(@datafile)) + ''',''' + ltrim(rtrim(@logfile)) + ''' ;'
print @sql
fetch next from curname into @dbid
end
close curname
deallocate curname
-
sa24,
Must be an easier way (move logs)
Just wanted to pass on that I modified you script and posted it here to almost fully automate the moving of log and data files.
I thank you for the base idea. I had to modify mine. For some reason my sysaltfiles didn't agree with my dbname..sysfiles. So in my script I read from the sysfiles into a temp table and then I do the script building from there.
I have mine do the detach, copy and then reattach automatically.
Thanks again.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply