Problem with sp_detache - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with sp_detache

Author

Topic

happyboy
Starting Member

21 Posts

Posted - 2009-05-20 : 06:34:21

Hi all,

I would like to detatch database to copy mdf and ldf file for back up.
I write this script to set single mode , and clear all connection, after that I will detatch, but I get the message .

"Cannot detach the database because it is currently in use."

Here my script

CREATE PROCEDURE sp_BackUpDatabase
@Source nvarchar(1000),
@Destination nvarchar(1000),
@DatabaseName nvarchar(1000),
@DataFile nvarchar(1000),
@LogFile nvarchar(1000)
WITH ENCRYPTION
AS
BEGIN

Declare @SourceServer nvarchar(1000)
Declare @DestinationServer nvarchar(1000)
Declare @f1 nvarchar (1000)
Declare @f2 nvarchar (1000)

Set @SourceServer = @Source
Set @DestinationServer = @Destination

If db_id(@DatabaseName) > 0 and db_id(@DatabaseName) is not null
Begin
-- Set exclusive
Declare @cmdSingleUser nvarchar(1000)
Set @cmdSingleUser = 'ALTER DATABASE '+ @DatabaseName + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
exec (@cmdSingleUser)

-- Kill all connections
DECLARE @SPId int

DECLARE my_cursor CURSOR FOR
SELECT SPId FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @SPId

WHILE @@FETCH_STATUS = 0
BEGIN
exec ('KILL ' + @SPId)
FETCH NEXT FROM my_cursor INTO @SPId
END

CLOSE my_cursor
DEALLOCATE my_cursor

-- Detach databse
exec sp_detach_db @DatabaseName,false

-- Copy data file and logfile
Declare @cmdCopy nvarchar(1000)
Set @cmdCopy = 'Copy ' + @SourceServer + '*.* ' + @DestinationServer
exec master..xp_cmdshell @cmdCopy
End

-- Attach database
Set @f1=@SourceServer + @DataFile
Set @f2=@SourceServer + @LogFile

exec sp_attach_db @dbname = @DatabaseName, @filename1 = @f1, @filename2 = @f2

-- Set Multi user mode
Declare @cmdAllUser nvarchar(1000)
Set @cmdAllUser = 'ALTER DATABASE ' + @DatabaseName + ' SET MULTI_USER'
exec (@cmdAllUser)
END

P/s How can i detatch database from remote server

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-20 : 06:59:13

you need to take database offline first before detach

KH
[spoiler]Time is always against us[/spoiler]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-20 : 07:37:18

If your only purpose is to make a backup, use the regular BACKUP command. It maintains transactional integrity and the database can stay online and available.

happyboy
Starting Member

21 Posts

Posted - 2009-05-20 : 22:22:48

thanks for reply
the reason why I dont use backup of sql because it just save backup on local disk. I like to save backup automaticcally on another server (on ly i map drive on remote computer to local running sql server, i cannot save to that drive!!!)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-20 : 23:59:15

If you are running SQL Server under the LocalSystem account, then yes, you can only back up to a local drive. If you change the service account to a domain account, then you can back up directly to a UNC path. However, it's still better to back up to a local drive, then copy the backup file to a remote network drive. This prevents the backup from being affected by network problems.

Either of these are still preferable to detaching and copying the file just to make a backup, for the reasons I stated earlier.

happyboy
Starting Member

21 Posts

Posted - 2009-05-21 : 01:25:57

I have run sql on domain account, but I dont see where to save to drive not on local .

Please help me

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-21 : 07:10:53

BACKUP DATABASE myDB TO DISK='\\myServer\myPath\myDB.bak' WITH INIT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources