Deattach or take offline the DB to copy

Question

Post reply

Deattach or take offline the DB to copy

Anamika

SSCertifiable

Points: 6202
More actions
July 22, 2009 at 10:40 pm

#207213

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

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

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

>>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.

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 2

luckysql.kinda

SSCertifiable

Points: 7816

July 23, 2009 at 4:16 am

#1028830

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

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 3

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? 😎

Anamika SSCertifiable Points: 6202 More actions · Answer 4

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 5

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?

VaibhavChoubey@SQL Ten Centuries Points: 1033 More actions · Answer 6

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.

Anamika SSCertifiable Points: 6202 More actions · Answer 7

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

ps. One Orange Chip Points: 29422 More actions · Answer 8

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

Anamika SSCertifiable Points: 6202 More actions · Answer 9

thanks,

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

regards

viji

ps. One Orange Chip Points: 29422 More actions · Answer 10

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

Anamika SSCertifiable Points: 6202 More actions · Answer 11

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

Gingkoo SSChasing Mays Points: 644 More actions · Answer 12

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

ps. One Orange Chip Points: 29422 More actions · Answer 13

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

tushaar.kudalkar Old Hand Points: 306 More actions · Answer 14

Hi

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