When working with legacy systems, we often need to migrate or duplicate databases between different SQL Server versions. The scenario becomes particularly challenging when dealing with SQL Server 2000 as the source and SQL Server 2005 as the destination.

The most reliable method for database duplication is using backup and restore operations. Here's how to implement it:

-- On source server (SQL Server 2000)
BACKUP DATABASE OriginalDB 
TO DISK = 'C:\Backups\OriginalDB.bak'
WITH INIT, COMPRESSION

-- On destination server (SQL Server 2005)
RESTORE DATABASE DuplicateDB 
FROM DISK = 'C:\Backups\OriginalDB.bak'
WITH 
MOVE 'OriginalDB_Data' TO 'C:\Data\DuplicateDB.mdf',
MOVE 'OriginalDB_Log' TO 'C:\Logs\DuplicateDB.ldf',
REPLACE

For same-server duplication, simply modify the restore command with a new database name and different file paths.

For same-server duplication with immediate availability requirements:

-- Step 1: Detach the original database
USE master
GO
EXEC sp_detach_db 'OriginalDB', 'true'
GO

-- Step 2: Copy the physical files using xp_cmdshell
EXEC xp_cmdshell 'copy C:\Data\OriginalDB.mdf C:\Data\DuplicateDB.mdf'
EXEC xp_cmdshell 'copy C:\Logs\OriginalDB.ldf C:\Logs\DuplicateDB.ldf'

-- Step 3: Reattach both databases
EXEC sp_attach_db 'OriginalDB', 
'C:\Data\OriginalDB.mdf', 
'C:\Logs\OriginalDB.ldf'

EXEC sp_attach_db 'DuplicateDB', 
'C:\Data\DuplicateDB.mdf', 
'C:\Logs\DuplicateDB.ldf'

When moving from SQL 2000 to 2005, consider these potential compatibility issues:

• Deprecated features in SQL 2005
• Database compatibility level differences
• Security model changes

After restoration, run this compatibility check:

-- Set compatibility level to SQL Server 2005
ALTER DATABASE DuplicateDB 
SET COMPATIBILITY_LEVEL = 90

For smaller databases or when you need schema modifications:

-- Generate schema script
USE OriginalDB
GO
EXEC sp_scriptdb @database_name = 'OriginalDB', 
@script_type = 'CREATE', 
@output_file = 'C:\Scripts\OriginalDB_Schema.sql'

-- Generate data script (third-party tools recommended)
-- Consider using SQL Server Integration Services (SSIS) 
-- or bcp utility for data transfer

For regular duplication tasks, create a stored procedure:

CREATE PROCEDURE usp_DuplicateDatabase
    @SourceDB VARCHAR(100),
    @TargetDB VARCHAR(100),
    @BackupPath VARCHAR(255)
AS
BEGIN
    DECLARE @BackupFile VARCHAR(300)
    DECLARE @SQL NVARCHAR(1000)
    
    SET @BackupFile = @BackupPath + @SourceDB + '_' + 
                      CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'
    
    -- Create backup
    SET @SQL = 'BACKUP DATABASE [' + @SourceDB + '] TO DISK = ''' + 
               @BackupFile + ''' WITH INIT, COMPRESSION'
    EXEC sp_executesql @SQL
    
    -- Restore as new database
    SET @SQL = 'RESTORE DATABASE [' + @TargetDB + '] FROM DISK = ''' + 
               @BackupFile + ''' WITH '
    SET @SQL = @SQL + 'MOVE ''' + @SourceDB + '_Data'' TO ''C:\Data\' + 
               @TargetDB + '.mdf'', '
    SET @SQL = @SQL + 'MOVE ''' + @SourceDB + '_Log'' TO ''C:\Logs\' + 
               @TargetDB + '.ldf'', REPLACE'
    
    EXEC sp_executesql @SQL
    
    -- Set compatibility level for SQL 2005
    SET @SQL = 'ALTER DATABASE [' + @TargetDB + '] SET COMPATIBILITY_LEVEL = 90'
    EXEC sp_executesql @SQL
END
GO

When working with MSSQL, you might need to duplicate databases for various purposes like testing, migration, or creating staging environments. There are two primary scenarios:

• Creating a copy on the same SQL Server instance with a different name
• Migrating the database to a different SQL Server (including version upgrades)

This works for both same-server duplication and cross-server migration:

-- Step 1: Create backup of source database (SQL 2000)
BACKUP DATABASE OriginalDB 
TO DISK = 'C:\Backups\OriginalDB.bak' 
WITH INIT, COMPRESSION;

-- Step 2: Restore to target server (SQL 2005) with new name
RESTORE DATABASE DuplicateDB 
FROM DISK = '\\NetworkShare\Backups\OriginalDB.bak'
WITH 
    MOVE 'OriginalDB_Data' TO 'D:\Data\DuplicateDB.mdf',
    MOVE 'OriginalDB_Log' TO 'E:\Logs\DuplicateDB.ldf',
    REPLACE, STATS = 10;

For creating a copy on the same server instance:

-- Step 1: Set database to single-user mode
ALTER DATABASE OriginalDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Step 2: Detach the database
EXEC sp_detach_db 'OriginalDB', 'true';

-- Step 3: Copy physical files (manually in Windows Explorer or xcopy)
-- OriginalDB.mdf and OriginalDB.ldf to DuplicateDB.mdf and DuplicateDB.ldf

-- Step 4: Attach both databases
EXEC sp_attach_db 'OriginalDB', 
    'D:\Data\OriginalDB.mdf', 
    'E:\Logs\OriginalDB.ldf';

EXEC sp_attach_db 'DuplicateDB', 
    'D:\Data\DuplicateDB.mdf', 
    'E:\Logs\DuplicateDB.ldf';

When moving from SQL 2000 to 2005, consider these compatibility issues:

• The backup/restore method automatically upgrades the database format
• Some deprecated features may need manual adjustment
• Check compatibility level after restore: ALTER DATABASE DuplicateDB SET COMPATIBILITY_LEVEL = 90;

For smaller databases or when you need schema customization:

-- Generate schema script (SQL 2000)
EXEC sp_script_db 'OriginalDB';

-- For data transfer, use BCP utility:
bcp OriginalDB..TableName out C:\Export\TableName.dat -n -S ServerName -U UserName -P Password
bcp DuplicateDB..TableName in C:\Export\TableName.dat -n -S NewServer -U UserName -P Password

For regular duplication tasks, consider creating a SQL Agent job with this T-SQL:

DECLARE @backupPath NVARCHAR(256)
DECLARE @restoreCmd NVARCHAR(MAX)
DECLARE @newDBName NVARCHAR(128) = 'DuplicateDB_' + CONVERT(VARCHAR(8), GETDATE(), 112)

SET @backupPath = 'C:\Backups\Auto_' + @newDBName + '.bak'

-- Create backup
BACKUP DATABASE OriginalDB TO DISK = @backupPath WITH INIT

-- Restore with new name
SET @restoreCmd = 'RESTORE DATABASE [' + @newDBName + '] FROM DISK = ''' + @backupPath + ''' WITH '
SET @restoreCmd = @restoreCmd + 'MOVE ''OriginalDB_Data'' TO ''D:\Data\' + @newDBName + '.mdf'', '
SET @restoreCmd = @restoreCmd + 'MOVE ''OriginalDB_Log'' TO ''E:\Logs\' + @newDBName + '.ldf'', REPLACE'

EXEC sp_executesql @restoreCmd