未加星标

SQL Server moving System Databases Part 1 msdb and model

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

I can remember a few distinct unpleasant memories while fishing. In one, I was fishing out on the Rainbow Flowage with my Dad, oldest son, and nephew. Since there wasn’t much room in the boat, I decided not to bring my muskie tackle box. I brought my pole with a single lure on it to do a bit of muskie fishing, but our targeted species that day was walleye, bass, and panfish.

About an hour into the trip I swung my pole hard to push out another cast and the lure simply popped off the line. The sound of line snapping is never a welcome one for a fisherman. Not only did I lose a lure worth around $20, but I was also finished muskie fishing for the trip, since I hadn’t brought any other lures. The lesson in the story is that your gear is only as strong as its weakest link. In my case, the line snapped, but it also could have been the leader breaking that ended my day early.

I recently ran out of C: drive space on my test environment database server. The drive is small at only 33 GB, so there just wasn’t much space to free up. I took a look and noticed the system databases, except for tempdb, were all residing on the C: drive. While there wasn’t much space between them, I still thought moving them would be the best way to free up a bit of space on the drive.

I looked at the database properties in SQL Server Management Studio (SSMS) to find the current location of the system database files.


SQL Server moving System Databases Part 1   msdb and model

I found them to be in the default folder for SQL Server 2008 installations. I navigated to the drive to confirm they were there and see what else was in there.


SQL Server moving System Databases Part 1   msdb and model

You can see the total used space for the three system databases is around 186 MB. Again, this is a very small amount of space, but it is the only space I know that I can free up.

The next step is to move the files’ locations in SQL Server’s records. I do this with an ALTER DATABASE command for each file.


SQL Server moving System Databases Part 1   msdb and model

With this complete, SQL Server will now look for the files in that location the next time the service is started. So I need to stop the service. I did it through the SQL Server configuration manager, but you can do it from the windows Services screen as well.


SQL Server moving System Databases Part 1   msdb and model

Next I need to go onto the server and physically move the files from their current folder to the new location that I specified.


SQL Server moving System Databases Part 1   msdb and model
SQL Server moving System Databases Part 1   msdb and model

This I handled with a simple cut and paste in Windows Explorer. The last step is to start the service back up. Everything came up fine and I am now able to see the files are in the new locations.


SQL Server moving System Databases Part 1   msdb and model

In the next post I will tackle moving the master database, which has a few wrinkles that make it much harder to move.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

分页:12
转载请注明
本文标题:SQL Server moving System Databases Part 1 msdb and model
本站链接:http://www.codesec.net/view/522082.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(24)