SQL Server Attach DB Problem. Error 5105

[rajeessh]

Hai All,
I want to write down a stored procedure in a way that duplicates the database.I follow the below mentioned step in
order
exec <storedprocname> <sourcedb>,<destdb>
1.)Get <sourcedb> Data File Name
2.)Drop <destdb> if already available.
3.)Detach <sourcedb> databases
4.)Create <destdb>
5.)Detach <destdb> databases
6.)Copy <sourcedb> data file to <destdb> Data file
7.)Attach SourceDb DestDb Files using sp_attach_db /
sp_single_attach_db

But when this is executed i get the Error 5105 : Device not found. The physcial device %s is missing where %s is the log file name for Source/Dest.I searched in net and got to use sp_attach_single_file but that also proven to be not working for me. I do not want to use BACKUP & RESTORE commands for this operation.Can anyone pl enlighten me in this area?
Thanx in advance for all of your tips and guidelines.
Cheers,
Srinivasan Rajesh

 

[tlbroadbent]

You can omit some steps.

1.)Get <sourcedb> Data File Name
2.)Drop <destdb> if exists
3.)Detach <sourcedb> databases
4.)Copy <sourcedb> data and log files to <destdb> Data and log files
5.)Attach DestDb Files using sp_attach_db

Alternate method:

1.)Get <sourcedb> Data File Name
2.)Drop <destdb> if already available.
3.)Detach <sourcedb> databases
4.)Copy <sourcedb> data file to <destdb> data file
5.)Attach DestDb data file using sp_single_attach_db

I can't tell why you get the error unless you lost the code you are using. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[rajeessh]

Hai ,
I have tried the following statement

usp_test @srcdb_name='Test',@destdb_name='TestDup'


and i get the messages
Successfully detached database 'Test'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 file(s) copied.

(1 row(s) affected)

Successfully attached database 'Test'.
Server: Msg 5105, Level 16, State 13, Line 1
Device activation error. The physical file name 'TestDup_data.mdf' may be incorrect.
success

Same i got before also. The SQL is as follows

CREATE PROCEDURE usp_test
@srcdb_name nvarchar(100),
@destdb_name nvarchar(100)
AS
declare
@src_dbfile_name nvarchar(4000),
@dest_dbfile_name nvarchar(4000),
@dest_logfile_name nvarchar(4000),
@create_new_db bit,
@stmt nvarchar(4000)

select @src_dbfile_name=[filename] from master.dbo.sysdatabases where [name]= @srcdb_name
set nocount on
if len(isnull(@src_dbfile_name,'')) = 0
begin
raiserror('Source DataBase not available',1,1)
return 0
end
select @dest_dbfile_name=[filename] from master.dbo.sysdatabases where [name]= @destdb_name
exec sims.usp_killprocess @db_name=@srcdb_name
exec sp_detach_db @srcdb_name,'true'
if len(isnull(@dest_dbfile_name,'')) > 0
begin
exec sims.usp_killprocess @db_name=@destdb_name
select @create_new_db = 0
set @stmt = ' drop database ' + @destdb_name
exec(@stmt)
end
select @dest_dbfile_name = @destdb_name + '_data.mdf'
select @stmt = ' copy ' + @src_dbfile_name + ' ' + @dest_dbfile_name
exec master..xp_cmdshell @stmt
exec sp_attach_single_file_db @srcdb_name,@src_dbfile_name
exec sp_attach_single_file_db @destdb_name,@dest_dbfile_name
set nocount off
print 'success'

If Possible Pl help.

 

[tlbroadbent]

If the destination DB doesn't exist, your code will result in a null value. The source file will be copied to the default directory rather than the correct SQL data diretory. sp_attach_single_file_db will attempt to attach using a default file name. That file will not exist.

Use the following statement to see where the file has been copied.

exec master.dbo.xp_cmdshell 'dir *.mdf'

Add logic to create the proper path and file name for the destination in case the file doesn't already exist. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.