Tuesday, July 24, 2012

Microsoft SQL Server 2005/2008:Migrating Data from One Schema to Another.



Use this script to transfer data from one Schema(Transfer_Schema) to Another(DBO).

Read Article for your reference and knowledge,http://msdn.microsoft.com/en-us/library/ms173423.aspx

Case 1.Transfer Data from DBO to Transfer_Schema.

Declare @Object_Name varchar(100) --Database Objects

Declare @Object_Type varchar(100)--Object Type

Declare @SQL varchar(200)--Script for SQL Query

DECLARE db_cursor CURSOR FOR

select name,type from sys.sysobjects
where type in ('U','SQ','F','D','P','V','TR','IT','TF','FN','K')
order by 2

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @Object_Name, @Object_Type

WHILE @@FETCH_STATUS = 0 

BEGIN 

--Execute this script for object Transfer
SET @SQL='ALTER SCHEMA Transfer_Schema TRANSFER  [dbo].['+@Object_Name +']'

EXEC(@SQL)

Print 'Object Transfered :'+@Object_Name


FETCH NEXT FROM db_cursor INTO @Object_Name, @Object_Type

END

Close db_cursor

Deallocate db_cursor

Case 2.Transfer Data from Trasfer_Schema to DBO.

Declare @Object_Name varchar(100) --Database Objects

Declare @Object_Type varchar(100)--Object Type

Declare @SQL varchar(200)--Script for SQL Query

DECLARE db_cursor CURSOR FOR

select --OBJECT_SCHEMA_NAME(id) Schema_Name,
name,type from sys.sysobjects
where type in ('U','SQ','F','D','P','V','TR','IT','TF','FN','K')
order by 2

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @Object_Name, @Object_Type

WHILE @@FETCH_STATUS = 0 

BEGIN 

--Execute this script for object Transfer

SET @SQL='ALTER SCHEMA dbo TRANSFER [Transfer_Schema].['+@Object_Name+']'

EXEC(@SQL)

FETCH NEXT FROM db_cursor INTO @Object_Name, @Object_Type

END

Close db_cursor

deallocate db_cursor

Re Execute if any error and check all objects migrated to new Schema.


Microsoft SQL Server 2005:Apply Service Pack 4(SP4) to SQL Server 2005.



For Applying Service pack to SQL Server 2005,Need to follow this steps.

 Take Downtime for SQL Server.(Approximate Hours will be 1 or 2).

1.Download SP4 for from http://www.microsoft.com/en-us/download/details.aspx?id=7218

Read the System requirements and Prerequisite for the patches.

Download Appropriate Executable as per your environments.

2. Click to execute SQLServer2005SP4-KB2463332-x86-ENU












Apply Patches to SQL Server 2005 Instance.


 


 Check Any Processes to Stop...






 Click to Install...


 Wait For Process Completion.



 


Check/View Summary report.



Finish the Installation.,,,

uncheck if OS is not Windows Vista.


 


3.Check Version Release.