locked
I need to move my sql server 2005 database to another drive RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I need to move my sql server 2005 database to another drive, i try to get my database offline and after that i will detach the database, but my database taking too much time to goes offline last 2 hour process still in progress. What i will do ? is their any other way for take offline database?
    • Changed type libra_ali786 Monday, November 19, 2012 11:24 AM
    Monday, November 19, 2012 9:59 AM

Answers

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    try this

    alter database dbname set offline with rollback immediate

    or

    alter database b set offline with no_wait

    you can use any of the above command ,this command immediately sets you database into offline mode

    Ramesh Babu Vavilla MCTS,MSBI

    • Marked as answer by libra_ali786 Tuesday, November 20, 2012 7:52 AM
    Monday, November 19, 2012 1:34 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Make sure there are no users in the database.

    You can use the KILL command to remove an inactive spid connected to the database.

    Move database to different drive:

    http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/5c0e5ba5-1e1a-4c38-ac92-3e5c50ac6c7b

    http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/79df8a55-d318-4c53-9c03-a9c2ede6bc81

    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    • Marked as answer by libra_ali786 Tuesday, November 20, 2012 7:51 AM
    Monday, November 19, 2012 3:52 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    1.make your database off line.

    2.The detach the DB, Copy LDF and MDF file to New Drive and attach the LDF and MDF file from New Drive and Remove the LDF and MDF from old drive

    or

    You can backup and restore your database; by using the WITH MOVE options you can relocate files 

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, November 20, 2012 6:09 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    you can also

    1. Alter database and provide the new location.
    2. Take database offline (with rollback immediate if you can't connection users)
    3. Move the files
    4. Bring database online.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Tuesday, November 20, 2012 6:19 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    The simple way is to Backup/restore the activity so while restoring do specify with Move option..incase if it is plan to move different drives.

    how ever as said all above you can also do the detach/Attach or simply update the catalog & take the DB offline, Move the files & Bring db to online.

    but as you said below-

    i try to get my database offline and after that i will detach the database, but my database taking too much time to goes offline last 2 hour process still in progress. What i will do ? is their any other way for take offline database?

    >>Ask any users if they have connected to database to come out from that -so Always this is fine approach because you never know if any open transactions or any other user activity running that can cause DB issue or you need to wait for till the trsanction rollback finishes when you have used it ROllback options while dropping connections forcefully.

    Incase if they are ok to kill then you can do as below-

    USE master
    GO
    ALTER DATABASE yourdbname
    SET OFFLINE WITH ROLLBACK IMMEDIATE
    GO

    then you can detach the Database, but more ever did you checked what was it was waiting for with the helpw sys.dm_exec_Requests?

    Also try to run CHECKDB to ensure that DB is in consistency state once all the activity completed from your side.

    Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.


    Tuesday, November 20, 2012 7:00 AM