Choosing a SQL Server Upgrade Method – Part 1 | Times of server

Choosing a SQL Server Upgrade Method – Part 1


You are wanting to redesign your SQL Server to the most recent adaptation. Which SQL

Server update strategy will you pick? There are numerous alternatives accessible and a few

will bode well in your business settings and others won’t. What are the

distinctive sorts of update strategies and which one would it be a good idea for me to choose?


The strategy you update your SQL Server comes down to two business targets:

either your SQL Server redesign requires as meager downtime as could reasonably be expected or it requires

minimal measure of consumption or you need both; less expensive and speedier.

There are numerous ways you can approach redesigning your SQL Server. The strategy you

picked will decide how much hazard you will take. For instance, one strategy

will overhaul your framework significantly speedier absent much hazard to your information and spare time

on your application downtime; which means you are sparing time, yet you are spending

more on your equipment and permitting costs. The other alternative gives a less expensive arrangement

and furthermore can go speedier, yet there is a hazard that on the off chance that anything turns out badly you can’t

move back. One is costly and the other is less expensive and more dangerous. One alternative requires

a major application blackout and alternate does not.

There are advantages and disadvantages of every strategy, consequently picking the right technique

of redesign, contingent upon your business circumstance, is vital.

The accompanying are the most well-known choices utilized in the business to redesign or

move SQL Server examples:

Reinforcement and Restore

Connect and Detach

Differential Restore for bigger databases

Set up update

One next to the other update (without High Availability)

Moving redesign (with High Availability)

SQL Server Upgrade versus Movement

Give me a chance to illuminate the terms moving and redesigning in extent of SQL Server. I have

seen these terms to be utilized reciprocally. Be that as it may, there are a few contrasts.

Updating SQL Server can mean you are redesigning just an Edition of SQL Server.

Perhaps you are redesigning from SQL Server 2014 Standard Edition (SE) to SQL Server

2014 Enterprise Edition (EE). Updating additionally implies possibly redesigning the Version of

SQL Server, say for instance from SQL Server 2012 (EE) SP4 to SQL Server 2016 (EE)

SP2. Or then again you are redesigning the Edition and Version. This will be the circumstance if

you are updating from SQL Server 2012 (SE) to SQL Server 2017 (EE). In addition, at whatever point

you apply Service Packs (SP) or Cumulative Updates (CU) to your SQL Server as a

some portion of your standard upkeep, you are really completing a smaller than normal overhaul.

Relocation then again, might possibly incorporate an overhaul of SQL Server,

yet, it implies moving your databases starting with one occurrence of SQL Server then onto the next. Possibly

you are simply completing an equipment and OS revive of your SQL Server. Or on the other hand possibly there

are simply an excessive number of databases on one case of SQL Server and upkeep work executions

are covering business hours. You need to part the heap. You turn up another

example of SQL Server on the same VM machine or demand another different VM. At that point

you introduce SQL Server and gap and move a portion of the databases to the new case.

It could likewise mean you are moving your databases from on-premises to the cloud,

for example, Azure. At long last, relocation can likewise incorporate updating of SQL Server.

To put it plainly, an overhaul alludes to a SQL Server example update which will result

in the update of client and framework databases. An overhaul can be a piece of database

relocation. Relocation, then again, alludes to moving of databases between occasions

of SQL Servers either on a similar VersionEdition of SQL Server or overhauled VersionEdition

of SQL Server.

In this tip, we will examine initial 2 techniques in the rundown above.

SQL Server Upgrade utilizing Backup and Restore

This is the most fundamental and least difficult technique for overhauling your databases amid

relocation. Contingent upon assets on your virtual machine (VM), for the most part, databases

of sizes under 50 GB are great possibility for this technique for update.

Situation: You are intending to overhaul and relocate your SQLDev

example from SQL Server 2012 (Developer Edition) to SQL Server 2016 (Developer

Release). You have 10 Dev databases on your SQLDev case. You need to update

them on another SQL case SQL2016. The VM machine name is SQLVM1. You introduced

SQL Server 2016 (DE) on SQLVM1SQL2016. Each of the dev databases are not exactly

50 GB. You did the update utilizing the Backup and Restore technique.

Execution: Here we are adopting exceptionally shortsighted strategy and skipping

numerous points of interest that go into arranging of any redesign venture. Since this is a dev situation,

