Jim Bonnie's Blog

Backing up SQL Server 2005 DB on discountasp.net

Apr 23

Written by: jbonnie
Sunday, April 23, 2006 11:11:49 AM 


Transferring SQL 2005 database (from another SQL 2k5 DB) is kinda a pain in the butt the way accounts are set-up on discountasp.net . For those of you whose familiar w/ SQL 2000, you would expect that SQL Import / Export wizard in SQL 2k5 will do the job. BUT that's not the case.

SQL 2k5's Import/Export wizard do not transfer all database objects, it will only transfer Table, Views and data but not Stored Procs, UDF, relationships, etc.

transferring with SSManagement Studio

1) Log into the SQL Server where the source database resides with SQL Management Studio

2) Right-Click on the database, Select Tasks, and Select Generate Scripts. The Script Wizard will pop up.

3) Highlight the database your want to transfer in the next screen. Click Next button

4) Choose Script Options Box will appear - Leave Defaults and change

“Generate Script for Dependent Objects” = TRUE
"Script Collation"= TRUE
"Script USE DATABASE" = TRUE

NOTE *** if you have full text indexes in your database, change
"Script Full-Text Indexes" = TRUE

Click Next

5) In the Choose Object Types - Select all except "Users" - Click Next button

Note *** you will be ask to select what "Database Roles, Defaults, Rules, Store Procedures, Tables, User-defined data types" you want to script - Select All or check on the box of the objects you want to script

and then click the Next button for each object you are asked to Script

6) Select Next and on the Output Option Box Wizard - select method to save script.

- if Script to file = select location and leave Unicode text *** Recommended
- if Script to Clipboard - open Notepad.exe and paste the script after it is successful ***Not Recommended
- if Script to New Query Windows - it will open a new query windows in SSMS

7) Open another connection to the remote SQL 2005 database.

Either open or copy the query and run against your SQL2005 database at DASP

***Note If the database you are copying from had objects owned by user other dbo, you will need to create a schema with the same of the user owning the object

Once the script completes, you should have all the database objects on the remote server.

8) To transfer database data, you can use the SQL Import/Export wizard.

Let us know if you have any feedback or comment.

Trackback Print
Location: Blogs Jim Bonnie's Blog
Tags:

Your name:
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel 

Navigation

Register Login

About Me

Thanks for stopping by. My name is Jim Bonnie and I have been writing software for over 20 years. After building financial data systems for Reuters for nearly 18 years I have started doing contract work and am excited about technology and currently focus on Microsoft solutions. DotNetNuke has helped provide a good introduction to ASP.NET web application development, and I am now starting to branch out into other areas. Data Access techniques and SubSonic is something that I am looking into now. This has helped me get an invitation to speak at Las Vegas at DNN Open Force 07. it was a great time. And I am looking forward to OpenForce08, where I wil be presenting on TDD for DNN.

 

Reading the codebetter blogs and attending a nothing but .net training class with JP Boodhoo helped spark my interest in ALT.NET. I attended the first conference in Austin Texas, and it was amazing to see Scott Guthrie introduce the ASP.NET MVC framework.

Add to Technorati Favorites

View blog top tags

Links

Jim Bonnie's Blog

Jim Bonnie's Blog

Jim Bonnie's Blog