I have a SQL Server database here that contains the info for the Gemini Issue Tracking software. It is running on a Windows XP system and I have SQL Server Management Studio (SSMS) on that system. I can successfully back it up to a xxx.bak file.
Now I want to bring that database up on an instance of SQL Server at a paid hosting site. I do not have shell access. I can log SSMS into the hosted database. That one uses SQL Server username/password foo\bar. My local database uses Windows Authentication HOST\USERNAME. I cannot replicate that Windows user onto the hosted database. I can transfer the xxx.bak backup to the hosted site through their Plesk control panel and I see it in my "files". I found out the physical path to it C:\Inetpub\vhosts\xxx.yy.com\, so I can construct a TRANSACT SQL statement
RESTORE DATABASE new_db_name
which forces it to wipe out the one they created with the info in the .bak.
Msg 3110, Level 14, State 1, Line 1 User does not have permission to RESTORE database 'new_db_name'.
Also the original backup is from a database with a different name. So I have a source database with Windows Login and one name, and I want to restore the data to another with a different SQL Server username/password and a different name (the first part of the name is fixed).
I am certain that SSMS is logged in under the SQL username/password as I can see the tables in the empty database. My SQL Server user has read/write permissions.
What issues am I going to face assuming I can figure out how to get the RESTORE DATABASE to work? What is the best way to do this?