Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Deattach or take offline the DB to copy Expand / Collapse
Author
Message
Posted Wednesday, July 22, 2009 10:40 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 6:37 PM
Points: 325, Visits: 961
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
Post #757972
Posted Wednesday, July 22, 2009 11:12 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
>>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.

Post #757977
Posted Thursday, July 23, 2009 4:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, Visits: 659
You want to copy .mdf and .ldf then make it offline
Post #758069
Posted Thursday, July 23, 2009 4:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, Visits: 659
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?
Post #758071
Posted Thursday, July 23, 2009 4:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 6:37 PM
Points: 325, Visits: 961
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.

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
Post #758093
Posted Thursday, July 23, 2009 5:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
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?
Post #758105
Posted Friday, July 24, 2009 3:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 30, 2012 11:15 PM
Points: 295, Visits: 109
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.
Post #758890
Posted Friday, July 24, 2009 7:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 6:37 PM
Points: 325, Visits: 961
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
Post #759055
Posted Friday, July 24, 2009 8:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 10, 2014 2:21 AM
Points: 2,223, Visits: 3,652
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
Post #759102
Posted Sunday, July 26, 2009 10:11 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 6:37 PM
Points: 325, Visits: 961
thanks,

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

regards
viji
Post #759875
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse