Migrate 2000 Database to 2005
-
My manager decided this morning that we are going to take our SQL Server 2000 database and migrate it to SQL Server 2005. I now have until the new server is configured to figure out how to make this happen.
We are using a new server with just SQL Server 2005 EE-32. Since we are not upgrading the old SQL 2000 server, I think this is the path I have to take:
1. Restore a full backup of the sql2000 database to the new SQL2005 box. (This converts the database to SQL 2005 - version 9.0?)
2. Manually recreate logins
3. Manually recreate DTS packages / backup jobs
There are probably a lot better ways to accomplish items 2 and 3, but for now I'll be happy if I can open the database, login in to it, and back it up.
Any suggestions would be appreciated!
Thanks!
-r.
-
Sounds kind of unreasonable to rush into a migration to SQL2005. It can be a pretty big deal.
The common recommendation is to download and run the SQL Server Upgrade Advisor from http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en to check for any issues that will affect your migration.
As for your plan,
1.The compatibility level isn't automatically changed to 9.0 when you restore a backup. You have to do it manually. You'll also want to read up on schemas in 2005 as a schema will be created for each database user when the database is restored.
2. You can script out the logins in your 2000 instance.
3. We've decided to use the 'Transfer Jobs' task in SSIS to migrate our jobs. We're using the SSIS Package Migration Wizard for DTS packages. Be aware that SSIS is worlds different from DTS and migrating packages and getting them to work is a big job in itself.
Good luck!
Greg
Greg
-
Thanks for your counsel! I appreciate the suggestions!
To be fair, management isn't expecting it to be done immediately, and sometimes the best way to learn is to jump right in. I will probably set this database up a few times before I get everything the way I want it.
I also have to familiarize myself with the direcrtory tree versus EnterpriseMgmt, and terms like SSIS, CLR. If they existed in SQL2000 i didn't use them.
-
Those terms aren't used in SQL2000, so don't feel bad about that. BTW, there are lots of good articles about migrating and upgrading to SQL2005 on this site and on Microsoft.com. And the Upgrade Advisor is a must, believe me! Again, good luck!
Greg
Greg
-
Here is the obligatory message from me - even if you use the Upgrade Advisor and change the compatibility level to 90 your database could be broken and you won't know unless a) you do a full system regression test or b) script out your SQL 2000 database and rebuild it on a clean SQL 2005 database.
We have more on this subject on our website http://www.innovartis.co.uk/topical/migratingto2005.aspx and some tools that can help you easily script out your database and rebuild it on SQL 2005.
Once you know what the real issues are you can fix them and rebuild. At the end of this you have a succinct set of the modified scripts. At this point you can do the backup from 2000 and restore to 2005, set the compatibility level to 90 and run the fix scripts and everything will be a-ok.
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com -
Thanks for the input! I'll be sure to check out the website and implement your suggestions!
-
Hi,
Following DB creation script works fine in SQL server 200 but It is giving problem in the SQL 2005. Unfortunately I dont have sql 2005 installed environment access with me. I have given this to my setup team sits in UK and they are not able to provide the detailed information about what the problem is.
If any one of you can run the below script in SQL 2005 and see it works and let me know ? It would be great if I get sugessions from any of you.
-- Create a table for SQL error tracking.
IF EXISTS (select * from tempdb..sysobjects WHERE name = 'SQLerrorTracking')
DROP TABLE tempdb..SQLerrorTracking
GO
CREATE TABLE tempdb..SQLerrorTracking(
errCode int,
errDesc varchar(255),
create_Ts datetime default getdate()
)
PRINT 'Temporary table tempdb.SQLerrorTracking created.'
GO
-- don't return the amount of the rows affected in this script.
SET NOCOUNT ON
USE master
GO
-- declare variables for database creationg
DECLARE @dbName sysname -- logical name of the database to be created
DECLARE @dbFileName sysname -- physical name of the database file (???.mdf)
DECLARE @dbPhysname sysname -- physical path and name of the database file
DECLARE @logName sysname -- logical name of the log file
DECLARE @logFileName sysname -- physical name of the log file (???.ldf)
DECLARE @logPhysname sysname -- physical path and name of the log file
DECLARE @dbInitialSize sysname -- Initial size of the database data file
DECLARE @dbMaxSize sysname -- Maximum size of the data file
DECLARE @dbGrowth sysname -- growth rate of the data file
DECLARE @logInitialSize sysname -- Initial size of the log file
DECLARE @logMaxSize sysname -- Maximum size of the log file
DECLARE @logGrowth sysname -- growth rate of the data file
DECLARE @FileDir sysname -- physical path to the datafile directory
DECLARE @FilePath sysname -- physical path and filename of the new data file
DECLARE @LogFilePath varchar(128) -- physical path and filename of the new log file
DECLARE @MasterPos int -- for finding the path
DECLARE @dropOldVersion bit -- drop database if it exists
-- start of database parameters, please change the values
-- so that they fullfill your needs.
SELECT @dbName = 'DPIN'
SELECT @dbInitialSize = '2500MB'
SELECT @dbMaxSize = '50000MB'
SELECT @dbGrowth = '100'
SELECT @logInitialSize = '200MB'
SELECT @logMaxSize = '1000MB'
SELECT @logGrowth = '10'
SELECT @dropOldVersion = 0
-- end of database parameters
-- check if the database already exists.
PRINT 'Create database part starting...'
IF @dropOldVersion = 1
BEGIN
IF EXISTS (SELECT * FROM master..sysdatabases WHERE name = @dbName)
BEGIN
PRINT 'Drop the existing ' + @dbName + ' database.'
EXEC ('DROP DATABASE ' + @dbName)
END
END
IF NOT EXISTS (SELECT * FROM master..sysdatabases WHERE name = @dbName)
BEGIN
PRINT 'Database ' + @dbName + ' does not exist.'
-- set the name of the database and the data file
SELECT @dbFilename = @dbName + '.mdf'
-- create the physical names of the data and log files.
SELECT @logName = @dbName + '_log'
SELECT @logFileName = @logName + '.ldf'
-- get the full path to the master.mdf
SELECT @FilePath = phyname
FROM master..sysdevices
WHERE name = 'master'
-- extract the path
SELECT @MasterPos = CHARINDEX('MASTER.MDF', @FilePath)
SELECT @FileDir = SUBSTRING(@FilePath, 1 , @MasterPos -1)
-- create the path for the new data and log file
SELECT @dbPhysName = @FileDir + @dbFileName
SELECT @logPhysName = @FileDir + @logFilename
PRINT '@dbPhysName=' + @dbPhysName
PRINT '@logPhysName=' + @logPhysName
EXEC ('CREATE DATABASE ' + @dbName +
' ON PRIMARY (NAME = ' + @dbName +
',FILENAME = ''' + @dbPhysName + ''',SIZE = ' + @dbInitialSize +
-- ',MAXSIZE = ' + @dbMaxSize +
',FILEGROWTH = ' + @dbGrowth +')' +
'LOG ON ( NAME = ' + @logName +
',FILENAME = ''' + @logPhysName + ''' ,SIZE = ' + @logInitialSize +
-- ',MAXSIZE = ' + @logMaxSize +
',FILEGROWTH = ' + @logGrowth +')')
END
GO
-- Check if errors occurred during the CREATE DATABASE section
IF @@ERROR <> 0
BEGIN
INSERT INTO tempdb..SQLerrorTracking (errCode,errDesc) VALUES(@@ERROR,'')
PRINT 'Error: ' + convert(char,@@ERROR) + ' occurred.'
END
-- CREATE DATABASE section ends here. >>>>
-- Replace <DATABASENAME> with the logical name of your database.
USE DPIN
go
-- Set the truncate log on checkpoint option
exec sp_dboption DPIN, 'trunc. log on chkpt.', TRUE
go
-- Check if errors occurred during the CREATE DATABASE section
IF @@ERROR <> 0
BEGIN
INSERT INTO tempdb..SQLerrorTracking (errCode,errDesc) VALUES(@@ERROR,'')
PRINT 'Error: ' + convert(char,@@ERROR) + ' occurred.'
END
-- Create scheduled tasks for the database.
use master
GO
declare @ServerName CHAR(40)
select @ServerName = srvname from sysservers where srvid = 0
/**********************************************/
/* Create the Dump transaction log task */
/**********************************************/
if exists (select * from msdb..systasks where name = 'DUMP KLSDB transaction log')
BEGIN
exec msdb..sp_droptask 'DUMP KLSDB transaction log'
END
exec msdb..sp_addtask 'DUMP KLSDB transaction log', @subsystem = 'TSQL', @server = @ServerName, @username = 'sa', @databasename = 'DPIN', @enabled = 1, @freqtype = 4, @freqinterval = 1, @freqsubtype = 1, @freqsubinterval = 0, @freqrelativeinterval = 0, @freqrecurrencefactor = 1, @activestartdate = 19980101, @activeenddate = 99991231, @activestarttimeofday = 220000, @activeendtimeofday = 235959, @runpriority = 0, @emailoperatorname = null, @retryattempts = 0, @retrydelay = 0, @loghistcompletionlevel = 2, @emailcompletionlevel = 0, @command = 'dump transaction qsdb to diskdump with truncate_only', @tagadditionalinfo = null, @description = null, @tagobjectid = 0, @tagobjecttype = 0, @cmdexecsuccesscode = 0
GO
-- Check if errors occurred during the CREATE DATABASE section
IF @@ERROR <> 0
BEGIN
INSERT INTO tempdb..SQLerrorTracking (errCode,errDesc) VALUES(@@ERROR,'')
PRINT 'Error: ' + convert(char,@@ERROR) + ' occurred.'
END
GO
-- Check if errors occurred during the CREATE DATABASE section
IF @@ERROR <> 0
BEGIN
INSERT INTO tempdb..SQLerrorTracking (errCode,errDesc) VALUES(@@ERROR,'')
PRINT 'Error: ' + convert(char,@@ERROR) + ' occurred.'
END
-- Check if any errors occurred during the script. If none occurred, write a statement
-- for the DBInstaller application to tell that the script was executed.
IF NOT EXISTS(SELECT * FROM tempdb..SQLerrorTracking WHERE errCode <> 0)
PRINT 'SQL Script Executed successfully'
ELSE
BEGIN
PRINT 'SQL errors occurred in the script.'
SELECT * FROM tempdb.SQLerrorTracking WHERE errcode <> 0
END
GO
-- Drop the error tracking table
DROP TABLE tempdb..SQLerrorTracking
PRINT 'script DPINDatabase.sql complete.'
-- CREATE DATABASE STRUCTURE section ends here >>>>
Thanks in advance.
Regards
Prashant
-
Just posting the error
Temporary table tempdb.SQLerrorTracking created.
Create database part starting...
Database DPIN does not exist.
.Net SqlClient Data Provider: Msg 1038, Level 15, State 1, Line 1
An object or column name is missing or empty. Verify each column in the SELECT INTO statement has a name. For other statements, look for empty alias names. Aliases defined as " " or [] are not allowed. Add a name or single space as the alias name.
.Net SqlClient Data Provider: Msg 911, Level 16, State 1, Line 14
Could not locate entry in sysdatabases for database 'DPIN'. No entry found with that name. Make sure that the name is entered correctly.
.Net SqlClient Data Provider: Msg 15010, Level 16, State 1, Procedure sp_dboption, Line 60
The database 'DPIN' does not exist. Use sp_helpdb to show available databases.
.Net SqlClient Data Provider: Msg 208, Level 16, State 1, Line 10
Invalid object name 'msdb..systasks'.
Error: 0 occurred.
SQL Script Executed successfully
script DPINDatabase.sql complete.
I shall debug and post the script with correctness sooner.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
actually I was going to start a new thread althoug it's same topic but with different questions. However I couldn't find where or how.
My questions are (maybe sound stupid
): 1. we are going to migrate sql 2000 databases to sql 2005, for example, using backup and restore method. Shall we migrate all the system databases?
2. I was told there were customized templates in sql 2000, the model database has to be migrated. is this same as user databases migration?
3. since there were databases for web application open to the public, we were considering change all windows authentication to sql authentication, should we still need migrate the logins? or modify then recreate them?
Thanks for advice in advance,
Sherry
-
After the restore of the SQL2000 database to SQL2005 .. I ran the sp_change_users_login command to sync up the user login Id with the ID on the database but it doesn't seem to work the same was as it did in SQL2000. I can't delete the ID in the database because there are schema's attached to it .. any suggestions would be helpful. Thx
-
i would not restore any system db's from sql 2000 to 2005
in my case i had a staging server to hold logins, dts, jobs, etc. for logins i just input the passwords again and ran sp_change_users_login
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply