August 13, 2003 at 6:31 am
I upgraded a SQL Server 7 to SQL Server 2K and becos I initially had SQL Server 7 installed on D drive, therefore after the upgrade, I had SQL Server 2K on both drives C&D and data files are on D drive.
I have another SQL Server 2K but this one is a full installation, therefore the data files are on C drive. I tried to transfer a particular database from the full installation SQL to the upgrade using the below method:
1. Copy all the logins using the below script:
USE Northwind
GO
-- Create table Logins in Nortwind
CREATE TABLE [dbo].[Logins] (
[Name] [varchar] (30) NULL ,
[EncryptedPassword] [nvarchar] (128) NULL ,
[DefaultDB] [nvarchar] (128) NULL ,
[DefLanguage] [nvarchar] (128) NULL ,
[sid] [varbinary] (85) NULL ,
[EncryptOpt] [varchar] (30) NULL ,
[LoginName] [varchar] (50) NULL
) ON [PRIMARY]
GO
-- Insert information about the logins into table logins.
INSERT logins
SELECT name, [password], dbname, language, sid,
'skip_encryption', loginname
FROM master..syslogins
ORDER BY name
GO
-- Remove special SQL logins and all Windows logins.
dDELETE logins
WHERE loginname IN ('distributor_admin', 'guest', 'sa')
OR loginname LIKE '%\%'
GO
2. DTS the Logins table from Northwind that I created to the destination
3. Manually create the data (with same name) on destination.
4. Create the login on destination using the below script:
Create.logins.sql
-- Create source-server logins on target-server
USE Master
Go
DECLARE logincur CURSOR
FAST_FORWARD
FOR
SELECT [name], encryptedpassword, defaultdb,
deflanguage, sid, encryptopt
FROM Northwind..logins
DECLARE @loginame varchar(30),
@passwd nvarchar(128),
@defdb nvarchar(128),
@deflang nvarchar(128),
@sid varbinary(85),
@encryptopt varchar(30)
OPEN logincur
FETCH NEXT FROM logincur
INTO @loginame, @passwd, @defdb,
@deflang, @sid, @encryptopt
WHILE (@@fetch_status = 0)
BEGIN
EXEC master..sp_addlogin
@loginame, @passwd, @defdb,
@deflang, @sid, @encryptopt
FETCH NEXT FROM logincur
INTO @loginame, @passwd, @defdb,
@deflang, @sid, @encryptopt
END
CLOSE logincur
DEALLOCATE logincur
GO
5. Restore the database from the back up
Now this method works on SQL 7 to SQL 7. I tried DTS directly the database and there was one table missing and all stored procedure are not transfered.
Does any know how to do it please?
August 14, 2003 at 12:15 am
I did the attach method since the user is not an issue (using dbo user) and it works.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply