September 13, 2010 at 6:23 am
Hi,
We are currently using sql server 2005 in production. We are planning to use sql server 2008 with cluster. We installed the server. I want to use my current msdb and master database with any change with jobs users maintance plans etc. Please help me about this situation.
Omer Colakoglu
September 13, 2010 at 6:37 am
you should probably think more about scripting 'out' your jobs and logins and then scripting them back 'in' to the new server. everything you need to move can be scripted. logins, jobs, linked servers.... right click on the object on the 2005 server and choose 'Create to... new query editor window'
on the new query window, change your connection to the new 2008 server and run the script.
September 13, 2010 at 6:39 am
i have alrady tried the scripting job below but i got the error like,
"Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137
Cannot insert the value NULL into column 'owner_sid', table 'msdb.dbo.sysjobs'; column does not allow nulls. INSERT fails.
The statement has been terminated.
"
USE [msdb]
GO
/****** Object: Job [BACKUP DURDUR] Script Date: 09/13/2010 14:53:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 09/13/2010 14:53:57 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'BACKUP DURDUR',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'INDEX ÇALILIYORKEN BACKUP DURDURUR',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'ERPMERKEZ580\Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
September 13, 2010 at 6:43 am
does this person exist yet on the new 2008 server ?
ERPMERKEZ580\Administrator
if not, either add them OR change your scripted out job with the proper login.
September 13, 2010 at 6:52 am
Yep you are great. I didnt care that the server names are different. I changed the erpmerkez580 to new server and i works. Thank you for your atention:)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply