Running sql server 2000 backup file on sql server 2008

Question

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

Answer 1

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.

Answer 2

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)