Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I restored sql server 2000 .bak file to sql server 2008 by

--FROM DISK = 'D:\DBHarshal\full backup august 6.BAK'

FROM DISK = 'D:\DBHarshal\full backup august 6.BAK'
WITH MOVE 'EPICSms_Data' TO 'C:\EPICSms_Data.mdf',
MOVE 'EPICSms_Log' TO 'C:\EPICSms_Log.mdf'


It asked to use master database when i used it i was able to load my backup ,but Now the problem is that i can see only tables with data but not stored procedure

share|improve this question

migrated from Aug 7 '10 at 18:42

This question came from our site for professional and enthusiast programmers.

Export and import the Stored procedues. I remmeber having the same issues before, but seems to have been fixed by the newer SQL servers.

To make it simpler to work, i would connect the 2k8 managment to the 2000 sql.

share|improve this answer

If you are still unable to restore a SQL Server 2000 database Backup to SQL Server 2008 AND you are able to connect to the SQL Server 2000 database, I recommend using the "Generate SQL Server Scripts Wizard" available in SQL Server 2008.

For your situation, you can use this tool as follows: (1) script all objects (tables, procs, logins, permissions, etc. -- you have the control to be selective) and RUN immediately. (2) script the data (ETL) as an SSIS package.

Next steps: (3) Open the data SSIS package (4) Add Execute SQL task(s) to complete BEFORE the ETL which sets all Foreign Key Constraints to "WITH NOCHECK" (5) Add Execute SQL task(s) to complete AFTER the ETL which sets all Foreign Key Constraints to "WITH CHECK CHECK"

From the SQL Server 2008 database you created in step (1), the code to generate the TSQL for steps (4) and (5) is:

    'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + OBJECT_NAME(parent_object_id) + ']' + ' NOCHECK CONSTRAINT ' + OBJECT_NAME(OBJECT_ID) AS ddlNoCheck
    ,'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + OBJECT_NAME(parent_object_id) + ']' + ' WITH CHECK CHECK CONSTRAINT ' + OBJECT_NAME(OBJECT_ID) AS ddlWithCheckCheck
    type_desc = 'FOREIGN_KEY_CONSTRAINT'
    , OBJECT_NAME(parent_object_id)
    , type_desc, OBJECT_NAME(OBJECT_ID)
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.