Transferring SQL 2005 Database
(EDIT: Please rate this thread if you use the information contained as I hope to post more guides like this and having them rated will allow me to improve upon them).
Transferring SQL 2005 database (from another SQL 2k5 DB) is kinda a pain in the butt. 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.
Method 1. Transferring with SQL Server Management 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 RFH
***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.
For those off you that don't have SSManagement Studio you can download an Express version of the management interface at
http://www.microsoft.com/downloads/d...displaylang=en
(note: this is a CTP release as the final has not yet been announced.)
Method 2. Backup Database
(Note: RFH do not offer SQL 2005 yet, and hence this method won't work yet, once available I'll try and put up a generic database backup script that those without a Management Stuido can use).
1). You will need to create a backup of your MS SQL Database (if you have the SQL Server Management Stuido you simply navigate to the database, right click and select backup.)
2). Open a ticket with RFH and arrange for them to restore the database. The RFH team will load the database and assign your database login as the 'dbo' (database owner). If you have any other logins or security requirements then you will need to re-apply these using scripts.
Method 3. Upsizing Tool
(this is not released yet and I'll update this when released and if made available by RFH, in the meantime here is a link to some information: http://weblogs.asp.net/scottgu/archi...15/427581.aspx).
Reply With Quote
Bookmarks