Solved

How to properly move a sql database?

Posted on 2008-06-18
8
194 Views
Last Modified: 2012-08-13
I need to move a sql database to a new hard drive with more space for the db to grow. In SQL 2000 how can I do this?
0
Comment
Question by:MicroAutomation
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21813620
Take a backup of your database (full backup) along with running DBCC CHECKDB on the database.  AFterwards, detach your database and copy the files over to the other server.  Once you've copied them there attach them.  
0
 

Author Comment

by:MicroAutomation
ID: 21815040
WIll any currently setup sql jobs or replication be affected? In other words will I have to tweak all the scheduled jobs and such?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21815047
IF it is on the same server then you won't need to change any of that stuff, no.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:MicroAutomation
ID: 21815077
sweet thanks...i will give a try..
0
 

Author Comment

by:MicroAutomation
ID: 21821852
when trying to detach the database in order to copy the system shows:

attention: the database cannot be detached while it is being replicated
0
 

Author Comment

by:MicroAutomation
ID: 21821941
ok i got the disabling publushing wizard now and when trying to do this it says it could not diable publishing and distribtuion on <server name>

Error 2812: Could not find stored procedure 'master...xp_commandshell'
0
 

Author Comment

by:MicroAutomation
ID: 21822296
i was able to take the database offline and am now coppying it over
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 125 total points
ID: 21823094
Cool...I didn't realize that you were using replication or I'd have mentioned that.  Sorry about that.  
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question