Introduction

When upgrading applications we often move the database from one server to another by taking a back-up and restoring onto the new server.

Change Process

• Take a Back-up of the database
• Create a copy of the database on the new server

Technical Process

Step 1

To perform a one-off full backup of a database by using SQL Server Management Studio, execute the following steps:

Right-click the database you want to back up, choose Tasks, and then choose Back Up.

In the Back Up Database dialogue box, verify that Backup Type is set to Full. Specify a name for the Backup Set. Specify the destination by either using an existing backup device or creating a new backup device. Configuring a backup expiration enables SQL Server backup to overwrite the backup after the backup has expired.

On the Options page, choose whether to append to the existing media set or overwrite the existing backup sets. You can choose to back up to a new media set, erasing all existing sets. You can also choose to verify backups or perform a checksum test on data before it is written to backup and whether to continue the backup operation if an error occurs. You can also set the backup compression setting here.

Make sure the set up compression setting below is set to compress

Save to the location and create a folder for whichever app you wish to backup

Step 2

Right click on the restore database

Click on device, add and select the backup you wish

Select the below settings

Check there is a D and L drive

DO NOT enable tail log backup and click overwrite this existing database (with replace)

Warning: only skip the tail-log backup when the target is a copy you intend to overwrite (for example a test or new server). On a live database, overwriting WITH REPLACE without a tail-log backup loses every transaction since the last log backup.

Once you’ve clicked ok run the below code to see how long the restore or back-up will take.

SELECT DB_NAME(database_id),
       der.session_id,
       der.blocking_session_id,
       der.command,
       der.status,
       der.percent_complete,
       der.estimated_completion_time,
       CONVERT(VARCHAR(10), DATEADD(ms, der.estimated_completion_time, 0), 8) EstimatedTimeLeft
FROM sys.dm_exec_requests AS der WITH(NOLOCK)--order by session_id desc
WHERE command = 'restore database'
      OR command = 'backup database'
      OR command = 'restore log'
      OR command = 'DbccFilesCompact'
      OR command = 'Alter Index'
      OR command = 'KILLED/ROLLBACK'
      OR command = 'update statistic'
      OR command = 'DBCC TABLE CHECK'
      OR command = 'DBCC IVIEW CHECK'
      OR command = 'DBCC ALLOC CHECK'
      OR command = 'CREATE INDEX'
      OR command = 'BACKUP LOG'
      OR command = 'DBCC';

From my SQL Server DBA handbook.