Only recently waved goodbye to the last SQL2K server I looked after, so have a few scripts in the toolbox still. You could also root around in the SSC scripts archive.
Here's an old index maintenance script by Kimberly Tripp:
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp - all rights reserved.
-- UPDATE: AUGUST 25, 2004
-- RE: Fix for DESC clustered indexes. There are no errors for DESC in
-- nonclustered indexes. Thanks!
--
-- For more scripts and sample code, check out
-- http://www.SQLSkills.com
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Execute this whole script to create the sp_RebuildIndexes stored procedure in Master.
-- Best Viewed with Courier New 12pt. and Tabs saved as 4 spaces not 8. (Tools, Options, Editor)
-- To use the sp_RebuildIndexes procedure once created use:
-- sp_RebuildIndexes
-- To Rebuild All Indexes on All Tables for all that have a Scan Density < 100%
-- sp_RebuildIndexes @ScanDensity = 80
-- To Rebuild All Indexes on All Tables with a Scan Density of < 80%
-- sp_RebuildIndexes 'Authors'
-- To Rebuild All Indexes on the authors table - for a Scan Density of < 100%
-- sp_RebuildIndexes 'Authors', 80
-- To Rebuild All Indexes on the authors table - for a Scan Density of < 80%
-- Object Name and ScanDensity are both optional parameters.
-- ScanDensity must be a whole number between 1 and 100.
USE master
go
IF OBJECTPROPERTY(object_id('sp_RebuildClusteredIndex'), 'IsProcedure') = 1
DROP PROCEDURE sp_RebuildClusteredIndex
go
IF OBJECTPROPERTY(object_id('sp_RebuildIndexes'), 'IsProcedure') = 1
DROP PROCEDURE sp_RebuildIndexes
go
CREATE PROCEDURE sp_RebuildClusteredIndex
(
@TableName sysname = NULL,
@IndexName sysname = NULL
)
AS
-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.sqlskills.com
-- NOTE: If your clustered index is NOT unique then rebuilding the clustered
-- index will cause the non-clustered indexes to be rebuilt. If the nonclustered
-- indexes were fragmented then this series of scripts will build them again.
-- This is something this script DOES NOT look for...because you should not create
-- clustered indexes on a non-unique column or composite set of columns.
IF @TableName IS NOT NULL
BEGIN
IF (OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0
AND OBJECTPROPERTY(object_id(@TableName), 'IsView') = 0)
BEGIN
RAISERROR('Object: %s exists but is NOT a User-defined Table. This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
RETURN
END
ELSE
BEGIN
IF OBJECTPROPERTY(object_id(@TableName), 'IsTable') IS NULL
BEGIN
RAISERROR('Object: %s does not exist within this database. Please check the table name and location (which database?). This procedure only accepts existing table names to process for index rebuilds.', 16, 1, @TableName)
RETURN
END
END
END
IF @IndexName IS NOT NULL
BEGIN
IF INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered') = 0
BEGIN
RAISERROR('Index: %s exists but is a Clustered Index. This procedure only accepts valid table names and their clustered indexes for rebuilds.', 16, 1, @IndexName)
RETURN
END
ELSE
BEGIN
IF INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered') IS NULL
BEGIN
SELECT @TableName, @IndexName, INDEXPROPERTY(object_id(@TableName), @IndexName, 'IsClustered')
RAISERROR('There is no index with name:%s on this table. Please check the table name and index name as well as location (which database?). This procedure only accepts existing table names and their clustered indexes for rebuilds.', 16, 1, @IndexName)
RETURN
END
END
END
-- So now we have a valid table, a valid CLUSTERED index and we're ready to rebuild.
-- Here's a quick overview of what this code will do:
-- Get the Column List and Index Defintion (Use the output from sp_helpindex)
-- Figure out if it's UNIQUE - to specify in CREATE INDEX statement
-- Build and Execute the CREATE INDEX command through dynamic string execution
DECLARE @ExecStr nvarchar(4000) -- more than enough even if 16 cols of 128 chars,
-- Tablename of 128 and Indexname of 128...
-- but if this is the case you have other problems :).
, @ColList nvarchar(3000)
, @Unique nvarchar(7) -- Will be either '' or 'Unique ' and added to CR Index String
, @FillFactor nvarchar(100)
CREATE TABLE #IndexInfo
(
IndexName sysname,
IndexDesc varchar(210),
IndexKeys nvarchar(2126)
)
INSERT INTO #IndexInfo
EXEC sp_helpindex @TableName
SELECT @ColList = IndexKeys
, @Unique = CASE
WHEN IndexDesc LIKE 'clustered, unique%'
THEN 'Unique '
ELSE ''
END --CASE Expression
, @FillFactor = ', FILLFACTOR = ' + NULLIF(convert(nvarchar(3),
(SELECT OrigFillFactor
FROM sysindexes
WHERE id = object_id(@TableName)
AND Name = @IndexName)), 0)
FROM #IndexInfo
WHERE IndexName = @IndexName
SELECT @ExecStr = 'CREATE ' + @Unique + 'CLUSTERED INDEX '
+ QUOTENAME(@IndexName, ']') + ' ON '
+ QUOTENAME(@TableName, ']') + '(' + @collist
+ ') WITH DROP_EXISTING ' + ISNULL(@FillFactor, '')
-- Added Aug 25, 04: FIX FOR DESC Indexes.
SELECT @ExecStr = REPLACE(@ExecStr, '(-)', ' DESC')
-- For testing the String
-- SELECT @ExecStr
-- Create the Clustered Index
EXEC(@ExecStr)
go
CREATE PROCEDURE sp_RebuildIndexes
(
@TableName sysname = NULL,
@ScanDensity tinyint = 100
)
AS
-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.sqlskills.com
--
-- This procedure will get the Fragmentation information
-- for all tables and indexes within the database.
-- Programmatically it will then walk the list rebuilding all
-- indexes that have a scan density less than the value
-- passed in - by default any less than 100% contiguous.
--
-- Use this script as a starting point. Modify it for your
-- options, ideas, etc. - and then schedule it to run regularly.
--
-- NOTE - This gathers density information for all tables
-- and all indexes. This might be time consuming on large
-- databases.
--
-- DISCLAIMER - Execute at your own risk. TEST THIS FIRST.
SET NOCOUNT ON
IF @ScanDensity IS NULL
SET @ScanDensity = 100
IF @ScanDensity NOT BETWEEN 1 AND 100
BEGIN
RAISERROR('Value supplied:%i is not valid. @ScanDensity is a percentage. Please supply a value for Scan Density between 1 and 100.', 16, 1, @ScanDensity)
RETURN
END
IF @TableName IS NOT NULL
BEGIN
IF OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0
BEGIN
RAISERROR('Object: %s exists but is NOT a User-defined Table. This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
RETURN
END
ELSE
BEGIN
IF OBJECTPROPERTY(object_id(@TableName), 'IsTable') IS NULL
BEGIN
RAISERROR('Object: %s does not exist within this database. Please check the table name and location (which database?). This procedure only accepts existing table names to process for index rebuilds.', 16, 1, @TableName)
RETURN
END
END
END
-- Otherwise the Object Exists and it is a table so we'll continue from here.
-- First thing to do is create a temp location for the data returned from DBCC SHOWCONTIG
CREATE TABLE #ShowContigOutput
(
ObjectName sysname,
ObjectId int,
IndexName sysname,
IndexId tinyint,
[Level] tinyint,
Pages int,
[Rows] bigint,
MinimumRecordSize smallint,
MaximumRecordSize smallint,
AverageRecordSize smallint,
ForwardedRecords bigint,
Extents int,
ExtentSwitches numeric(10,2),
AverageFreeBytes numeric(10,2),
AveragePageDensity numeric(10,2),
ScanDensity numeric(10,2),
BestCount int,
ActualCount int,
LogicalFragmentation numeric(10,2),
ExtentFragmentation numeric(10,2)
)
IF @TableName IS NOT NULL -- then we only need the showcontig output for that table
INSERT #ShowContigOutput
EXEC('DBCC SHOWCONTIG (' + @TableName + ') WITH FAST, ALL_INDEXES, TABLERESULTS')
ELSE -- All Tables, All Indexes Will be processed.
INSERT #ShowContigOutput
EXEC('DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS')
PRINT N' '
-- Quick test to see if everything is getting here correctly
-- SELECT * FROM #ShowContigOutput
-- Walk the showcontig output table skipping all replication tables as well as all tables necessary for
-- the UI. This is also where you can list large tables that you don't want to rebuild all at one time.
-- NOTE: If you take out a large table from rebuilding this script may have already checked density
-- meaning that the expense in terms of time may have been expensive.
-- Also, you should use a different procedure to rebuild a large table specifically.
-- Even when you pass in the tablename it will be avoided here if MANUALLY added to the
-- list by you.
-- Test, Test, Test!
DECLARE @ObjectName sysname,
@IndexName sysname,
@QObjectName nvarchar(258),
@QIndexName nvarchar(258),
@IndexID tinyint,
@ActualScanDensity numeric(10,2),
@InformationalOutput nvarchar(4000),
@StartTime datetime,
@EndTime datetime
DECLARE TableIndexList CURSOR FAST_FORWARD FOR
SELECT ObjectName, IndexName, IndexID, ScanDensity
FROM #ShowContigOutput AS sc
JOIN sysobjects AS so ON sc.ObjectID = so.id
WHERE sc.ScanDensity < @ScanDensity
AND (OBJECTPROPERTY(sc.ObjectID, 'IsUserTable') = 1
OR OBJECTPROPERTY(sc.ObjectID, 'IsView') = 1)
AND so.STATUS > 0
AND sc.IndexID BETWEEN 1 AND 250
AND sc.ObjectName NOT IN ('dtproperties')
-- Here you can list large tables you do not WANT rebuilt.
ORDER BY sc.ObjectName, sc.IndexID
OPEN TableIndexList
FETCH NEXT FROM TableIndexList
INTO @ObjectName, @IndexName, @IndexID, @ActualScanDensity
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @QObjectName = QUOTENAME(@ObjectName, ']')
SELECT @QIndexName = QUOTENAME(@IndexName, ']')
SELECT @InformationalOutput = N'Processing Table: ' + RTRIM(UPPER(@QObjectName))
+ N' Rebuilding Index: ' + RTRIM(UPPER(@QIndexName))
PRINT @InformationalOutput
IF @IndexID = 1
BEGIN
SELECT @StartTime = getdate()
EXEC sp_RebuildClusteredIndex @ObjectName, @IndexName
SELECT @EndTime = getdate()
SELECT @InformationalOutput = N'Total Time to process = ' + convert(nvarchar, datediff(ms, @StartTime, @EndTime)) + N' ms'
PRINT @InformationalOutput
END
ELSE
BEGIN
SELECT @StartTime = getdate()
EXEC('DBCC DBREINDEX(' + @QObjectName + ', ' + @QIndexName + ') WITH NO_INFOMSGS')
SELECT @EndTime = getdate()
SELECT @InformationalOutput = N'Total Time to process = ' + convert(nvarchar, datediff(ms, @StartTime, @EndTime)) + N' ms'
PRINT @InformationalOutput
END
PRINT N' '
FETCH NEXT FROM TableIndexList
INTO @ObjectName, @IndexName, @IndexID, @ActualScanDensity
END
END
PRINT N' '
SELECT @InformationalOutput = N'***** All Indexes have been rebuilt. ***** '
PRINT @InformationalOutput
DEALLOCATE TableIndexList
go
Wait stats, the old way (not sure where this originated from):
use master
go
if ((object_id('sp_waitstats') is not null) and (objectproperty(object_id('sp_waitstats'), 'IsProcedure') = 1))
drop proc [dbo].[sp_waitstats]
go
create proc [dbo].[sp_waitstats] @orderby nvarchar(10) = N'total'
as
set nocount on
if (lower(@orderby) not in ('total', 'resource', 'signal'))
begin
raiserror('Error: incorrect @orderby value, use ''total'', ''resource'', ''signal''', 10, 1) with nowait
return
end
declare @requests bigint,
@totalwait numeric(20, 5),
@totalres numeric(20, 5),
@totalsig numeric(20, 5),
@endtime datetime,
@begintime datetime
create table [#waitstats]
(
[wait type] varchar(80) not null,
[requests] bigint not null,
[wait time] numeric(20, 5) not null,
[signal wait time] numeric(20, 5) not null,
[now] datetime not null default getdate()
)
insert into [#waitstats]([wait type], [requests], [wait time], [signal wait time])
exec ('dbcc sqlperf(waitstats) with tableresults, no_infomsgs')
select @begintime = min([now]),
@endtime = max([now])
from [#waitstats]
where [wait type] = 'Total'
--- subtract waitfor, sleep, and resource_queue from Total
select @requests = sum([requests]),
@totalwait = sum([wait time]),
@totalres = sum(([wait time] - [signal wait time])),
@totalsig = sum([signal wait time])
from [#waitstats]
where [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total', '***total***')
and [now] = @endtime
-- insert adjusted totals, rank by percentage descending
delete [#waitstats]
where [wait type] = '***total***'
and [now] = @endtime
insert into #waitstats values('***total***', @requests, @totalwait, @totalsig, @endtime)
if (@orderby = N'total')
begin
select [requests],
[wait type],
[total wait time] = [wait time],
[resource wait time] = [wait time] - [signal wait time],
[signal wait time],
[%total wait time] = case @totalwait when 0 then 0 else cast(100 * [wait time] / @totalwait as numeric(20, 5)) end,
[%resource wait time] = case @totalres when 0 then 0 else cast(100 * ([wait time] - [signal wait time]) / @totalres as numeric(20, 5)) end,
[%signal wait time] = case @totalsig when 0 then 0 else cast(100 * [signal wait time] / @totalsig as numeric(20, 5)) end,
[avg total wait time] = case [requests] when 0 then 0 else cast(100 * @totalwait / [requests] as numeric(20, 5)) end,
[avg resource wait time]= case [requests] when 0 then 0 else cast(100 * @totalres / [requests] as numeric(20, 5)) end,
[avg signal wait time] = case [requests] when 0 then 0 else cast(100 * @totalsig / [requests] as numeric(20, 5)) end
from [#waitstats]
where [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total')
and [now] = @endtime
order by [%total wait time] desc
end
if (@orderby = N'resource')
begin
select [requests],
[wait type],
[total wait time] = [wait time],
[resource wait time] = [wait time] - [signal wait time],
[signal wait time],
[%total wait time] = case @totalwait when 0 then 0 else cast(100 * [wait time] / @totalwait as numeric(20, 5)) end,
[%resource wait time] = case @totalres when 0 then 0 else cast(100 * ([wait time] - [signal wait time]) / @totalres as numeric(20, 5)) end,
[%signal wait time] = case @totalsig when 0 then 0 else cast(100 * [signal wait time] / @totalsig as numeric(20, 5)) end,
[avg total wait time] = case [requests] when 0 then 0 else cast(100 * @totalwait / [requests] as numeric(20, 5)) end,
[avg resource wait time]= case [requests] when 0 then 0 else cast(100 * @totalres / [requests] as numeric(20, 5)) end,
[avg signal wait time] = case [requests] when 0 then 0 else cast(100 * @totalsig / [requests] as numeric(20, 5)) end
from [#waitstats]
where [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total')
and [now] = @endtime
order by [%resource wait time] desc
end
if (@orderby = N'signal')
begin
select [requests],
[wait type],
[total wait time] = [wait time],
[resource wait time] = [wait time] - [signal wait time],
[signal wait time],
[%total wait time] = case @totalwait when 0 then 0 else cast(100 * [wait time] / @totalwait as numeric(20, 5)) end,
[%resource wait time] = case @totalres when 0 then 0 else cast(100 * ([wait time] - [signal wait time]) / @totalres as numeric(20, 5)) end,
[%signal wait time] = case @totalsig when 0 then 0 else cast(100 * [signal wait time] / @totalsig as numeric(20, 5)) end,
[avg total wait time] = case [requests] when 0 then 0 else cast(100 * @totalwait / [requests] as numeric(20, 5)) end,
[avg resource wait time]= case [requests] when 0 then 0 else cast(100 * @totalres / [requests] as numeric(20, 5)) end,
[avg signal wait time] = case [requests] when 0 then 0 else cast(100 * @totalsig / [requests] as numeric(20, 5)) end
from [#waitstats]
where [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total')
and [now] = @endtime
order by [%signal wait time] desc
end
drop table [#waitstats]
go
exec sp_waitstats
exec sp_waitstats @orderby = 'total'
exec sp_waitstats @orderby = 'resource'
exec sp_waitstats @orderby = 'signal'
go
Since you are using the Backup/Restore method, you don't need to copy the database (mdf) and log file (ldf) to the new server, just the backup files. You do not need to recreate the database either. The database, database files and log file will all be created during the restore process. The upgrade process is done by SQL Server when restoring your database backup.
Here is the method I use:
On SQL Server 2000
- Perform a full backup of the database
- Perform a log backup of the database
"captures records on the transaction log that were written since the last transaction log backup". Consider using the WITH NORECOVERY option, if you don't want people accessing the database after you're done.
- Copy the full database and log backup files to the server that has SQL Server 2005
On SQL Server 2005
- Restore the full backup with NORECOVERY
Make sure you change the directories as mentionned in your post.
- Restore the log backup with RECOVERY
Once the database is restored on the new server. I run the following commands in a new query window
USE [mydb]
sp_updatestats -- updates statistics
This is because statistics are not automatically updated during the upgrade process.
DBCC CHECKDB WITH DATA_PURITY
Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. This is important when upgrading from SQL Server 2000 to 2005 or 2008.
Associating database users with Logins
Now that your database has been restored, you need to associate its orphaned users with an actual login.
Method A: Fixing orphaned users
EXEC sp_change_users_login 'Report'
-- find orphaned users
- if the login exists already, you can asssociate the orphaned user with the login. Otherwise, you can create the login
-- Login exists
EXEC sp_change_users_login 'Auto_Fix', 'user'
-- Login doesn't exist
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'
Method B: sp_help_revlogin
You also have an alternative called sp_help_revlogin which can help you to restore not only the login but the original password that went with it.
http://support.microsoft.com/kb/246133/en-us
It essentially entails creating a stored procedure on your old server to generate a script that will output all of your existing logins. You then copy the output of that stored procedure to your new server and restore the logins that are associated with the database you restored.
Best Answer
SQL 2000 maintenance plans are just SQL Jobs with a bunch of parameters. You can easily enough just look at the parameters which are passed to sqlmaint.