Can anyone provide me with a stored procedure that will copy a database please

as you can see here, the recordset returned by RESTORE FILELISTONLY has changed.

replace these lines:

CREATE TABLE #restoretemp
(
 LogicalName varchar(500),
 PhysicalName varchar(500),
 type varchar(10),
 FilegroupName varchar(200),
 size int,
 maxsize bigint
)

Select allOpen in new window

with these ones:

CREATE TABLE #restoretemp
(
 LogicalName nvarchar(128),
 PhysicalName nvarchar(260),
 Type char(1),
 FilegroupName nvarchar(128),
 Size numeric(20,0),
 MaxSize numeric(20,0),
 FileId bigint,
 CreateLSN numeric(25,0),
 DropLSN numeric(25,0),
 UniqueId uniqueidentifier,
 ReadOnlyLSN numeric(25,0),
 ReadWriteLSN numeric(25,0),
 BackupSizeInBytes bigint,
 SourceBlockSize int,
 FileGroupID int,
 LogGroupGUID uniqueidentifier,
 DifferentialBaseLSN numeric(25,0),
 DifferentialBaseGUID uniqueidentifier,
 IsReadOnly bit,
 IsPresent bit,
 TDEThumbprint varbinary(32)
)

Select allOpen in new window

and you should be on your way.

I've tried your suggestion and same problem im afriad i.e.

(1 row(s) affected)
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
 
Any ideas?

I Took out TDEThumbprint varbinary(32) and its now working but, its still not creating a new copy the db in question?

Can i confirm does this work for you?

This is the error its giving me now .....

Running this ......
RESTORE DATABASE D:\deleteMe\TranslationsTest_01 FROM DISK = 'D:\deleteMe\TranslationsTest.dat' WITH MOVE 'TranslationsTest' TO 'D:\deleteMe\TranslationsTest_01.mdf', MOVE 'TranslationsTest_log' TO 'D:\deleteMe\TranslationsTest_01.ldf', FILE = 0

Gives me this....

Incorrect syntax near 'D:'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

This is not right:
RESTORE DATABASE D:\deleteMe\TranslationsTest_01 FROM DISK = 'D:\deleteMe\TranslationsTest.dat' WITH MOVE 'TranslationsTest' TO 'D:\deleteMe\TranslationsTest_01.mdf', MOVE 'TranslationsTest_log' TO 'D:\deleteMe\TranslationsTest_01.ldf', FILE = 0

The database name is not a path.  It probably should be something like this:
RESTORE DATABASE TranslationsTest_01 FROM DISK = 'D:\deleteMe\TranslationsTest.dat' WITH MOVE 'TranslationsTest' TO 'D:\deleteMe\TranslationsTest_01.mdf', MOVE 'TranslationsTest_log' TO 'D:\deleteMe\TranslationsTest_01.ldf', FILE = 0

now i get

The value '0' is not within range for the FILE parameter.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

from

RESTORE DATABASE TranslationsTest_01 FROM DISK = 'D:\deleteMe\TranslationsTest.dat' WITH MOVE 'TranslationsTest' TO 'D:\deleteMe\TranslationsTest_01.mdf', MOVE 'TranslationsTest_log' TO 'D:\deleteMe\TranslationsTest_01.ldf', FILE = 0

Lose the ", FILE = 0".

Yes, it's working for me.
Ok, this should be your final script:
 

USE master
GO

-- the original database (use 'SET @DB = NULL' to disable backup)
DECLARE @DB varchar(200)
SET @DB = 'TranslationsTest'

-- the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'D:\deleteMe\TranslationsTest.dat'

-- the new database name
DECLARE @TestDB varchar(200)
SET @TestDB = 'TranslationsTest_01'

-- the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'D:\deleteMe\TranslationsTest_01'


-- ****************************************************************
--                    no change below this line
-- ****************************************************************


DECLARE @query varchar(2000)

DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFile + '.mdf'

DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreFile + '.ldf'

IF @DB IS NOT NULL
BEGIN
    SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
    EXEC (@query)
END

-- RESTORE FILELISTONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE HEADERONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE LABELONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE VERIFYONLY FROM DISK = 'C:\temp\backup.dat'

IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
BEGIN
    SET @query = 'DROP DATABASE ' + @TestDB
    EXEC (@query)
END

RESTORE HEADERONLY FROM DISK = @BackupFile
DECLARE @File int
SET @File = @@ROWCOUNT

DECLARE @Data varchar(500)
DECLARE @Log varchar(500)

SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')

CREATE TABLE #restoretemp
(
 LogicalName nvarchar(128),
 PhysicalName nvarchar(260),
 Type char(1),
 FilegroupName nvarchar(128),
 Size numeric(20,0),
 MaxSize numeric(20,0),
 FileId bigint,
 CreateLSN numeric(25,0),
 DropLSN numeric(25,0),
 UniqueId uniqueidentifier,
 ReadOnlyLSN numeric(25,0),
 ReadWriteLSN numeric(25,0),
 BackupSizeInBytes bigint,
 SourceBlockSize int,
 FileGroupID int,
 LogGroupGUID uniqueidentifier,
 DifferentialBaseLSN numeric(25,0),
 DifferentialBaseGUID uniqueidentifier,
 IsReadOnly bit,
 IsPresent bit
)
INSERT #restoretemp EXEC (@query)

SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'

PRINT @Data
PRINT @Log

TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp

IF @File > 0
BEGIN
    SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') + 
        ' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +
        QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)
    EXEC (@query)
END
GO

Select allOpen in new window

Thanks and sorry for slight delay i was off work..

But while both now work i.e. no exceptions

The new db isnt getting created when i goto the databases and look for 'TranslationsTest_01'

Any ideas?

you have to refresh the view
right click on "databases" -> refresh

select name from master..sysdatabases

do you see the newly created database in the list?
please give some feedback

I did press refresh and after running the query i'm not getting it in the list either?

very strange

which version of SQL server are you running?
regarding the directory where you're trying to put (1) the source DB's backup file and (2) the destination DB's MDF and LDF files (in our example: D:\deleteMe):
- does this directory exist?
- does the "network service" user (assuming you're running SQL Server under that account) have read/write permissions on it?
does the source DB (TranslationsTest) exist?

I'm using sql 2005 express and yes to all other questions

Not much help i know, but is there any error handling i can add to see if such an error occurs?