SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Upgrading from SQL 7 to 2000 (Part 2)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 05/29/2002 :  14:32:52  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

United Arab Emirates
1408 Posts

Posted - 05/30/2002 :  02:57:52  Show Profile  Reply with Quote
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)




--------------------------------------------------------------
Go to Top of Page

smccreadie
Aged Yak Warrior

USA
505 Posts

Posted - 05/30/2002 :  13:38:13  Show Profile  Visit smccreadie's Homepage  Reply with Quote
Nice job Mark. I will be referring to these articles this summer as we upgrade.


Go to Top of Page

Rootman
Starting Member

15 Posts

Posted - 06/17/2002 :  16:33:03  Show Profile  Reply with Quote
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!!

Go to Top of Page

byauchler
Starting Member

USA
4 Posts

Posted - 07/17/2002 :  18:32:43  Show Profile  Reply with Quote
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:>>

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 07/17/2002 :  19:41:16  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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.

Go to Top of Page

Subrato
Starting Member

1 Posts

Posted - 07/30/2002 :  13:41:06  Show Profile  Reply with Quote
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?

Go to Top of Page

daveeasa
Starting Member

1 Posts

Posted - 01/19/2004 :  19:24:45  Show Profile  Reply with Quote
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'
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 01/19/2004 :  19:47:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 01/20/2004 :  12:48:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Argyle
Yak Posting Veteran

53 Posts

Posted - 03/25/2004 :  09:49:21  Show Profile  Reply with Quote
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

Edited by - Argyle on 03/26/2004 04:40:46
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/09/2005 :  16:59:58  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 08/09/2005 :  17:04:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
It just matched the data I wanted at that time.

Tara
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/09/2005 :  17:19:44  Show Profile  Reply with Quote
quote:
Originally posted by tduggan

It just matched the data I wanted at that time.

Tara



ok, cool. Thanks.



-ec
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 08/10/2005 :  12:31:35  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/10/2005 :  13:33:30  Show Profile  Reply with Quote
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

Edited by - eyechart on 08/10/2005 13:34:25
Go to Top of Page

stealth420
Starting Member

1 Posts

Posted - 08/26/2005 :  16:53:36  Show Profile  Reply with Quote
Does this method allow you to move the DTS packages to the new server? I don't see where that happens... Thanks in advance.
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 08/30/2005 :  13:43:56  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/31/2006 :  17:58:49  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000