Altering Model Database
-
Hi there
I recently read in 'SQL Server 2000 Programming' that the model database is utilized, when a new database is created. Seems logical, because of the name.
The model database's Transaction-Log directory is the same as the Data directory, which you normally assign during the SQL Server setup procedure. In my case the data directory is on the D: drive (defined during setup). The SQL Server 7.0 (yes, I know, but hey...) application itself is on the C: drive. When we create a database we store the transaction log files on our E: drive.
C: SQL Server application
D: Data files
E: Transaction Log files
Problem: Creating a database is a boring procedure, because I have to manually alter the Transaction Log file location for each new database I create. This is because the model database's log directory is on the D:
drive.
Question: Is there a way to persuade MS SQL to create the Transaction Log files on the E: drive for every new database I create, without having to use T-SQL?
I tried modifying the model database using:
alter database model modify file
(
name=modellog,
Filename='E:\SQL\LOGS\MODELLOG.LDF'
)
..., but that didn't work. You are only allowed to change the filename for
the tempdb database.
I then tried to backup the model database, restore it and supply the new directory name for the transaction log files.
That didn't work either.Does anybody else have a tip that would help?
Thanks for information.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
SQL2K has a server config setting that lets you set the default data and log paths. Other than that, what I use is a small app that prompts user for db name, does the set up (plus replication and a bunch of other stuff I need too).
Andy
-
See http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071 for additional information on moving databases and their files.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Thanks a lot for the tip with the sp_attach_db and sp_detach_db sprocs. I was looking at them before I posted, but was not quite sure what to do with them.
The KB article is a tricky little thing and without the -T3608 flag it probably wouldn't have worked anyway.
I'll keep you posted. Cheers.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
Well, what should I say?
It worked just fine. Thank you Antares686, for pointing in the right direction.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
Seems to be a common thing for people to ask, seems quite funny MS did not include this information in SQL BOL with an easy to find reference if it is there.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Hi there,
I have a similar problem....
I moved my model db to another drive. Did set the startup parameters, moved the database & restarted the server & everything worked 100% until we rebooted the server this morning...
The model db dissapeared...
Try to restore or attach, I get the error you were refering to...
Any ideas??
Thanks in advance!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply