I am ultimately trying to backup my database from a deployed site to my PC's disk, because tools such as MyLittleBackup, which is provided by my hosting company, is done under http and not https.
But initally I am trying to get a backup working on my local ASP.Net development server. I am getting the following error message;
"Could not locate entry in sysdatabases for database 'MyDatabase'. No entry found with that name. Make sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally."
My stored procedure is;
ALTER PROCEDUREdbo.DBBK1
AS
BEGIN
BACKUP DATABASE MyDatabase
TO DISK='C:\Documents and Settings\BBBN\My Documents\MyDatabase.bak''
WITHFORMAT;
END
RETURN
My Stored Procedure is called by the following script:
cnn1.ConnectionString = "Data Source=.\sqlexpress;" & "Integrated Security=SSPI;" & "User Instance=True;" & "AttachDBFileName=" & My.Computer.FileSystem.CombinePath( _
Server.MapPath("/MyWebSite/App_Data"), "MyDatabase.mdf")
cmd3.Connection = cnn1
cmd3.CommandText = "DBBK1"
cmd3.CommandType = Data.CommandType.StoredProcedure
cnn1.Open()
Try
cmd3.ExecuteNonQuery()
Catch ex As Exception
Message1.Text = (ex.Message)
Finally
cnn1.Close()
End Try
I'm not sure what I need to consider to progress this. Any help would be appreciated.
Chris
Try running
USE MASTER
SELECT name, database_id FROM sys.databases
This will list all databases on your server.
|||Hi, thanks for you post
If I add USE MASTER to my stored procedure, I get the error, a USE database statement is not allowed in a procedure, function or trigger.
I did previously use a similar SELECT statement and the database was listed in my output view. I can't get that list back now though for some reason.
Obviously my database is there because it's sourced by other routines within the site so I still unsure how to proceed
Chris
|||
After another 24 hours, I am still unable to resolve this. If anyone can point me in the right direction, that would be appreciated.
I have changed from using a stored procedure and my code is as follows in my deployed site.
cnn1.ConnectionString = "Data Source=host's server name;Initial Catalog=Mydatabase;User ID=BlahBlah;Password=BlahBlah"
cmd3.Connection = cnn1
cmd3.CommandType = Data.CommandType.Text
cmd3.CommandText = "USE MASTER;" & "BACKUP DATABASE MyDatabase TO DISK = 'C:\Program Files\Microft SQL Server\MSSQL.1\MSSQL\Backup\\MyDatabase.bak'" & "WITH FORMAT;"
cnn1.Open()
cmd3.ExecuteNonQuery()
cnn1.Close()
The error message is as follows:
Server Error in '/' Application.
------------------------
BACKUP DATABASE permission denied in database 'MyDatabase'.
BACKUP DATABASE is terminating abnormally.
Changed database context to 'master'.
Chris
|||
What permissions does User ID=BlahBlah have on the database you are trying to back up? Does that user have db_backupoperator on that database?
|||Hi
I'm not sure, User ID and password are simply the SQL server authentication login and password. I haven't set up any specific permissions.
Chris
|||
Open your SQL Server Management Studio (or even Enterpise Manager).
Click on your database (MyDatabase) node, then Security node, then Users, then right click on the user you are using (e.g. BlahBlah) and select Properties from the list.
Now, a new windows is opened and you will find some checkboxes for Roles, check the box for db_backupoperator.
Tip:
I suggest you to have one database user for backup with db_backupoperator role and use it always from backup/restore operations.
Good luck.
|||I've done something wrong. I checked the box for db_backupoperator for owned schemas instead of roles. Now I appear to have lost all my tables and I can't un-check the box. The run error I'm getting is;
Server Error in '/' Application.
------------------------
The EXECUTE permission was denied on the object 'aspnet_Membership_GetPasswordWithFormat', database 'MyDatabase', schema 'dbo'.
Chris
|||
I'm back to where I was before my previous post. I have checked the db_backupoperator box in roles but I still get the same error which is ;
Server Error in '/' Application.
BACKUP DATABASE permission denied in database 'MyDatabase'.
BACKUP DATABASE is terminating abnormally.
Changed database context to 'master'.
My user ID and password were set up through helm, so is the permission denied a hosting issue. My connectionstring works correctly throughout the site and I believe my Backup syntax is correct.
My Backup syntax states "Use master" yet I still get the error "Changed database context to 'master'
Any suggestions would again be appreciated.
Chris
|||
ChrisCB:
in '/' Application.
------------------------The EXECUTE permission was denied on the object 'aspnet_Membership_GetPasswordWithFormat', database 'MyDatabase', schema 'dbo'.
Chris
Open your SQL Server Management Studio (or even Enterpise Manager).
Click on your database (MyDatabase) node, then Security node, then Users, then right click on the user you are using (e.g. BlahBlah) and select Properties from the list.
Now, a new windows is opened select "Securables" from the left side of the window, click on Add button, then "All Objects of Type", then "Stored Procedues", then OK.
Now you suppose to get a list of stored procedures in the database, click on 'aspnet_Membership_GetPasswordWithFormat' stored procedure and check the "Grant" box for it.
Now that database user can execute the 'aspnet_Membership_GetPasswordWithFormat' stored procedure and the message appeared should go.
Good luck.
|||
I'm back to where I was before my previous post. I have checked the db_backupoperator box in roles but I still get the same error which is ;
Server Error in '/' Application.
BACKUP DATABASE permission denied in database 'MyDatabase'.
BACKUP DATABASE is terminating abnormally.
Changed database context to 'master'.My user ID and password were set up through helm, so is the permission denied a hosting issue. My connectionstring works correctly throughout the site and I believe my Backup syntax is correct.
My Backup syntax states "Use master" yet I still get the error "Changed database context to 'master'
Any suggestions would again be appreciated.
Chris
Yes, I believe it is from ythe hosting company.
Check this link:http://support.microsoft.com/kb/290787
Good luck.
|||Hi
I've had a reply from my hosting company and the problem is they say;
"You won't be able to create your own script to backup databases in a secure shared hosting environment. This is the reason we provide MyLittleBackup."
This being so, it brings me back to my first post on this thread and my initial problem. I have a secure shared hosting environment where customer information can be collected securely over SSL. If I were to have a database failure, the only backup facility appears to be MyLittleBackup which passes the backup and restore data un-secured over the internet, which defeats the purpose of having SSL in the first place. (Its like locking the front door, but not the back door).
I would really appreciate anyone's comments as to be best way to procced from here.
Chris
|||
Hi
I've had a reply from my hosting company and the problem is they say;
"You won't be able to create your own script to backup databases in a secure shared hosting environment. This is the reason we provide MyLittleBackup."
This being so, it brings me back to my first post on this thread and my initial problem. I have a secure shared hosting environment where customer information can be collected securely over SSL. If I were to have a database failure, the only backup facility appears to be MyLittleBackup which passes the backup and restore data un-secured over the internet, which defeats the purpose of having SSL in the first place. (Its like locking the front door, but not the back door).
I would really appreciate anyone's comments as to be best way to procced from here.
Chris
I suggest you to ask the hosting company about this security isse, why they offer it in this way?
If their answer is okay for you conntinue with them, otherwise go for another hosting company.
It is really strange!
Good luck
|||>I would really appreciate anyone's comments as to be best way to proceed from here.
Are you able to create a extra site to run a web service and run this using SSL? If so you can expose the database via a series of methods one per table. Provided your home system has a fixed external IP address, you can the lock the web service site to response only to that IP address.
Sorry for the delay in replying.
CS4Ever, I think at the moment I will have to rely on backups provided by the hosting company on their servers.
Tatworth, Using a web service in the way you descibe sounds very interesting. I haven't used them before so I will look at it at some point but it seems quite a complicated task at this late stage of my sites development.
Many thanks for your help and time.
Chris
.
No comments:
Post a Comment