Customers may want to move SQL database to another server. This is the process to do so.
- 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.
- 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):
@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), ':', '')
INTO [dbo].#tempBackup (name, flag)
SELECT name, 0
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)
@name = name,
@fileName = @path + name + '_' + @fileDate + '.BAK' -- Unique FileName
--@fileName = @path + @name + '.BAK' -- Non-Unique Filename
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
SET flag = 1
WHERE name = @name
AND flag = 0
DROP TABLE [dbo].#tempBackup
- Copy all backed-up SQL databases from the old server to the new server, preferably in a folder in the root of C: drive.
- Restore all databases to the new SQL Server
- For each SQL database, expand the database -> Security -> Users and delete the portal user from the database user permissions.
- Go to the top level SQL Server, and go to Security under that, then to Logins.
- If the portal SQL user is not listed under there, right-click on Logins and click New Login…
- Create the SQL user.
- Uncheck Enforce Password Policy, Enforce Password Expiration, and User Must Change Password at Next Login
- Under Server Roles, make the user a dbcreator and public.
- Right-click the user and select Properties.
- Go to User Mapping
- For each portal database, check the box next to the database name, and then go to the bottom section and check db_owner.
- Click OK.
- All SQL databases should be set.
- Go to the Portal installation folder.
- 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.
- In c:\Program Files\Passageways\Services\Agent, in the agent config file, change the servername in the connection string.
- 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.