Move local SQL Server DB to hosted SQL Server

Question

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 
FROM DISK="C:\Inetpub\vhosts\xxx.yy.com\xxx.bak" 
WITH RESTORE 

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?

Answer 1

First, you need to find out from the hosted provider the username/password of a user with either the sysadmin fixed role, or at minimum CREATE DATABASE rights (intermediate: dbcreator fixed role). This will let you run the restore, if SQL can access the .bak files.

Second, you need to change ALL the passwords for ALL the users on your hosted SQL Server. Long (25+ character) random passwords stored in KeePass or similar are ideal.

Next, you need to remove all domain users from the restored database; they're worthless now. You need to change your apps - whatever uses the database - to use the new SQL user and password rather than whatever was previously used.

Next, configure backups on the hosted site.

Next, RESTORE a backup from the hosted site - make sure it works!!!

Optionally, configure the hosted instance to force SSL connections; talk to your hosting provider about certificate generation and installation, then use sys.dm_exec_connections.encrypt to validate (it does take a service restart).

Optionally, figure out how to pull backups from the hosted site local, in case the hosted site has serious issues (possibly legal issues, for instance) and you lose everything there.