• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

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

I want somethign like this
http://weblogs.asp.net/mschwarz/archive/2004/08/26/220735.aspx

But when i try to use it i get exceptions on this line i.e.


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

Returns me

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.

I'm sure this is a very common requirement so can anyone please tell me what im doing wrong or provide me with another alternative to this please

Thanks
Webbo
0
Webbo_1980
Asked:
Webbo_1980
  • 7
  • 6
  • 2
1 Solution
 
lomo74Commented:
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
)

Open 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)
)

Open in new window


and you should be on your way.
0
 
Webbo_1980Author Commented:
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?
0
 
Webbo_1980Author Commented:
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?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Webbo_1980Author Commented:
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.
0
 
Anthony PerkinsCommented:
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
0
 
Webbo_1980Author Commented:
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
0
 
Anthony PerkinsCommented:
Lose the ", FILE = 0".
0
 
lomo74Commented:
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

Open in new window

0
 
Webbo_1980Author Commented:
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?
0
 
lomo74Commented:
you have to refresh the view
right click on "databases" -> refresh
0
 
lomo74Commented:
select name from master..sysdatabases

do you see the newly created database in the list?
please give some feedback
0
 
Webbo_1980Author Commented:
I did press refresh and after running the query i'm not getting it in the list either?

very strange
0
 
lomo74Commented:
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?
0
 
Webbo_1980Author Commented:
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?
0
 
lomo74Commented:
yes, try to run the various steps manually and see what's going on.
let's start from beginning.

run this command:
BACKUP DATABSE TranslationsTest TO DISK = 'D:\deleteMe\TranslationsTest.dat' WITH INIT
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 7
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now