SQl2000 to SQL2008R2 Database Migration Scripts steps

Steps required for Database migration from SQL2000 to SQL2008R2.

Follow the below steps to migrate the database from older version (SQL2000) to another version SQL2008R2.
Backup the production database required and keeps it for restore into a new version SQL server.

1. Backup live Database
BACKUP DATABASE kumarDB
TO DISK = 'Z: \SQLServerBackups\kumarDB.bak'
GO

 2. Restore live Database to new server
----Restore Database
RESTORE DATABASE kumarDB
FROM DISK = 'D:\kumarDB.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:YourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:YourLDFFile.ldf'


3. Generate the scripts required for changing the column collation. (COL 1 Generate_change_column_collation); Keep the scripts for future use.
declare @toCollation sysname

SET @toCollation = 'Latin1_General_CI_AS' -- Database default collate

SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
WHEN DATA_TYPE in ('text','ntext') then ''
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
+' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'

END
FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES
ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
AND TABLE_TYPE = 'BASE TABLE'
and COLLATION_NAME <> @toCollation

4. This scripts required to create generation of primary key constraints scripts (COL 2 Generate_primary_key_contraints); Keep the scripts for future use.
BEGIN TRAN

-- Get all existing primary keys
DECLARE cPK CURSOR FOR
SELECT so.name,si.name,si.type_desc
from sys.indexes si
join sys.objects so
on si.object_id = so.object_id
and so.type = 'U'
where si.type_desc <> 'HEAP'
and si.is_Primary_Key = 1
ORDER BY so.Name

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME
Declare @KeyType nvarchar(50)

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName,@KeyType
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' ADD CONSTRAINT ' + @PkName + ' PRIMARY KEY ' + @KeyType + ' ('

-- Get all columns for the current primary key
DECLARE cPKColumn CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @PkTable AND CONSTRAINT_NAME = @PkName
ORDER BY ORDINAL_POSITION
OPEN cPKColumn

DECLARE @PkColumn SYSNAME
DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1
-- Loop through all columns and append the sql statement
FETCH NEXT FROM cPKColumn INTO @PkColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@PkFirstColumn = 1)
SET @PkFirstColumn = 0
ELSE
SET @PKSQL = @PKSQL + ', '

SET @PKSQL = @PKSQL + @PkColumn

FETCH NEXT FROM cPKColumn INTO @PkColumn
END
CLOSE cPKColumn
DEALLOCATE cPKColumn

SET @PKSQL = @PKSQL + ')'
-- Print the primary key statement
PRINT @PKSQL

FETCH NEXT FROM cPK INTO @PkTable, @PkName, @KeyType
END
CLOSE cPK
DEALLOCATE cPK


ROLLBACK


5. This script is required to generate scripts for alternate key indexes. (COL 3 Generate_alternate_key_indexes); Keep the scripts for future use.
BEGIN TRAN

-- Get all existing primary keys
DECLARE cPK CURSOR FOR
select si.Object_id,si.Index_Id,so.name,si.name,si.type_desc,si.is_unique from sys.indexes si
join sys.objects so
on so.object_id = si.object_id
and so.type = 'U'
and si.is_Primary_key = 0
and si.type_desc <> 'HEAP'
order by so.name

Declare @ObjectID int
Declare @IndexID int
Declare @TableName nvarchar(50)
Declare @IndexName nvarchar(50)
declare @IndexType nvarchar(50)
declare @IndexUnique bit

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @ObjectID,@IndexId,@TableName,@IndexName,@indexType,@IndexUnique
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
Declare @KeyUnique nvarchar(10) set @KeyUnique = ''
if @IndexUnique = 1 set @KeyUnique = 'Unique'
SET @PKSQL = 'Create ' + @KeyUnique + ' ' + @IndexType + ' INDEX ' + @IndexName + ' ON ' + @TableName + ' ('

-- Get all columns for the current key
DECLARE cPKColumn CURSOR FOR
select sc.name
from sys.index_Columns sic
join sys.columns sc
on sc.object_id = sic.object_id
and sc.column_id = sic.column_id
where sic.object_id = @ObjectID
and sic.Index_id = @IndexID

OPEN cPKColumn

DECLARE @PkColumn SYSNAME
DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1
-- Loop through all columns and append the sql statement
FETCH NEXT FROM cPKColumn INTO @PkColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@PkFirstColumn = 1)
SET @PkFirstColumn = 0
ELSE
Begin
SET @PKSQL = @PKSQL + ', '
end

