You are in luck! Since all of the SQL Server objects (tables, stored procedures, triggers, functions, etc.) are stored as T-SQL code in the system tables, it is possible to script out the objects in the SQL Server 7.0 objects and apply to the SQL Server 2000 environment. Scripting out the objects can be achieved by leveraging the SQL Server scripting application in Enterprise Manager. With this application it is possible to save the contents of the application to a local file or to the clip board, which can be pasted into Query Analyzer for execution. Below outlines the high level steps to access the script generation application in SQL Server 2000 Enterprise Manager.1. Right click on the database name, select the 'All Tasks' option and then the 'Generate SQL Script...' option to start the application residing in Enterprise Manager.
Figure 1 – Enterprise Manager – Generate SQL Script…
2. On the first tab select the objects of interest.
Figure 2 – Generate SQL Scripts – General Tab
3. Select the appropriate formatting options for the output.
Figure 3 – Generate SQL Scripts – Formatting Tab
4. Select the security, table and file options, then press 'OK' for the option to save the contents to disk or click back on the General Tab to 'Preview' the code and then save it to the clip board.
Figure 4 – Generate SQL Scripts – Options Tab
As you are in the process of moving these objects want to think about a formalized Upgrade and Change Management process. For more information check out:
- Article - SQL Server Upgrade Recommendations and Best Practices - Part 1
- Article - SQL Server Upgrade Recommendations and Best Practices - Part 2
- Presentation - Change Management for SQL Server
- Article - Who Needs Change Management
- Article - Change Management – Buzz words or real value?
Dig Deeper on Microsoft SQL Server Installation
Related Q&A from Greg Robidoux
Avoid restoring your SQL Server database to solve the SQL Server error message "server out of memory." The problem could be the memory settings. Continue Reading
Back up tables from a SQL Server database to a file with these commands. Continue Reading
Restore backups in SQL Server 2000 and SQL Server 2005 with this command. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.