Alternative Method for Copying Databases in SQL Server 2005 Management Studio

{ January 25, 2008 @ 2:36 am } · { Uncategorized }

How’s this for an awesome post… nevermind global warming, subprime mortgages or national healthcare, this one tops them all: How to easily port that database that you’ve been working on remotely on a hosted web server to your own machine using SQL Server 2005 Management Studio.

Why on earth am I writing this? Because I was trying to do a “Copy Database” in SQL Server 2005 Management Studio for the past hour, and kept getting permissions errors from my web host when using SMO. So in case I forget (likely), I wanted to write some steps for myself. It’s late and I’m too lazy to paste pictures. Just use your imagination on this one:

HOW TO DO IT:

  1. Open SQL Server 2005 Management Studio, and Connect to the database that you will transferring from
  2. Create a new database to copy to
  3. Right-Click on the database you will be copying, select “Tasks” -> “Export Data”
  4. Choose all tables and follow the prompts to Export the data and tables from the original database to the newly created db
  5. After the export finishes (will take a few minutes, most likely) check to make sure all tables, views, stored procs etc have been installed
  6. Right-click the original copy database, select “Tasks”->”Generate Scripts”
  7. Follow the prompts, choose to generate the script into a query for ease
  8. Copy the generated script to your clipboard
  9. Select the new database, open a new query, and paste the generated script into this query.  The query will be run on the new database.
  10. Run the new script.  There will be errors, because the script will not be able to replace existing tables.  We are only running the script to install objects that don’t hold data, such as views and stored procedures.
  11. Check to ensure all the original tables, views, stored procs etc are now in the new database
  12. Close all scripts, don’t save unless you really want to
  13. Feel good about yourself

Well that’s it. I hope you learned a lot in those few seconds of reading my rather ad-hoc instructions. Now if you don’t mind, I’m going to try and tip toe over my roommates dogs and get a cup of joe.

Peace

Leave a Comment