Database detach problem

Hi all,

I'm trying to detach a database and after archive it for deliver to other server.

The detach wents fine and the database is removed from the database tree list in Management Studio. The problem is that the mdf file is being locked by some process (maybe SQL) and I can't imagine why. Here is the code for this operation:

USE master;

GO

ALTER DATABASE IMS_MCK_MIS SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE IMS_MCK_MIS SET EMERGENCY

GO

DECLARE @.strDate NVARCHAR(8)

DECLARE @.strCmd NVARCHAR(255)

SELECT @.strDate = CONVERT(nvarchar(8), GETDATE(),112)

EXEC sp_detach_db 'IMS_MCK_MIS'

SELECT @.strCmd = 'wzzip -ex -m C:\Delivery\archive_' + @.strDate + '.zip E:\DB\SQL\IMS_MCK_MIS.mdf'

exec master..xp_cmdshell @.strCmd

Anyone have any idea how to quickly free the mdf file?

Thanks and regards

CG

Setting emergency mode is not same as detach. If you simply want to copy the database files then set the database state to OFFLINE, copy the files and then make it ONLINE. This gives the benefit of retaining the database definition during the copy process. You can do detach and attach but that will remove the database definition from instance upon detach. Emergency mode is meant for disaster recovery scenarios.|||

The emergency state was a desasperated code line. Even putting the database offline, it stills being in use by SQL Server. The idea is to move that DB to an external server in client's house.

This code works fine in SQL Server 2000 but in 2005 even after being removed from available databases in Studio, the file is being in use by operating system.

Do you have any idea, besides a backup operation?

Regards,

|||

What error are you getting from the OS? Were you able to set the database offline. I don't use SSMS (GUI part) that much so I don't know if that does something behind the scenes. But below script works fine for me:

use master
go
create database det_test
go
use det_test
go
select * from sys.database_files
go
use master
go
alter database det_test set offline
go
-- do copy here:

-- cleanup:
drop database det_test
go

Once the database was offline, I was able to copy the files to a different folder/location successfully. So can you post a repro script like this? And also any error message that you are getting now.

|||

There's a GUI part of SSMS? :)

Just a wild guess. Any kind of virus scanning going on? Some other process that might have the file busy? I made it work on my laptop server using the following code using detach and with offline too, even with the detach and setting it offline in the same batch:

USE master;
GO
begin try
alter database testZip set single_user with rollback immediate
drop database testZip
end try
begin catch
end catch
go

create database testZip
ON
( NAME = testZip,
FILENAME = 'c:\testZip.mdf',
SIZE = 2mb )
LOG ON
( NAME = testZip_log,
FILENAME = 'c:\testZip.ldf',
SIZE = 1MB
)
go

ALTER DATABASE testZip SET AUTO_UPDATE_STATISTICS ON
GO

--use detach

exec sp_detach_db 'testZip'

DECLARE @.strDate NVARCHAR(8)
DECLARE @.strCmd NVARCHAR(255)
SELECT @.strDate = CONVERT(nvarchar(8), GETDATE(),112)

SELECT @.strCmd = '"C:\Program Files\Self Installed Files\info-zip\zip" C:\archive_' + @.strDate + '.zip c:\testZip.mdf'

exec master..xp_cmdshell @.strCmd

exec sp_attach_db 'testZip','c:\testZip.mdf'
go

--now try offline mode

ALTER DATABASE testZIp SET OFFLINE

DECLARE @.strDate NVARCHAR(8)
DECLARE @.strCmd NVARCHAR(255)
SELECT @.strDate = CONVERT(nvarchar(8), GETDATE(),112)

SELECT @.strCmd = '"C:\Program Files\Self Installed Files\info-zip\zip" C:\archive_' + @.strDate + '.zip c:\testZip.mdf'

exec master..xp_cmdshell @.strCmd

ALTER DATABASE testZIp SET ONLINE
go

|||

Hi guys,
Thanks for the samples. I've tried both and here are the results:
1. the detach method - Error message

output

WinZip(R) Command Line Support Add-On Version 2.0 (Build 7041)
Copyright (c) WinZip International LLC 1991-2005 - All Rights Reserved
Searching... ... ..
Adding testZip.mdf
Warning: could not open for reading: c:\testZip.mdf. .
creating Zip file C:\archive_20061104.zip
NULL
(7 row(s) affected)

The mdf is still being in use.
2. The offline method works fine, the mdf is released.

output

WinZip(R) Command Line Support Add-On Version 2.0 (Build 7041)
Copyright (c) WinZip International LLC 1991-2005 - All Rights Reserved
Searching...
creating Zip file C:\archive_20061104.zip
Moving Files...
NULL

(6 row(s) affected)

No matter how strange this behaviour is, I'll use the second choice.
Thanks for your help.
See you.