Home > Uncategorized > Check List – Move SQL Database

Check List – Move SQL Database

I’ve been tasked with moving almost 100 SQL databases from our old SQL Cluster to our brand new SQL Servers. To assist me, I’ve created a checklist, which may be useful to others…

Check List – Move SQL Database

This check list is to assist in moving a database from one SQL server to another.

  • Identify application connection logon details
  • Create logins
  • Create destination folders for MDF and LDF files
  • Take frontend application off line
  • Backup Database
  • Take Database Offline
  • Copy backup file to new SQL server
  • Restore Database
  • Change Database SQL Compatibility Level
  • Re-create connection logon details
  • Update application connection logon details
  • Delete the original Database

Identify application connection details

Identify how the application connects to SQL. If a SQL user account is used, obtain the password (this may be in a connection string in the application). If it’s not available, discuss with Application support about creating a new password. Check if other accounts have access to the database.

Create logins

Create logins for Windows Users/Groups or SQL logins. Check language settings.

Create destination folders for MDF and LDF files

Create the folders when the database files will be stored.

Take frontend application offline

If possible, take the frontend application offline. This will stop new data being written to the database.

Backup Database

Name the backup file similar to “<DatabaseName>_PreMove.BAK” so it can be identified from other database that may also be being move.

Take Database Offline

Right click the database, highlight Tasks, and then click Take Offline.

Copy backup file to new SQL server

Copy the BAK files to the new SQL server.

Restore Database

Create subfolders for the database with the Database and Log folders. Ensure that the file paths restore to these new locations.

Change Database SQL Compatibility Level

Right click the database. Select Options from the Select a page menu and change the Compatibility Level to the highest number (unless the vendor dictates otherwise).

Re-create connection logon details

Create a new login and ensure the default database is selected, but do not perform any user mappings. Select the database and open a New Query window.  Enter the following command:

sp_change_users_login 'auto_fix', 'NewLoginUserName'

This is not required for Active Directory users or groups, as the SID remains the same, so it automatically associated with the database.

Update application connection string

Ensure that the Server Team or Application Support team have updated the frontend connection to the SQL server and tested the functionality.

Delete the original Database

Delete the Database (which should be offline) or the original SQL Server.

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: