Tag: SQL

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
----------------------------------------------------------------------------------------------------------------------------------

Code snippet will loop through all tables of Database SOURCEDB and compare record counts with tables in TARGETDB database

USE SOURCEDB

EXEC SP_MSforeachtable ‘DECLARE @OriCount INT
     DECLARE @Count INT
     DECLARE @Name VARCHAR(400)
     SET @Count = (SELECT COUNT(*) FROM TARGETDB.?)
     SET @OriCount = (SELECT COUNT(*) FROM SOURCEDB.?)
     SET @Name=”?”

IF(@Count <> @OriCount)
BEGIN
         SELECT @Name,@Count as target,@OriCount as source

END‘