application downtime term isn’t an issue. These are least advances we are going

to take to relocate and overhaul dev databases.

Make a vocation “Do Backup” ahead of time of the redesign, with the content

to reinforcement every database on the SQLVM1SQLDev occasion.

Reinforcement DATABASE [Db1] TO DISK = N’C:MSSQLSQLBackupsDb1.bak’


Reinforcement DATABASE [Db2] TO DISK = N’C:MSSQLSQLBackupsDb2.bak’


reinforcement work step

Make an occupation “Do Restore” ahead of time of the redesign, with contents

to reestablish every database on the SQLVM1SQL2016 occasion.

Reestablish DATABASE [Db1] FROM DISK = N’C:MSSQLSQLBackupsDb1.bak’


Reestablish DATABASE [Db2] FROM DISK = N’C:MSSQLSQLBackupsDb2.bak’


Run work “Do Backup” on SQLVM1SQLDev to reinforcement databases.

Run work “Do Restore” on SQLVM1SQL2016 to reestablish databases.

After check of fruitful database overhaul, uninstall SQL server 2012

case SQLVM1SQLDev. Rename SQLVM1SQL2016 occasion as SQLVM1SQLDev.

Executive master.sys.sp_dropserver @server = ‘SQLVM1SQL2016 ‘;


Executive master.sys.sp_addserver @server= ‘SQLVM1SQLDev’, @local = neighborhood;



SQL Server Upgrade utilizing Detach and Attach Database

This is another fundamental and straightforward strategy for overhauling SQL Server databases amid

relocations. It’s anything but a suggested best practice technique. Essentially, it does the

same thing as “Reinforcement and Restore” strategy. It is considerably more secure and dependable

to complete an overhaul with “Reinforcement and Restore” for databases with littler


Situation: Your QA database occasion SQLQA is perched on exceptionally

old physical machine and you need to resign the machine. There are 8 QA databases

of sizes under 30 GB. Application downtime length isn’t imperative, however you

need to do it in a 1 business day, so QA can be ready for action like never before the following day. You

utilized the Detach and Attach technique. You need to move your SQL occurrence to

another VM, SQLVM2 that your foundation group made for this reason. You do

not have any desire to update the SQL Server Version or Edition.

Execution: Again, we are discarding a considerable lot of the means of an effective

relocation with a specific end goal to keep the illustrations basic. These are the base advances we require

to take.

Introduce SQL Server case SQLQA2 on SQLVM2 machine.

Issue a Detach summon for every database on SQLQA.

Utilize [master]




Executive master.dbo.sp_detach_db @dbname = N’DB1′


Duplicate the .mdf, .ndf and .ldf records to the new area on SQLVM2 machine.

Run an Attach summon for every database on SQLVM2SQLQA2 occasion.

Utilize [master]



ON (FILENAME = ‘C:SQLBackupsDB1_Data.mdf’), (FILENAME = ‘C:SQLBackupsDB1_Log.ldf’)


After check of fruitful movement, close down the physical server SQLQA

what’s more, decommission it after a proper time.

The reason this isn’t a prescribed method to relocate your databases is on the grounds that

you can keep running into different sorts of issues. For instance, if there is a SQL Server gathering

contrast between the source and goal SQL Server, your append summon may

fall flat. On the off chance that the registry where you replicated your database records on the new server does

not have appropriate authorization for the SQL Server benefit account, it may fall flat. Another

central reason is if some time has passed, say multi day or two, between the occasions you

have withdrawn a database and endeavoring to join, records can be erased by botch.

In the event that server reboots, documents may get debased.


In this tip, we talked about various manners by which we can do database movement

andor update. We contemplated 2 of the most fundamental techniques for database relocations utilizing

some business situations. We likewise talked about slight distinction in meaning of updating

versus relocation in SQL Server condition.

Subsequent stages

Read more

tips on Backup and Restore database.

Read more

tips on Attach and Detach database.

Stay tuned to Part 2 of this arrangement.

Last Update: 2018-08-27

next webcast catch

next tip catch

About the creator

MSSQLTips creator Ameena Lalani

Ameena Lalani is a MCSA on SQL Server 2016. She is a SQL Server veteran and began her voyage with SQL Server 2000. She has executed various High Availability and Disaster Recovery arrangements.

View every one of my tips


Leave a Reply

Your email address will not be published. Required fields are marked *