SET @PKSQL = @PKSQL + @PkColumn

FETCH NEXT FROM cPKColumn INTO @PkColumn
END
CLOSE cPKColumn
DEALLOCATE cPKColumn

SET @PKSQL = @PKSQL + ')'
-- Print the primary key statement
PRINT @PKSQL

FETCH NEXT FROM cPK INTO @ObjectID,@IndexId,@TableName,@IndexName,@indexType,@IndexUnique
END
CLOSE cPK
DEALLOCATE cPK


ROLLBACK
6. This script is required to generate the scripts for foreign key constraints. (COL 4 Generate_foreign_key_contraints); Keep the scripts for future use.
BEGIN TRAN

-- Get all existing primary keys
DECLARE cPK CURSOR FOR
select sf.object_id,sf.name,so.name,sor.name from sys.foreign_keys sf
join sys.objects so
on so.object_id = sf.parent_object_id
join sys.objects sor
on sor.object_id = sf.referenced_object_id
ORDER BY sf.Name

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME
Declare @RefName nvarchar(50)
declare @objectid bigint

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @objectid,@PkName,@PkTable, @refName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
Declare @FKSQL Nvarchar(4000) set @fkSQL = ''
SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' WITH NOCHECK ADD CONSTRAINT ' + @PkName + ' Foreign KEY ' + ' ('
Set @FKSQL = ' REFERENCES ' + @RefName + ' ('

-- Get all columns for the current primary key
DECLARE cPKColumn CURSOR FOR
select so.name,sor.name from sys.foreign_key_columns sfc
join sys.columns so
on so.column_id = sfc.parent_column_id
and so.object_Id = sfc.parent_object_id
join sys.columns sor
on sor.column_id = sfc.referenced_column_id
and sor.object_id = sfc.referenced_object_id
where sfc.Constraint_object_id = @ObjectID
OPEN cPKColumn

DECLARE @PkColumn SYSNAME
Declare @fkColumn sysname
DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1
-- Loop through all columns and append the sql statement
FETCH NEXT FROM cPKColumn INTO @PkColumn,@fkColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@PkFirstColumn = 1)
SET @PkFirstColumn = 0
ELSE
Begin
SET @PKSQL = @PKSQL + ', '
set @FkSQL = @FKSQL + ', '
end

SET @PKSQL = @PKSQL + @PkColumn
set @FkSql = @FKSQL + @FKColumn

FETCH NEXT FROM cPKColumn INTO @PkColumn,@FKColumn
END
CLOSE cPKColumn
DEALLOCATE cPKColumn

SET @PKSQL = @PKSQL + ')'
set @FKSql = @FKSQL + ')'
-- Print the primary key statement
PRINT @PKSQL
Print @FKSQL

FETCH NEXT FROM cPK INTO @objectid,@PkName,@PkTable, @refName
END
CLOSE cPK
DEALLOCATE cPK


ROLLBACK



7. This script is required to generate the scripts for check constraints. (COL 5 Generate_check_contraints); Keep the scripts for future use.
select 'Alter Table ' + st.name + ' With Nocheck ' + 'Add Constraint ' + scc.name + ' check ' + scc.definition
from sys.tables st
join sys.check_constraints scc
on st.object_id = scc.parent_object_id
order by st.name



  8. This script is required to drop the check constaints. (COL 6 drop_check_contraints)
declare ca Cursor
for select st.name,scc.name
from sys.tables st
join sys.check_constraints scc
on st.object_id = scc.parent_object_id
order by st.name

declare @TableName nvarchar(50)
declare @ConstraintName nvarchar(50)
declare @DbName nvarchar(50)
Declare @Sql nvarchar(4000)

set @dbName = db_name()
open ca
fetch from ca into @TableName,@ConstraintName
While @@Fetch_Status = 0
Begin
set @SQL = 'use ' + db_name() +' Alter Table ' + @TableName + ' Drop Constraint ' + @ConstraintName + ';'
print @sql
exec (@Sql)
fetch from ca into @TableName,@ConstraintName
end

close ca
deallocate ca


             
9. This script is required to drop the foreign key constraints (COL 7 drop_foreign_key_contraints)
-- Get all existing Foreign keys
DECLARE cPK CURSOR FOR
select sf.object_id,sf.name,so.name,sor.name from sys.foreign_keys sf
join sys.objects so
on so.object_id = sf.parent_object_id
join sys.objects sor
on sor.object_id = sf.referenced_object_id
ORDER BY sf.Name

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME
Declare @RefName nvarchar(50)
declare @objectid bigint

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @objectid,@PkName,@PkTable, @refName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
Declare @FKSQL Nvarchar(4000) set @fkSQL = ''
SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' Drop CONSTRAINT ' + @PkName

-- Print the Drop key statement
PRINT @PKSQL
Exec(@pksql)

FETCH NEXT FROM cPK INTO @objectid,@PkName,@PkTable, @refName
END
CLOSE cPK
DEALLOCATE cPK
   
10. This script is required to drop the alternate key indexes. (COL 8 drop_alternate_key_indexes)
-- Get all existing Alternate keys
DECLARE cPK CURSOR FOR
select si.Object_id,si.Index_Id,so.name,si.name,si.type_desc,si.is_unique from sys.indexes si
join sys.objects so
on so.object_id = si.object_id
and so.type = 'U'
and si.is_Primary_key = 0
and si.type_desc <> 'HEAP'
order by so.name

Declare @ObjectID int
Declare @IndexID int
Declare @TableName nvarchar(50)
Declare @IndexName nvarchar(50)
declare @IndexType nvarchar(50)
declare @IndexUnique bit

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @ObjectID,@IndexId,@TableName,@IndexName,@indexType,@IndexUnique
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
Declare @KeyUnique nvarchar(10) set @KeyUnique = ''
if @IndexUnique = 1 set @KeyUnique = 'Unique'
SET @PKSQL = 'DROP INDEX ' + @IndexName + ' ON ' + @TableName

-- Print the Alternate key statement
PRINT @PKSQL
exec (@pksql)
FETCH NEXT FROM cPK INTO @ObjectID,@IndexId,@TableName,@IndexName,@indexType,@IndexUnique
END
CLOSE cPK
DEALLOCATE cPK
    
    
11. This script is required to drop the primary key constraints. (COL 9 drop_primary_key_contraints)
-- Get all existing primary keys
DECLARE cPK CURSOR FOR
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Where Constraint_Type = 'Primary Key'
ORDER BY TABLE_NAME

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
SET @PKSQL = 'use ' + db_name() + ' ALTER TABLE ' + @PkTable + ' drop CONSTRAINT ' + @PkName

print @PKSQL
exec(@PKSQL)

FETCH NEXT FROM cPK INTO @PkTable, @PkName
END
CLOSE cPK
DEALLOCATE cPK



             
12. This script is required to change the collation of the database. (COL 10 Change collation of database)
USE master;
GO
ALTER DATABASE KumarDB
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO
13. Get the scripts from Step 3 to change the column collation. (COL11_Change_Column_Collation)

14. Get the scripts from Step 4 to create the dropped primary keys. (COL12_Create_PK)

15. Get the scripts from Step 5 to create the dropped alternate keys. (COL13_Create_AK)

16. Get the scripts from Step 6 to create the dropped foreign keys. (COL_14_Create_FK)

17. Get the scripts from Step 7 to create the dropped check constraints. (COL_15_Create_CK)

18. Set the compatibility level of the database to new server compatibility level. (PCol1_set_compatibility_level)
ALTER DATABASE KumarDB
SET COMPATIBILITY_LEVEL = 100;
GO

19. Run the Database consistency check against a database. (PCol2_dbcc_dbcheck_with_data_purity)
DBCC CHECKDB ('KumarDB') WITH DATA_PURITY, NO_INFOMSGS

20. Run the Database consistency check against a database. It reports and corrects pages and row count inaccuracies in the catalog views (PCol3_dbcc_update_usage)
USE KumarDB;
GO
DBCC UPDATEUSAGE (KumarDB) WITH NO_INFOMSGS;
GO

21. Run the Database consistency check against a database. It updates statistics for all user-defined and internal tables in the database (Pcol4_update_statistics)
EXEC sp_updatestats;

22. If anything goes wrong, Drop the database and Re run the above steps.        
   RE RUN PROCESS DROP DATABASE

23. Finally consider the creation of application role, service account and add to role, grant permissions for application role and drop the old users or unnecessary users.
Have a happy and successful database migration implementation.



Comments

Popular posts from this blog

SSIS2008R2 Error: 0xC0209303 at Package:

SQL2008R2 Error: The remote copy of database "XXXXX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)