Search

Showing posts with label Moving model databases. Show all posts
Showing posts with label Moving model databases. Show all posts

Friday, October 7, 2011

Moving model databases


Since model is a system databases, unfortunately we cannot move this just by detach and attach process, as we cannot attach or detach a system database.


First get the list of model database files by using this query
select name,physical_name from sys.master_files whereDB_NAME(database_id) = 'model'  
Then for each model database file that you need to move, execute statements like below
Alter Database model modify
file (NAME = 'modeldev' ,
FILENAME = 'Drive:\Path\model.mdf') -- Mention the new location


Alter Database model modify
file (NAME = 'modellog' ,
FILENAME = 'Drive:\Path\modellog.ldf') -- Mention the new location
Stop SQL Services
Move the files manually to the new location
Start SQL Services
Verify the new Location
select name,physical_name from sys.master_files whereDB_NAME(database_id) = 'model'