Moving Databases

[billum]

Hi ,

I have a new server and I want to move all the SQL databases,logins ,jobs etc to the new server
while retaining all in the old server,that is the old
server need to run as it is .I have backups all of
the databases, more than 250 dbs,but they have the
date as the suffix of the .bak files along with their
database names.Please help me as to how to do this

Thanks
Bill

 

[JayKusch]

Were all of the DBs created on the same day. That being all of the .bak files you have start w/ the same date prefix. Can you give us an example of a .bak file.

Thanks

J. Kusch

 

[SQLBill]

Can you take the old server down for a while (offline)?

If so, then the EASIEST way to do this is to detach all the databases on the old server, COPY all the .mdf and .ldf files to the new location, then on the old server - attach each of the databases and do the same on the new server.

BTW-which version of SQL Server do you have? Is it the same on both servers?

-SQLBIll

 

[JayKusch]

SQLBill in deed has the easiest and fastest way to get those DBs moved and reattached. Here is a job that will script ALL of your jobs on the old server to a text file. Run that text file in Query Analyzer on the new server and you are one more step closer.



BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Script All Jobs')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Script All Jobs'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Script All Jobs'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Script All Jobs', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Script Jobs Step', @command = N'

Dim conServer
Dim fso
Dim iFile
Dim oJB
Dim strJob
Dim strFilename
Const ioModeAppend = 8

Set conServer = CreateObject(&quot;SQLDMO.SQLServer&quot
conServer.LoginSecure = True
conServer.Connect &quot;Stage_DB&quot;

strFilename = &quot;C:\JOBS.sql&quot;

For Each oJB In conServer.JobServer.Jobs
strJob = strJob & &quot;--------------------------------------------------&quot; & vbCrLf
strJob = strJob & &quot;-- SCRIPTING JOB: &quot; & oJB.Name & vbCrLf
strJob = strJob & &quot;--------------------------------------------------&quot; & vbCrLf
strJob = strJob & oJB.Script() & vbCrLf
Next
Set conServer = Nothing

Set fso = CreateObject(&quot;Scripting.FileSystemObject&quot
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing


', @database_name = N'VBScript', @server = N'', @database_user_name = N'', @subsystem = N'ActiveScripting', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:



Thanks

J. Kusch

 

[billum]

Hi,

Thanks for the info.The bakfiles have same date but time may be different like test200309270126 which indicates that this backup was taken at 1.26 on 27th sep.I have sql 2000 on both servers.

I will try detach and attach and see whther that will do the purpose.

Thanks
Bill

 

[SQLBill]

Also, make sure both SQL Server are at the same 'level'.

For instance, if the backups were done with SQL Server 2000 at SP2. You may not be able to restore them to a SQL Server 2000 at SP3. First restore them, then do any patches/upgrades.

-SQLBill