DRAG DROP
You administer a Microsoft SQL Server 2012 environment that contains a production SQL
Server 2005 instance named SQL2005 and a development SQL Server 2012 instance
named SQL2012.
The development team develops a new application that uses the SQL Server 2012
functionality. You are planning to migrate a database from SQL2005 to SQL2012 so that the
development team can test their new application.
You need to migrate the database without affecting the production environment.
Which three actions should you perform in sequence? (To answer, move the appropriate
actions from the list of actions to the answer area and arrange them in the correct order.)
Answer: 
How can I perform ‘Restore (full) db-backup and transaction-log-backup on SQL2012’
when I ‘performed a full db-backup on SQL2005’ only
and never ‘performed a transaction-log-backup on SQL2005’ ?
By the way restoring a full db-backup only on SQL2012 should be sufficient for on a development-system.
It is should be like this:
1. Full backup on sql2005
2. Restore the full backup on sql2012
3. Change the compatibility level to 110 on sql2012
I Agree with you
agreed
This one correct
Or
1 Perform Full Backup from 2005
2 Perform Transaction Backup from 2005
3 Restore full backup and Transaction on 2012
4 Change the compatibility level to 110
I am sorry my above answer is wrong
The answer as it is right
1-Full Backup
2-Restore backup and Transaction log(because it automatically done by the server)
3-change the compatibility level to 110
Thanks
i think :
1 Perform Full Backup from 2005
2 Perform Transaction Backup from 2005
3 Restore full backup and Transaction on 2012
because the default compatibility level of SQLServer 2012 is ALREADY 110: https://msdn.microsoft.com/en-us/library/bb510680%28v=sql.110%29.aspx
Actually, the compatibility of a restored database from an older version will retain its compatibility level. From the link provided by Pete:
When a database is upgraded to SQL Server 2012 from any earlier version of SQL Server, the database retains its existing compatibility level if it is at least 90. Upgrading a database with a compatibility level below 90 sets the database to compatibility level 90.
So the compatibility level should be set to 110 as the last step
Well it will still work if you don’t change the compatibility.
In order to use the sql server 2012 new features you should chance the compatibility.
Key sentence “The development team develops a new application that uses the SQL Server 2012 functionality.” this is a nice flag that says “hey probably compatibility should be changed to support the sql server 2012 functionality”
the first step is definitely do a full back up.
and I do agree with pete a t-log back up then restore.
but when it is says to support the sql server 2012 functionality does that mean the new features or just to run on sql server 2012?
if it is to use the new features and run on sql server 2012 you would change the compatibility which means 4 steps should be taken. the last step being the compatibility. if it is just to run on sql server 2012 then no need for compatibility change.
So pete is correct. it said 3 options you have to perform the full back up followed by a log back up followed by restoring it in the mssql server 2012.
well, “A full database backup backs up the whole database. This includes part of the transaction log so that the full database can be recovered after a full database backup is restored. Full database backups represent the database at the time the backup finished.” https://msdn.microsoft.com/en-GB/library/ms186289.aspx
if you can restore the t-log back up from that then the answer provided is correct if not pete is correct.
it has to be a VSS backup .. as the question says without affecting the production environment.. a VSS backup does that.. and when you choose this option it makes more sense as well from the available options…
I think using VSS is correct.
1. Perform a VSS Backup on the database on SQL2005
2. Restore VSS Backup on SQL2012
3. Change the compatibility level to 110
The question states “You need to migrate the database without affecting the production environment.” And VSS does that. Here is the link:
https://documents.software.dell.com/netvault-backup-plug-in-for-sql-server/5.7/users-guide/backing-up-data/performing-vss-backups-in-sql-server-2005-and-later
The link states “VSS allows volume backups to be performed while applications on the system continue to write to the volumes.”
I agree:
1. Perform a VSS Backup on the database on SQL2005
2. Restore VSS Backup on SQL2012
3. Change the compatibility level to 110
From MSDN:
Use VSS when “The data usually needs to be backed up while the applications that produce the data are still running.”
This would fulfill the questions requirement of not affecting the production requirement.
https://technet.microsoft.com/en-us/library/ee923636(v=ws.10).aspx
1-Full Backup
2-Restore backup and Transaction log(because it automatically done by the server)
3-change the compatibility level to 110
Hi guys,
tricky question for me, remember that SQL 2005 does not have “copy only backup” option then performing full backup will have affect on backup plan scheduled on production. I prefer:
1. perform a Vss backup on sql 2005
2. restore vss backup on sql 2012
3. change the compatibility to 110
Any suggestions?
No Luke, actually SQL Server 2015 has “copy only backup” https://www.mssqltips.com/sqlservertip/1772/copy-only-backup-for-sql-server/