Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Restore databse from SQL SERVER 2005 to SQL SERVER 2012. Expand / Collapse
Author
Message
Posted Tuesday, July 9, 2013 5:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 5:09 AM
Points: 2, Visits: 2
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
Post #1471550
Posted Tuesday, July 9, 2013 6:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 12,910, Visits: 32,026
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1471571
Posted Tuesday, July 9, 2013 9:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:57 AM
Points: 23, Visits: 426
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;
Post #1471698
Posted Tuesday, July 9, 2013 12:50 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
Thanks for sharing the script guys.
Post #1471806
Posted Tuesday, July 9, 2013 10:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 5:09 AM
Points: 2, Visits: 2
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
Post #1471937
Posted Wednesday, July 10, 2013 2:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 12,910, Visits: 32,026
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1472003
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse