 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008Move installation or databases
Hi, I need to move a SQL 2005 installation to another drive, or at least all its databases. How do I go about it? Craig see the link bellow http://msdn.microsoft.com/en-us/library/ms345408.aspx http://oneplace4sql.blogspot.com/2007/05/moving-sql-server-2000-databases-to.html vinu > Hi, > > I need to move a SQL 2005 installation to another drive, or at least all > its > databases. How do I go about it? This seems to be information for SQL 2000, is it the same as in 2005? Would the below work? Go into SQL Server Management Studio (BACKUP YOUR DATABASE BEFORE YOU CONTINUE) Expand the Databases folder Right click on database you want to move the files for, then Tasks and Detach After clicking on OK, the database will no longer be seen. Then copy the .MDF and .LDF files to the new location you want them to reside. Right click on Database, then attach. Click on Add, then browse to the new location of the .MDF file. Select that file and click on OK. It will take it a minute to process the database. After it is done thinking, you will see it listing the .MDF file and a .LDF file listed. You will want to change the log file so that it points to the one that you moved. After all is done, click on OK, and your database should now be accessable, and be using the files in the new location. > Craig > [quoted text clipped - 10 lines] > > its > > databases. How do I go about it? Craig You can use detach/attach technique to move all the user databses. to move the system databse see the link bellow http://support.microsoft.com/kb/224071 vinu > This seems to be information for SQL 2000, is it the same as in 2005? > [quoted text clipped - 41 lines] >> > its >> > databases. How do I go about it? Thanks for the response, I think I will just move the user databases for now. Just a quick question though. One of the databases is 11MB but its log file is 3.8GB. What gives? I am a bit of a novice at SQL admin so I thought the logs would be like in exchange where they disappear after a backup. yet I did a full backup on Friday night and its still has a massive log file. Craig, Normally log file size should be less than datafile size, should not be more than 10% of your datafile size. in your case this could be because of the following your database recovery model is set to FULL and you have never taken transaction log backup. first off all check your dbs recovery model settings, if its set to full take a transaction log backup regards vinu > Thanks for the response, I think I will just move the user databases for > now. [quoted text clipped - 5 lines] > did > a full backup on Friday night and its still has a massive log file. Hi Craigh, I think that your database recovery mode was FULL. You can check this via right click the Database in SSMS, click Properties, and check Recovery model under Options. There are many ways you can do to truncate the transaction log file. I would like to recommend two ways here for your reference: Method 1 1. Run "BACKUP LOG database_name WITH TRUNCATE_ONLY" 2. Run "DBCC SHRINKFILE('virtual_log_file_name', 2)" to shrink the log file to 2MB. 3. Run "BACKUP DATABASE database_name TO DISK='backup_path'" to perform a full database backup. Method 2 1. Change your database recovery model to Simple; 2. Perform a full database backup with "BACKUP DATABASE database_name TO DISK='backup_path'". 3. If you want to keep your database to FULL recovery model, you can change your database recovery model to FULL. You may also want the following references: Overview of the Recovery Models http://technet.microsoft.com/en-us/library/ms189275.aspx How to stop the transaction log of a SQL Server database from growing unexpectedly http://support.microsoft.com/kb/873235/en-us Hope this helps. If you have any other questions or concerns, please feel free to let me know. Best regards, Charles Wang Microsoft Online Community Support =========================================================== Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. =========================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notifications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx. ============================================================  SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= Hi Charles, I have done a backup of the database by right clicking the database>>Tasks>>Back Up. I chose a full backup type but the log is still massive, in fact it is now 4.2GB I also have full backup that runs every night and that doesn't shrink any of the logs either. I am doing a full database backup with that, but I do have the option of backing up just the transactional logs. What am I doing wrong? Hi, Thank you for your response. It seemed that you directly performed a database backup from SSMS other than using my suggestions. Your steps seemed not changing the database recovery model to Simple. In this case, the transaction log will not be reduced. If you do not want to change the database recovery model, please use my Method 1. This is a common seen scenario and it will work in most of such cases. The KB article, http://support.microsoft.com/kb/873235/en-us, also explains the issue and solution detailedly. Best regards, Charles Wang Microsoft Online Community Support ========================================================= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. ========================================================= SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.