Need help with restoring the data using store procedure [resolved]

[szlamany]

I've not done .NET work - but I believe you need to break that parameter line into two lines. Processing one parameter at a time.

[ryanlum]

i tried already
earlier on
but it cannot read 2 seperate parameters..

[techgnome]

i tried already
earlier on
but it cannot read 2 seperate parameters..

Ummm.... that doesn't make sense..... sure it can, I did so just last night....

In fact....

myCommand.Parameters.Add(New SqlParameter("@Name", RestoreName)
myCommand.Parameters.Add(New SqlParameter("@Path", RestorePath))

Tg

[szlamany]

You know - we went around and around the other day (in your other thread) with what can and cannot be a variable in a SPROC.

Here is a BACKUP command that I use:

BACKUP DATABASE Funds 
   TO DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_copy.bak'

Notice that the c:\Program Files... stuff is in QUOTES. That means it's a string. That means you can replace it with a variable.

This will work - and we talked about it in your other thread:

Declare @Location varchar(100)
Set @Location='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_copy.bak'
Backup Database Funds to Disk = @Location

The only reason that works is that @Location is a variable, replacing a "string" in a command.

But - the first three words in the backup command - Backup Database Funds are not string. They are keywords.

I do not believe that you will be able to use a variable for the database name.

You should test that in QA before trying to make a jump all the way to production calls of a SPROC that won't work anyway. Otherwise you will find yourself going around in the same circles as the other day.

If you have a dozen different databases - you can create an IF/BEGIN/END block for each one in the SPROC - that will allow you to have a parameterized database name.

[techgnome]

I'm going to go waaaaay out on a limb here and suggest something..... it may bite me in the arse, and if I get it wrong szlamany, plz correct me. But....

Would this work?

DECLARE @DBName as sql_variant
SET @DBName = N'MyDB'

BACKUP DATABASE @DBName
TO DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_copy.bak'

??

I'm just asking. Personaly, I'd do my backup another way, especialy if they need to be dynamic like that.

Additionaly, could such a query be executed via dynamic SQL using EXEC?

Tg

[szlamany]

TG - I stand corrected. I wasn't 100% sure either way - just wanted to make sure the person tested in QA before launching into VB to call it.

All three of these backup statements worked:

backup database acctfiles to disk='c:\a1.bak'
go

declare @d1 varchar(100)
set @d1='c:\a2.bak'
backup database acctfiles to disk=@d1
go

declare @db varchar(100)
declare @d1 varchar(100)
set @db='acctfiles'
set @d1='c:\a3.bak'
backup database @db to disk=@d1
go

Message pane looked like this:

Processed 13824 pages for database 'acctfiles', file 'Acctfiles_Data' on file 1.
Processed 1 pages for database 'acctfiles', file 'Acctfiles_Log' on file 1.
BACKUP DATABASE successfully processed 13825 pages in 4.665 seconds (24.275 MB/sec).

Processed 13824 pages for database 'acctfiles', file 'Acctfiles_Data' on file 1.
Processed 1 pages for database 'acctfiles', file 'Acctfiles_Log' on file 1.
BACKUP DATABASE successfully processed 13825 pages in 6.633 seconds (17.073 MB/sec).

Processed 13824 pages for database 'acctfiles', file 'Acctfiles_Data' on file 1.
Processed 1 pages for database 'acctfiles', file 'Acctfiles_Log' on file 1.
BACKUP DATABASE successfully processed 13825 pages in 6.685 seconds (16.940 MB/sec).

Syntax wise that makes no sense to me. You can't have a variable for the "SELECT" keyword.

[szlamany]

But this doesn't work:

declare @what varchar(100)
declare @db varchar(100)
declare @d1 varchar(100)
set @what = 'database'
set @db='acctfiles'
set @d1='c:\a3.bak'
backup @what @db to disk=@d1
go

Which makes sense - since "database" is keyword - not in quotes. So the syntax allows the unquoted database name or a variable - that's odd to me.