Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GRAYWOLF
Posting Yak Master
106 Posts |
 Posted - 2009-03-07 : 05:23:52
|
| I can run this query so I can multiple instances of this combination:Select * from dbo.FileGroupsorder by [server], [Database], [Type]Basically I will get a return similar to:Server1, databaseA, data, Data_file1.mdfServer1, databaseA, data, Data_file2.ndfServer1, databaseA, data, Data_file3.ndfI need to pull the info and stick it in a table like:[Server], [Database], [Type], [Filename1], [Filename3], [Filename3]Server1, databaseA, data, Data_file1.mdf, Data_file2.mdf, Data_file3.mdf---------------------------Working until "the morning sun sets the midnight sky on fire"! |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-03-07 : 07:08:11
|
| declare @tbl table([Server] varchar(32), [database] varchar(32), [Type] varchar(32), [File] varchar(32),[Filename] varchar(32))insert into @tbl select 'Server1', 'databaseA', 'data', 'Data_file1.mdf','Filename1'insert into @tbl select 'Server1', 'databaseA', 'data', 'Data_file2.ndf','Filename2'insert into @tbl select 'Server1', 'databaseA', 'data', 'Data_file3.ndf','Filename3'select [Server],[database],[Type],[Filename1],[Filename2],[Filename3]from @tblpivot (max([file]) for [Filename] in ([Filename1],[Filename2],[Filename3]))p |
 |
|
|
GRAYWOLF
Posting Yak Master
106 Posts |
Posted - 2009-03-10 : 02:10:39
|
| Sorry, I was a little short on information. Of our 200 or so DBs a dozen or two have multiple files. I need to run a query to pull just the ones with the multiple files and store them in the new table (dbo.SplitDataLog)One problem is there is no naming convention for these files. I need them in the correct order...This is part of a backup test that goes down the list of DBs and just restores it, tests it and deletes it. If there is a way to do the restores without having to specify the Data and Log file placement, that would probably be even beter...---------------------------Working until "the morning sun sets the midnight sky on fire"! |
|
|
|
GRAYWOLF
Posting Yak Master
106 Posts |
Posted - 2009-03-11 : 02:03:20
|
| Would it be easier to set up something to insert [server] and [Database] cells for those DBs that have more than 2 rows?Basically, using Count to identify where the combination of the 2 columns occupy more than 2 rows and then creating a single insert of those fields in to another table.---------------------------Working until "the morning sun sets the midnight sky on fire"! |
|
|
|
GRAYWOLF
Posting Yak Master
106 Posts |
Posted - 2009-03-11 : 04:33:54
|
| I was able to do this, now I need to figure out how to get the file names in the table.---------------------------Working until "the morning sun sets the midnight sky on fire"! |
|
|
|
|
|
|
|
|
Picking Data from a Table