Upgrading from Windows 2008 R2 to 2016 - migrating MS SQL

Migrating MS SQL (2008 R2 to 2016)

---

- ncc1701d.apoyar (Old Server – 2008R2)

- TempNCC (New Server – 2016)

Login to old server (ncc1701d.apoyar)

• Open SQL management studio (connect)

• Select service type

• Select name

• Connect

Database backup in SQL management studio

• Expand databases

• Select and right click database

• Task

• Backup

• Select full backup

• Database name leave as it is

• Select DISC option and keep location as it is (default)

• Name the backup file with (.bak) extension

• Click OK

Disable logon on Terminal Server

• Connect to server

• Open command prompt

• Run below command

- change logon /disable

Connect to New server (TempNCC)

• Browse for backup files on old server through file explorer

• Copy the files to new server

• Rename the old server

• Rename the new server (give it the old server’s name)

Open SQL management studio, click on New Query

Write and execute below queries

-- Get the file list from a backup file.

-- This will show you current logical names and paths in the BAK file

RESTORE FILELISTONLY FROM disk = N'C:\Backups\MyDatabaseName.bak'

-- Perform the restore of the database from the backup file.

-- Replace 'move' names (MDFLogicalName, LDFLogicalName) with those found in

-- the previous filelistonly command

restore database MyDatabaseName

from disk = N'C:\Backups\MyDatabaseName.bak'

with move 'MDFLogicalName' to 'D:\SQLData\MyDatabaseName.mdf',

    move 'LDFLogicalName' to 'D:\SQLLogs\MyDatabaseName_log.ldf',

replace, stats=10;