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

[ryanlum]

hey guyz.. i will bold the line that crashed.. it crashed when i was adding the parameters in the vb.net section
just in case i will post the stored procedure part alos

this is the vbcode i used

Dim myconnection As New SqlConnection(myConnectString)
 
        myconnection.ConnectionString = myConnectString
 
        myCommand = New SqlCommand("spRestoreDatabase", myconnection)
 
        myconnection.Open()
        myCommand.CommandType = CommandType.StoredProcedure
 
       [b] myCommand.Parameters.Add(New SqlParameter("@Name", RestoreName, "@Path", RestorePath))[/b]
 
        myCommand.ExecuteNonQuery()
 
        MsgBox("Data Restored")
        myconnection.Close()

This is the stored procedure that i used

Create Procedure spRestoreDatabase
@Name as VARCHAR(1000),
@Path as VARCHAR(1000)
 
As
 
Restore Database @Name
From Disk = @Path
GO

[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.

[szlamany]

And on top of that - this doesn't work:

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

[techgnome]

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.

So, as long as it isn't a SQL keyword, looks like almost anything goes..... sometimes.... because you couldn't pass in a name of a column and then have this:

SELECT @ColName from @TableName



Would that be an inconsistancy in the t-SQL lang, the ANSI-SQL spec, or fault of MS in how QA was built?



Tg

[szlamany]

Would that be an inconsistancy in the t-SQL lang, the ANSI-SQL spec, or fault of MS in how QA was built?



Tg

I think it boils down to the fact that USE DBNAME is unquoted - that seems to support the standard that DBNAME, TABLENAME and COLUMNNAME are all unquoted - and variables cannot be used in place of them.

But with the BACKUP command - that's really more the MS T-SQL syntax - they choose to follow the unquoted nature, as in USE DBNAME, but in reality it's a string - since a variable can take it's place.

Hard to explain this to someone new to the syntax - that's a shame!

[techgnome]

I think it boils down to the fact that USE DBNAME is unquoted - that seems to support the standard that DBNAME, TABLENAME and COLUMNNAME are all unquoted - and variables cannot be used in place of them.

But with the BACKUP command - that's really more the MS T-SQL syntax - they choose to follow the unquoted nature, as in USE DBNAME, but in reality it's a string - since a variable can take it's place.

Hard to explain this to someone new to the syntax - that's a shame!

And equaly hard to someone who isn't so new to it either. But that's the "joys" of programming.

Tg

[ryanlum]

i have changed the stored procedure to

now it crashes on myconnection.executenonquery.

the error msg that was provided is "exclusive access could not be obtained becuase the database is in use"

CREATE Procedure spRestoreDatabase
@Path  VARCHAR(100)
AS
Restore Database Test From Disk = @Path
GO

[ryanlum]

any ideas anyone?

[szlamany]

What database are you connecting to in your connection string - since you are restoring a database - it obviously cannot be that database - right?

Maybe connecting to MASTER would work? At least having some heavy access rights would required.

[ryanlum]

i am planning to restore the database test... it is the one that i am using.. can it be done?

[szlamany]

i am planning to restore the database test... it is the one that i am using.. can it be done?

How would you imagine that it's possible to "connect" and authenticate to a database that is not yet restored?

How are you connecting? Don't you specify the database in the connection string?

[ryanlum]

this is the connection string that i am using..

Dim myConnectString As String = "Data source=(local);Integrated security = SSPI;database = test"

[szlamany]

this is the connection string that i am using..

Dim myConnectString As String = "Data source=(local);Integrated security = SSPI;database = test"

If you are planning on restoring a database named TEST, then you cannot connect to a database TEST.

If you are planning on restoring a DB - it should not exist before you connect.

Try making the database=MASTER...

Although, that brings up security and rights issues that you are going to have to think about.

[ryanlum]

that worked.. thx alot.. but inorder to solve that problem.. should i create another special database instead of connecting it to the master and grant sufficient rights for it so that i can backup the database with it?

[szlamany]

that worked.. thx alot.. but inorder to solve that problem.. should i create another special database instead of connecting it to the master and grant sufficient rights for it so that i can backup the database with it?

Depends really on who has access to the SPROC. If it's a small group of DBA type people - then MASTER access is fine. Otherwise - creating a special DB for this purpose makes sense also.

[ryanlum]

whats SPROC?

erm... i am allowing the users in the server app to just backup and restore the database. what would be the most efficient method?

[szlamany]

whats SPROC?

erm... i am allowing the users in the server app to just backup and restore the database. what would be the most efficient method?

SPROC is simply my abbreviation for STORED PROCEDURE.

You are going down a unique path - allowing users in the server app to backup and restore a DB.

I feel that your idea to create a DB with the rights to do this task is better than giving MASTER access to the users.

You must keep in mind that the level of database admin access you are giving these users is extreme. I'm not sure what that means overall - but it stands out as a concern to me.

Good luck.