restore procedure - help

[divinyl]

can someone assist - i'm trying to write a Restore stored proc - as below. The concatenation is killing me - i can't get it write:

------

CREATE PROCEDURE RestoreDBFromDisk
@DBSource varchar(200),
@DataFile varchar(100),
@LogFile varchar(100),
@DBRestorePath varchar(200),
@LogRestorePath varchar(200)
AS
declare @cmd varchar(200)

SELECT @cmd = 'RESTORE DATABASE FROM DISK = ''' + @DBSource + ' MOVE ['+ @DataFile +'] TO
[' + @DBRestorePath + '] MOVE [' + @DataFile + '] TO ['+ @LogRestorePath']'

EXEC @cmd
-----

 

[DrSql]

Try this..

CREATE PROCEDURE RestoreDBFromDisk
@DBSource varchar(200),
@DataFile varchar(100),
@LogFile varchar(100),
@DBRestorePath varchar(200),
@LogRestorePath varchar(200)
AS
declare @cmd varchar(200)

SELECT @cmd = 'RESTORE DATABASE FROM DISK = ''' +
@DBSource +
' MOVE ['+
@DataFile
+'] TO [' +
@DBRestorePath +
'] MOVE [' +
@DataFile +
'] TO ['+
@LogRestorePath+
']'
Print @cmd
EXEC @cmd


Dr.Sql
Good Luck.

 

[divinyl]

thanks Doc!

But i still can't get this to work. The apostrophes are fine now - and i can create the procedure, but when i execute it, i get an error.

Here is my sproc:

-------------
CREATE PROCEDURE RestoreDBFromDisk
@DB sysname,
@DBSource varchar(200),
@DataFile varchar(100),
@DBRestorePath varchar(200),
@LogFile varchar(100),
@LogRestorePath varchar(200)
AS
declare @cmd varchar(200)

SELECT @cmd = 'RESTORE DATABASE [' + @DB + '] FROM DISK = ''' + @DBSource + ''' WITH RECOVERY,
MOVE '''+ @DataFile +''' TO ''' + @DBRestorePath + ''', MOVE ''' + @LogFile + '''
TO '''+ @LogRestorePath +''''

EXEC @cmd

-----------------

here is my execution:

-----------------

EXEC RestoreDbFromDisk Northwind, 'C:\SQLBackups\Northwind.BAK', 'Northwind',
'C:\SQLData\northwind.mdf', 'Northwind_Log', 'C:\SQLData\northwind.ldf'

------------------

Here is the result!

------------------

Server: Msg 203, Level 16, State 2, Procedure RestoreDBFromDisk, Line 28
The name 'RESTORE DATABASE [Northwind] FROM DISK = 'C:\SQLBackups\Northwind.BAK' WITH RECOVERY,
MOVE 'Northwind' TO 'C:\SQLData\northwind.mdf', MOVE 'Northwind_Log'
TO 'C:\SQLData\northwind.ldf'' is not a valid identifier.

-------------------

My poor head hurts!

 

[SQLBill]

Try running RESTORE FILELISTONLY (see BOL for the syntax) and see if you have the correct logical names (confirm that Northwind and Northwind_Log are correct).

Also, I don't know if it makes a difference.....but in my install of SQL Server Northwind is saved as northwnd.mdf and northwnd.ldf (no i in wind)

-SQLBill

Posting advice: FAQ481-4875

 

[divinyl]

I checked that already - i'm sure i've got the right file names....

The error is strange - "...not a valid identifier..."

Any more ideas??

Thx,
Div

 

[divinyl]

thanks

i tried filelistonly already and the logical file names are right. The physical paths where the data files are being movesd to can be called whatever you want though, because i tried running the statement outside of the sproc and it works:

---

RESTORE DATABASE [Northwind]
FROM DISK = 'C:\SQLBackups\northwind.bak' WITH RECOVERY,
MOVE 'northwind_log' TO 'C:\SqlData\northwnd.ldf',
MOVE 'northwind' TO 'c:\sqldata\northwnd_log.mdf'

---

i can't figure this out!

Any more ideas?

 

[divinyl]

Thanks guys - i got this working. I just took the restore statement out of the cmd variable - so looked like this:

create procedure RestoreDB
variables....
AS

Restore Database.....

That seemed to work. I notice that there are a few statements that don't work within variables - "alter database" was another one... i don't know the reasons for this. Would be grateful if someone could point me to some useful info about writing Sprocs!!

Div