Article: Upgrading from SQL 7 to 2000 (Part 2)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Upgrading from SQL 7 to 2000 (Part 2)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-29 : 14:32:52

In Part 1, I introduced the basic options for upsizing a SQL Server from version 7.0 to 2000. One important part of that article was the list of what has to be moved. Here in part 2, I will take that list and explain how I accomplished all of this on 53 separate databases in under 2 hours total, from a remote location.

Article Link.

Nazim
A custom title

1408 Posts

Posted - 2002-05-30 : 02:57:52

I was thinking this would help. but then as Mark has rightly pointed out anything which disturbs the system databases will result in untold miseries.

sp_msforeachdb 'dbcc checkdb (?)'
(this will work for system db's too, so use it carefully)

--------------------------------------------------------------

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-05-30 : 13:38:13

Nice job Mark. I will be referring to these articles this summer as we upgrade.

Rootman
Starting Member

15 Posts

Posted - 2002-06-17 : 16:33:03

Great,

This is exactly what I need. I am moving from SQL dev to DBA, and this is the first project I have been assigned. I now have a plan to follow - thanks!!

byauchler
Starting Member

4 Posts

Posted - 2002-07-17 : 18:32:43

Thanks Mark...
This is a great article. I am doing an 7 to 2000 upgrade in a few weeks and I feel confident after reading your articles.

Biva:>>

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-07-17 : 19:41:16

Thanks for your comments, folks. It's good to know that my time and effort to write the articles after I did all the work is paying off for others.

Subrato
Starting Member

1 Post

Posted - 2002-07-30 : 13:41:06

The step 3 & 14 ( for transferring SQL agent Jbs) does not work. When I tried to run the script extracted by "Generate Scripts" on the new server, It produces errors like

Server: Msg 105, Level 15, State 1, Line 36
Unclosed quotation mark before the character string 'truncate table summary_monit
'.

When I inspect the code generated by "Generate Script", I find that there are codes like

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'EU1-DATS', @command = N'truncate table summary_monit

which look like missing a quotation mark at the end. Such codes are consistently produced by the "Generate Scipt" .

So I could not transfer my jobs this way.

Any solutions?

daveeasa
Starting Member

1 Post

Posted - 2004-01-19 : 19:24:45

I spent part of today getting this to work properly, please submit any comments or suggestions:

sp_MSforeachdb @command1 = 'IF (db_id(''?'') > 6) BEGIN DECLARE @files VARCHAR(1024)
SET @files = ''''
SELECT @files = @files + '',
@filename'' + CAST(fileid AS VARCHAR) + '' = N'''''' + RTRIM(filename) + ''''''''
FROM ?..sysfiles
SELECT ''sp_attach_db @dbName = N''''?'''''' + @files END'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-19 : 19:47:50

sp_MSforeachdb is going to run the command that you are creating. How can it do that if the database is already attached? It's attached because you are trying to get the info from syfiles.

How many databases do you want to attach?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-20 : 12:48:31

If you are just trying to write out the sp_attach_db commands for all of the user databases, then you can use this:


SET NOCOUNT ON

DECLARE @DBName SYSNAME
DECLARE @SQL VARCHAR(7000)

SELECT name
INTO #Databases
FROM master.dbo.sysdatabases
WHERE dbid > 6
ORDER BY name

CREATE TABLE #Code
(
Code VARCHAR(7000) NOT NULL
)

WHILE (SELECT COUNT(*) FROM #Databases) <> 0
BEGIN

	SELECT TOP 1 @DBName = name
	FROM #Databases
	ORDER BY name

	INSERT INTO #Code
	SELECT 'EXEC master.dbo.sp_attach_db @dbName = N''' + @DBName + ''', ' + CHAR(13) +  + '@filename1 = N'''

	CREATE TABLE ##Files
	(
	fileid SMALLINT NOT NULL,
	filename NVARCHAR(260) NOT NULL
	)

	SELECT @SQL = 'INSERT INTO ##Files SELECT fileid, RTRIM(filename) FROM ' + @DBName + '.dbo.sysfiles ORDER BY fileid'

	EXEC (@SQL)

	UPDATE #Code
	SET Code = Code + (SELECT filename FROM ##Files WHERE fileid = 1) + ''', ' + CHAR(13) + '@filename2 = N''' + (SELECT filename FROM ##Files WHERE fileid = 2) + ''''
	WHERE Code LIKE '%' + @DBName + '%'

	INSERT INTO #Code
	SELECT CHAR(13)
	
	DELETE FROM #Databases
	WHERE name = @DBName

	DROP TABLE ##Files

END

SELECT * FROM #Code

DROP TABLE #Code
DROP TABLE #Databases

As long as your database names aren't similar, it will work. Otherwise, you'll have to change the WHERE clause in the UPDATE statement.

Tara

Argyle
Yak Posting Veteran

53 Posts

Posted - 2004-03-25 : 09:49:21

Good article. I needed to refresh this info before a migration project.

I modified the last script a bit to allow it to build attach statments for databases with more than 2 filegroups.


SET NOCOUNT ON

DECLARE @dbname SYSNAME
DECLARE @filelist varchar(8000)
DECLARE @filecmd varchar(8000)
DECLARE @filecount INT
DECLARE @filenbr INT
DECLARE @fileid SMALLINT
DECLARE @filename NVARCHAR(260)

SELECT name
INTO #databases
FROM master.dbo.sysdatabases
WHERE dbid > 6
ORDER BY name

CREATE TABLE ##files
(
	fileid SMALLINT NOT NULL,
	[filename] NVARCHAR(260) NOT NULL
)

WHILE (SELECT COUNT(*) FROM #databases) <> 0
BEGIN

	SELECT TOP 1 @dbname = [name]
	FROM #databases
	ORDER BY [name]

	SELECT @filecmd = 'INSERT INTO ##files SELECT fileid, rtrim([filename]) FROM ' + @dbname + '.dbo.sysfiles'
	EXEC (@filecmd)

	SELECT @filecount = COUNT(*) FROM ##files

	SET @filenbr = 1

	SELECT @filelist = ''

	WHILE @filenbr <= @filecount
	BEGIN
		SELECT @fileid = fileid, @filename = [filename] FROM ##files ORDER BY fileid DESC

		SELECT @filelist = @filelist + '@filename' + CAST(@filenbr AS VARCHAR(3)) + ' = N''' + @filename + ''''

		IF @filenbr = @filecount --last file, no comma
		BEGIN
			SELECT @filelist = @filelist + CHAR(13) + CHAR(10)
		END
		ELSE
		BEGIN
			SELECT @filelist = @filelist + ',' + CHAR(13) + CHAR(10)
		END

		SELECT @filenbr = @filenbr + 1

		DELETE FROM ##files
		WHERE fileid = @fileid
	
	END

	--print attach statment for current db
	PRINT 'sp_attach_db @dbName = N''' + @dbname + ''',' + CHAR(13) + CHAR(10) + @filelist + 'GO' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
	
	DELETE FROM #databases
	WHERE name = @dbname
END

DROP TABLE ##files
DROP TABLE #databases

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-09 : 16:59:58

quote:

Originally posted by tduggan

If you are just trying to write out the sp_attach_db commands for all of the user databases, then you can use this:


SET NOCOUNT ON

DECLARE @DBName SYSNAME
DECLARE @SQL VARCHAR(7000)

SELECT name
INTO #Databases
FROM master.dbo.sysdatabases
WHERE dbid > 6
ORDER BY name

CREATE TABLE #Code
(
Code VARCHAR(7000) NOT NULL
)

WHILE (SELECT COUNT(*) FROM #Databases) <> 0
BEGIN

	SELECT TOP 1 @DBName = name
	FROM #Databases
	ORDER BY name

	INSERT INTO #Code
	SELECT 'EXEC master.dbo.sp_attach_db @dbName = N''' + @DBName + ''', ' + CHAR(13) +  + '@filename1 = N'''

	CREATE TABLE ##Files
	(
	fileid SMALLINT NOT NULL,
	filename NVARCHAR(260) NOT NULL
	)

	SELECT @SQL = 'INSERT INTO ##Files SELECT fileid, RTRIM(filename) FROM ' + @DBName + '.dbo.sysfiles ORDER BY fileid'

	EXEC (@SQL)

	UPDATE #Code
	SET Code = Code + (SELECT filename FROM ##Files WHERE fileid = 1) + ''', ' + CHAR(13) + '@filename2 = N''' + (SELECT filename FROM ##Files WHERE fileid = 2) + ''''
	WHERE Code LIKE '%' + @DBName + '%'

	INSERT INTO #Code
	SELECT CHAR(13)
	
	DELETE FROM #Databases
	WHERE name = @DBName

	DROP TABLE ##Files

END

SELECT * FROM #Code

DROP TABLE #Code
DROP TABLE #Databases

As long as your database names aren't similar, it will work. Otherwise, you'll have to change the WHERE clause in the UPDATE statement.

Tara

I know this is an old thread, but I needed some code to automate a bunch of sp_attach_db commands.

Anyway, why are you limiting the databases where dbid > 6? I have some user databases that have dbid <= 6 that are not getting caught.

I changed the code to read

WHERE name not in ('master', 'tempdb', 'model', 'msdb')

instead.

-ec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-09 : 17:04:05

It just matched the data I wanted at that time.

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-09 : 17:19:44

quote:
Originally posted by tduggan

It just matched the data I wanted at that time.

Tara

ok, cool. Thanks.

-ec

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-10 : 12:31:35

eyechart, what DBIDs do they have? Are they 5 and 6 with Northwind and Pubs not originally installed? I had thought that it was a standard configuration that DBIDs 1-6 were master, model, msdb, tempdb, Northwind and pubs (not necessarily in that order). Maybe it was just a fluke of my installations that it worked out that way every time?

---------------------------
EmeraldCityDomains.com

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-10 : 13:33:30

quote:
Originally posted by AjarnMark

eyechart, what DBIDs do they have? Are they 5 and 6 with Northwind and Pubs not originally installed? I had thought that it was a standard configuration that DBIDs 1-6 were master, model, msdb, tempdb, Northwind and pubs (not necessarily in that order). Maybe it was just a fluke of my installations that it worked out that way every time?

---------------------------
EmeraldCityDomains.com

pubs and northwind were originally installed, but they are long gone. do dbids get re-used?

EDIT:
I suppose I could test that. maybe I'll do that later on today when I get some time.

-ec

stealth420
Starting Member

1 Post

Posted - 2005-08-26 : 16:53:36

Does this method allow you to move the DTS packages to the new server? I don't see where that happens... Thanks in advance.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-30 : 13:43:56

No, DTS packages are not stored within a user database. I believe the definitions are stored inside msdb. I would suggest saving them out to an external storage and then bringing them back in if you really want to keep them just the same. But you also might want to re-create them to use newer functionality. This will be doubly true when SQL 2005 comes out, and the Integration Services (replacement to DTS) has been radically enhanced.

---------------------------
EmeraldCityDomains.com

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-07-31 : 17:58:49

old topic I know, but this is a really useful script.

I made another changed to the code, line 29 is now:


SELECT @filecmd = 'INSERT INTO ##files SELECT fileid, rtrim([filename]) FROM [' + @dbname + '].dbo.sysfiles'

This handles databases names with non-standard characters. We have some 3rd party databases that were created with a '-' in them. This script would blow up when it tried to perform the SELECT statement above without the brackets.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources