Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

--RESTORE FILELISTONLY
--FROM DISK = 'D:\DBHarshal\full backup august 6.BAK'
--ALTER DATABASE EPICSms
--SET SINGLE_USER WITH
--ROLLBACK IMMEDIATE

RESTORE DATABASE EPICSms
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'


 ,REPLACE

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 stackoverflow.com Aug 7 '10 at 18:42

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

2 Answers 2

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:

SELECT 
    '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
FROM
    sys.objects
WHERE
    type_desc = 'FOREIGN_KEY_CONSTRAINT'
ORDER BY
    SCHEMA_NAME(schema_id)
    , OBJECT_NAME(parent_object_id)
    , type_desc, OBJECT_NAME(OBJECT_ID)
share|improve this answer

Your Answer

 
discard

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