Deattach or take offline the DB to copy

  • Hi,

    Do I have to Deattach or take offline the DB to copy the .mdf and .ldf files to other machine.

    I can't do a back up and restore as the server doesn't have enough space to do it.

    --

    Regards,

    Viji

  • >>Do I have to Deattach or take offline the DB to copy the .mdf and .ldf files to other machine.

    Yes. Without detach/offline files cannot be copied.

    >> I can't do a back up and restore as the server doesn't have enough space to do it.

    You can take backup to a network share. Or use a third party backup tool which does file compression.

  • You want to copy .mdf and .ldf then make it offline

  • I am not sure but I read somewhere that we can make some change in the database property and then .mdf and .ldf can be copied without taking DB offline. Anyone know about this? 😎

  • Hi All,

    Thanks for your suggestions,

    I've done with it. Steps I've followed are

    1. KILL the processes which is active / suspended / sleeping etc on the database 😀

    2. Take the Database OFFLINE

    3. Now copy the .mdf and .ldf to other machine.

    and my whole requiement was deleting the current .mdf and .ldf files

    restore new set of .mdf and .ldf files

    connect those to the same database. :w00t:

    after the above 3 steps, i

    4. deleted .mdf and .ldf files

    5. deleted database

    5. copied new .mdf and .ldf files into the location.

    6. created new database with the same name.

    and it works welllllllll

    Thanks,

    Regards,

    Viji

  • Glad to know that it works well.

    But, I have concerns 🙁

    Is it a production server? One should be carefull while issuing KILL command on a production server.

    Do you have proper backups, in case required?

  • Not a good way buddy, you should have tooka backup to a network location and then drop database and restore the new one , i dont see any profit in your approch.

  • hi,

    first itz not a production database

    2nd i hv proper back up

    3rd look into my 3rd point in the previous post.

    "copy the .ldf and .mdf to other machine"

    so @SQL i've done with your idea.

    i can't get into problems just like that..

    i know you people are there to educate me.

    thanks,

    regards

    viji

  • viji (7/23/2009)


    1. KILL the processes which is active / suspended / sleeping etc on the database 😀

    Instead of killing all active sessions, you can fire this simple statement to terminate all sessions and bring the db offline

    alter database mydb set offline with rollback immediate



    Pradeep Singh

  • thanks,

    is there any other command just to close all the connections??

    regards

    viji

  • viji (7/26/2009)


    thanks,

    is there any other command just to close all the connections??

    regards

    viji

    The command i wrote would be sufficient. the other way is to terminate all open connections using KILL.



    Pradeep Singh

  • sorry,

    let me be more clear

    i want to close all the connections but not to take the db offline.

    is there a command to do all this by one shot becoz KILL have to issue many times to kill all the processes........?

    thanks

    regards

    viji

  • ALTER DATABASE YourDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

    ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    Or this will do the job. Sorry about the cursor, an old script but you get the idea. sp_ on master will make available to all dbs, bad practice, not that you seem to mind :)..

    USE [master]

    go

    CREATE PROCEDURE sp_killprocess

    @dbname varchar(128)

    AS

    SET NOCOUNT ON

    SET QUOTED_IDENTIFIER OFF

    declare @kill_id int

    declare @query varchar(320)

    declare killprocess_cursor cursor for

    select a.spid from sysprocesses a join

    sysdatabases b on a.dbid=b.dbid where b.name=@dbname

    open killprocess_cursor

    fetch next from killprocess_cursor into @kill_id

    while(@@fetch_status =0)

    begin

    set @query = 'kill '+ convert(varchar,@kill_id)

    exec (@query)

    fetch next from killprocess_cursor into @kill_id

    end

    close killprocess_cursor

    deallocate killprocess_cursor

    GO

  • viji (7/27/2009)


    i want to close all the connections but not to take the db offline.

    The first two lines from the post of Victor are other two methods on how to disconnect all connections while your db will be still online. Single_user puts the database in single usesr mode(only one user can connect at a time).

    If u're putting the database in restricted_users, only members of sysadmin would be able to connect to the database.



    Pradeep Singh

  • Hi

    U need to stop the server services or else u will not be able to move ur .mdf and .ldf files

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply