July 9, 2013 at 5:31 am
Hi,
We are in planning phase to migrate our current databases from SQL Server 2005 (SP3 + CU6) to SQL SERVER 2012.Will their be any issue if we take the backup and restore it to the new environmnet(SQL 2012).
Is it necessary to apply SP4 on SQL Server 2005 before we plan to migrate.
Thanks in advance.
Regards,
--Vinod
July 9, 2013 at 6:06 am
it is not necessary to upgrade, no; the database backup is the same, regardless of service pack, version, and that's what you would backup/restore. the service packs / CU change how the engine behaves, and not how the backup is stored.
one thing i'd mention is to make sure you update statistics; the stats are used differently between engine versions, and not updating statistics often leads to reports of "the new super server with 40 petabytes of RAM is slower than the old server" until that is done.
something like this is what i've suggested int eh past:
DECLARE @Exec VARCHAR(MAX)
SELECT @Exec = ''
SELECT @Exec = 'UPDATE STATISTICS '
+ QUOTENAME(schema_name(schema_id))
+ '.'
+ quotename(name)
+ ' WITH FULLSCAN ; '
+ CHAR(13)
+ CHAR(10) + @Exec FROM sys.tables ORDER BY name DESC
PRINT LEN(@Exec)
PRINT @Exec
EXEC(@Exec)
Lowell
July 9, 2013 at 9:20 am
we also perform same and may be you want to update your view , below is the script.
DECLARE @view AS VARCHAR(255) ;
DECLARE ListOfViews CURSOR FOR
SELECT TABLE_SCHEMA + '.' +TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0
ORDER BY TABLE_SCHEMA,TABLE_NAME
OPEN ListOfViews
FETCH NEXT FROM ListOfViews
into @view
WHILE (@@FETCH_STATUS <> -1)
BEGIN FETCH NEXT FROM ListOfViews
INTO @view
BEGIN TRY
EXEC sp_refreshview @view;
PRINT @view;
END TRY
BEGIN CATCH
PRINT 'recorded error in this refreshView on : ' + @view;
END CATCH;
FETCH NEXT FROM ListOfViews
INTO @view
END
CLOSE ListOfViews;
DEALLOCATE ListOfViews;
July 9, 2013 at 12:50 pm
Thanks for sharing the script guys.
July 9, 2013 at 10:09 pm
I thought SQL Server 2005 has to be on SP4 or above before we plan to migrate/restore it to SQL 2012.
Thanks for clarifying my doubt. I will also definitely consider using above scripts.
--Vinod
July 10, 2013 at 2:20 am
vinod.venugopal 71189 (7/9/2013)
I thought SQL Server 2005 has to be on SP4 or above before we plan to migrate/restore it to SQL 2012.Thanks for clarifying my doubt. I will also definitely consider using above scripts.
--Vinod
i think that might be true for an in place upgrade, but when just doing backup and restore on the new 2012, then it's not necessary
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply