Below is the sql script to import data from source database into target database, It is assumed that you have both the databases on single server.The source database is current database & few tables are corrupted whereas the traget database is created from old backup for target database.since their is corruption ,it is not possible to take backup of current database. This script imports back data (only) from source to target database. You need to only replace 3 lines of the script (12th line from bottom).
Download script from here
------------- Create helper functions -----------------------------
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Mig_ImportTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Mig_ImportTable]
GO
CREATE PROC dbo.Mig_ImportTable @Database SYSNAME, @Table SYSNAME AS
SET NOCOUNT ON
DECLARE @Column SYSNAME
DECLARE @SQL NVARCHAR(4000)
DECLARE @colSQL NVARCHAR(4000)
DECLARE @IsIdentity BIT
DECLARE @IsTableIdentity BIT
SET @colSQL = ''
SET @SQL=''
SET @IsTableIdentity = 0 --false
SET @IsIdentity = 0 --false
PRINT 'Table Migration Started for :' + @Table + ' in ' + @Database
DECLARE curMoveDown CURSOR
LOCAL FORWARD_ONLY
OPTIMISTIC FOR
SELECT column_name,COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity')as IsIdentity FROM information_schema.columns
WHERE UPPER(table_name ) = UPPER(@Table)
OPEN curMoveDown FETCH NEXT FROM curMoveDown INTO @Column,@IsIdentity
WHILE @@FETCH_STATUS = 0
BEGIN
SET @colSQL = @colSQL + '[' + @Column + '],'
SET @IsTableIdentity = @IsTableIdentity | @IsIdentity
IF (@IsTableIdentity = 1) BREAK
FETCH NEXT FROM curMoveDown INTO @Column,@IsIdentity
END
CLOSE curMoveDown
DEALLOCATE curMoveDown
IF(LEN(@colSQL)>1)
BEGIN
SET @colSQL = LEFT(@colSQL,LEN(@colSQL) - 1)
IF(@IsTableIdentity = 1) SET @SQL = @SQL + 'SET IDENTITY_INSERT ' + @Table + ' ON '
SET @SQL = @SQL + ' ALTER TABLE ' + @Table + ' DISABLE TRIGGER ALL '
SET @SQL = @SQL + ' ALTER TABLE ' + @Table + ' NOCHECK CONSTRAINT ALL '
SET @SQL = @SQL + ' TRUNCATE TABLE ' + @Table + ' '
IF(@IsTableIdentity = 1)
SET @SQL = @SQL + ' INSERT INTO ' + @Table + ' (' + @colSQL + ') SELECT '+ @colSQL + ' FROM ' + '[' + @Database + '].[dbo].[' + @Table + '] '
ELSE
SET @SQL = @SQL + ' INSERT INTO ' + @Table + ' SELECT * FROM ' + '[' + @Database + '].[dbo].[' + @Table + '] '
IF(@IsTableIdentity = 1) SET @SQL = @SQL + ' SET IDENTITY_INSERT ' + @Table + ' OFF '
SET @SQL = @SQL + ' ALTER TABLE ' + @Table + ' ENABLE TRIGGER ALL '
SET @SQL = @SQL + ' ALTER TABLE ' + @Table + ' CHECK CONSTRAINT ALL '
EXEC sp_executesql @SQL
PRINT 'Table migrated :' + @Table
END
ELSE
PRINT 'No Column found :' + @Table + ' (SQL = ' + @colSQL + ')'
PRINT 'Table Migration finished for :' + @Table
GO
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Mig_ImportDatabase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Mig_ImportDatabase]
GO
CREATE PROC dbo.Mig_ImportDatabase @Source SYSNAME AS
DECLARE @SERVER SYSNAME
DECLARE @INSTANCE SYSNAME
DECLARE @FULLNAME SYSNAME
DECLARE @DBNAME SYSNAME
DECLARE @cmd varchar(1000)
DECLARE @SQL Nvarchar(1000)
DECLARE @Table Nvarchar(100)
DECLARE @Result INT
SET NOCOUNT ON
CREATE TABLE [#Mig_FailedTables] ( [name] SYSNAME NOT NULL ) ON [PRIMARY]
SELECT @SERVER = CONVERT(SYSNAME, SERVERPROPERTY('servername'))
SELECT @INSTANCE = IsNull('',CONVERT(SYSNAME, SERVERPROPERTY('InstanceName')))
SELECT @DBNAME = DB_NAME()
IF(Len(@INSTANCE)>0) SET @FULLNAME = @SERVER + '' + @INSTANCE
ELSE SET @FULLNAME = @SERVER
Print 'Migration started from database [' + @SOURCE + '] to database [' + @DBNAME + '] on server ' + @FULLNAME
--Cursor to loop throw all tables of Target database (current database)
DECLARE CurTables CURSOR LOCAL FORWARD_ONLY
OPTIMISTIC FOR
SELECT NAME from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by Name
OPEN CurTables FETCH NEXT FROM CurTables INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Migrating table ' + @Table
SET @cmd = 'ECHO Exec [Mig_ImportTable] ''' + @Source + ''',''' + @Table + ''' > DbMig.sql'
EXEC @Result = master..xp_cmdshell @cmd, no_output
SET @cmd = 'ECHO GO >> DbMig.sql'
EXEC @Result = master..xp_cmdshell @cmd, no_output
SET @cmd = 'ECHO @ECHO OFF > DbMig.cmd'
EXEC @Result = master..xp_cmdshell @cmd, no_output
SET @cmd = 'ECHO osql -E -b -S "' + @FULLNAME +'" -d "' + @DBNAME +'" -i "DbMig.sql" >> DbMig.cmd'
EXEC @Result = master..xp_cmdshell @cmd, no_output
SET @cmd = 'ECHO EXIT ERRORLEVEL >> DbMig.cmd'
EXEC @Result = master..xp_cmdshell @cmd, no_output
SET @cmd = 'CMD /c "DbMig.cmd>>DbMig.Log"'
EXEC @Result = master..xp_cmdshell @cmd, no_output
IF (@Result <> 0)
Insert into #Mig_FailedTables (Name) values (@Table)
FETCH NEXT FROM CurTables INTO @Table
END
CLOSE CurTables
DEALLOCATE CurTables
Print 'Migration Finished..'
SELECT Name as [Failed Tables] FROM #Mig_FailedTables
DROP TABLE #Mig_FailedTables
GO
------------- Migration of data starts from here ------------------
----------------------------------------------------------------------------------------------------------------------------------
USE TargetDatabase -- Target Database ** Change This
Exec Mig_ImportDatabase 'SourceDatabase' -- Source Database ** Change This - Import Complete database
EXEC Mig_ImportTable 'SourceDatabase','SpecificTableName' -- SpecificTableName ** Change This - Import Single Table
----------------------------------------------------------------------------------------------------------------------------------
