| Author |
Topic |
|
arusu
Yak Posting Veteran
60 Posts |
 Posted - 2011-09-14 : 09:54:38
|
| Hi, I need to get the first non-system database on a server. I need it so that I can retrieve the default log and data path for non-system databases. The reason that I can't use a specific database is because I am restoring a database from code so it does not exist yet. What I am doing is using the master database against the sp_helpfile stored procedure if a non-system database does not exist but I need the name of the first one to use it with this stored procedure.Am also open to different suggestions.Thanks in advance |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-14 : 11:33:34
|
| You can retrieve that from the model database.SELECT filename FROM model..sysfiles |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-09-14 : 11:34:05
|
Not sure what FIRST means in that context, but maybe this will get you started:SELECT *FROM sys.databasesWHERE owner_sid <> 1 ; |
|
|
|
arusu
Yak Posting Veteran
60 Posts |
Posted - 2011-09-14 : 13:02:02
|
| thanks for the help guys.I actually ended up using sp_helpfile but to retrieve the database that I wanted. I got a list of all of the databases (using c# code) and I put a filter on the DBID so that it is greater than 4 since the system databases DBID's are fixed (1,2,3,4). This way I could get the default data and default log paths for the first database that was returned to me.do you see any issues with this? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-14 : 14:41:02
|
| dbid > 4 only works if you don't have reporting services installed or have a distribution database (replication) on the server.If you're looking for the default path, the query I showed above will return it. |
|
|
|
arusu
Yak Posting Veteran
60 Posts |
Posted - 2011-09-14 : 15:07:08
|
| actually that one does not work as I've changed my default data path to "C:\Temp" just for testing purposes and it did not return the path. So for the reporting and distribution databases, can the path be different for them vs the user databases? if so can you please provide instructions on how to do that? (for my testing) All I know is that for user databases you right click the server connection->Click on properties->Go to Database Settings and the paths can be changed there. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-14 : 15:14:04
|
| Every database can have a different path.Path should always be specified when creating databases, but if it's not, then it will use the "default."And, I forgot that you can change the default without modifying model. For your purposes, I guess selecting where database_id = 5 should work. |
|
|
|
arusu
Yak Posting Veteran
60 Posts |
Posted - 2011-09-14 : 16:02:04
|
| Ok thanks a lot Russel, I feel more confident in accomplishing my task now but I am still just a little bit unclear on one thing. if i changed that default data and log path with the instructions from my last message, does that change the default paths for the report and replication databases? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-14 : 16:06:28
|
| I think so |
|
|
|
|
Get Non-System Databases