Moving SQL Server between 2 Hosting Companies

I am moving a site from Host4Life to WinHost.  There were 2 high-level tasks:

Publish the website

Move the database

 

Publishing the website was easy.   All I had to do was to change the ftp address in Visual Studio 2010 Publish Website wizard

Moving the database was more complicated.  I tried 3 different ways using SQL Server Management Studio 2008 (SSMS):

1)      Backup/restore.  The backup failed on the default file location.  However, when I backed it  up not specifying the path, the backup worked.  Somewhere on Host4Life’s data servers is a file named Test.  In any event, the fact that I could not back up to my local file system in SSMS killed this idea.  I then tried to look for test but they have a new interface (tinyhost) and my SQL Server password did not work and there was no way I am interacting with those clowns at H4L’s help desk

2)      Copy Database.  I tried this next.  It failed when trying to copy from H4L to WinHost and H4L to my local file system.  I went through a couple of iterations with my local system – I made more progress once I enabled all of the SQL Server Services, but it ultimately failed and the log did not tell me why easily

3)      Scripting.  Ultimately, this is what worked for me.  I don’t know what version you can add scripting the data (shown here) but that made all the difference.  I scripted the tables, functions, views, and stored procs (fortunately, I don’t have any recursive dependencies in my objects) in order and the database came up as expected

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: