How to Copy a MSSQL Server database to a New Webhost without RESTORE

This article explains how to copy a database from one server to another when Backup/Restore is not an option.

• This will work as long as you have SQL Server Management Studio access to the source and destination databases.


• This is very slow, and may take many hours to run on a big database.

1. First, open the database server you want to copy in SSMS.


2. Next, Right-click the database, Tasks -> Generate Scripts.
    


3. In my case I want all the scripts to completely recreate my database, so here are the steps.


4. Next
   


5. Script entire database and all database objects


6. Save to new query window (file is OK too).
   


7. Click Advanced (by default it will write a script to create all objects, but not copy data).
   
   Make sure to select Schema and data.
   


8. Review all the other settings to make sure you get the stuff you want on the destination database.  Click OK, then Next.


9. Review and Next (or previous if you need to change something.)


10. You'll get a checklist screen and then the script is done.  It should pop up in SSMS.

To restore the database...

1. Log SSMS into the destination database.


2. Back up what's there if there is any chance you'll need it.
   


3. In your script, if you need to rename the database to satisfy the new hosting company, do a global replace in the SQL script.  Make sure the FileNames reflect the destination database name not the source database
   


4. Copy the entire script to the clipboard.


5. right click the destination database and do a New Query (if the database hasn't been created yet, you can click the server name).
   


6. Paste the script into the new query editor.


7. If the database already exists, you may need to delete the CREATE statement and all the ALTER DATABASE statements, down to the [USE database] command.


8. Look in the scrollbar for possible problems...  Then fix them.
   


9. Run the script and see what happens.  Get coffee.  It's gonna be a while.  Call your Dad.  He misses you.

You're done.  Just in case you don't have all the same Generate Scripts options, this demo was from a SQLExpress2016 database, using all this stuff...



Microsoft SQL Server Management Studio                        14.0.17199.0

Microsoft Analysis Services Client Tools                        14.0.1008.227

Microsoft Data Access Components (MDAC)                        10.0.16299.15

Microsoft MSXML                        3.0 4.0 6.0

Microsoft Internet Explorer                        9.11.16299.0

Microsoft .NET Framework                        4.0.30319.42000

Operating System                        6.3.16299