How To: Migrate Databases to Another SQL Server

Customers may want to move SQL database to another server.  This is the process to do so.

1. Make sure SQL is installed where the databases are going to be moved to.  Make sure it has Full-text Indexing installed as well.  This will want to be installed before performing the move.
2. On the original SQL Server, take a backup copy of all SQL databases. If they have a common name, you can use the following SQL Script (changing @Path and the common text as necessary):
   
   DECLARE

@name VARCHAR(50), -- database name

@path VARCHAR(256), -- path for backup files

@fileName VARCHAR(256), -- filename for backup

@fileDate VARCHAR(20) -- used for file name

CREATE TABLE [dbo].#tempBackup (name VARCHAR(200), flag BIT)

SET @path = 'C:\Migration\'

-- Includes the date in the filename

SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112)

-- Includes the date and time in the filename

--SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '')

INSERT

INTO [dbo].#tempBackup (name, flag)

SELECT name, 0

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') and name like 'Production_Portal%'

AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'

WHILE EXISTS(SELECT TOP 1 name FROM [dbo].#tempBackup WHERE flag = 0)

BEGIN

SELECT

@name = name,

@fileName = @path + name + '_' + @fileDate + '.BAK' -- Unique FileName

--@fileName = @path + @name + '.BAK' -- Non-Unique Filename

FROM [dbo].#tempBackup

WHERE flag = 0

-- does not overwrite the existing file

BACKUP DATABASE @name TO DISK = @fileName

-- overwrites the existing file (Note: remove @fileDate from the fileName so they are no longer unique

--BACKUP DATABASE @name TO DISK = @fileName WITH INIT

UPDATE #tempBackup

SET flag = 1

WHERE name = @name

AND flag = 0

END

DROP TABLE [dbo].#tempBackup

3. Copy all backed-up SQL databases from the old server to the new server, preferably in a folder in the root of C: drive.
4. Restore all databases to the new SQL Server
5. For each SQL database, expand the database -> Security -> Users and delete the portal user from the database user permissions.
6. Go to the top level SQL Server, and go to Security under that, then to Logins.
7. If the portal SQL user is not listed under there, right-click on Logins and click New Login…
8. Create the SQL user.
9. Uncheck Enforce Password Policy, Enforce Password Expiration, and User Must Change Password at Next Login
10. Under Server Roles, make the user a dbcreator and public.
11. Right-click the user and select Properties.
12. Go to User Mapping
13. For each portal database, check the box next to the database name, and then go to the bottom section and check db_owner.
14. Click OK.
15. All SQL databases should be set.
16. Go to the Portal installation folder.
17. For web.config and each module.config file in the Modules folder, change the ConnectionString property to have servername = the new server name instead of the old server name.
18. In c:\Program Files\Passageways\Services\Agent, in the agent config file, change the servername in the connection string.
19. If EIM is installed, locate the folder it is installed in, and change all connection strings in all the config files to have the new SQL Servername.