August 24, 2006 at 12:13 pm
We're copying our SQL Server 2000 instance and placing it on a new server. What would be the best way to do this so we get everything: ( jobs, databases, users, etc). Any articles would greatly be appreciated.
Ed.
August 24, 2006 at 1:14 pm
Best way would be step by step.
Assuming you don't have replication (dist/pub/sub) configured. If you do, things can get a bit trickey. Books on line has a section describing how to backup / restore DB's under replication.
Also, assuming you don't have fulltext enabled and running. That is registry dependent and dependent on DBID's. That can get trickey too if you have to move fulltext without rebuilding the indexes. If you can rebuild the indexes from scratch then this shouldn't be an issue.
backup restore user databases. run update statistics on each db.
backup restore msdb. (if replication is setup, again this might be trickey)
Use DTS's Transfer Users Task to move the users.
run sp_configure on both computers and compare. adjust accordingly
August 24, 2006 at 1:15 pm
and don't forget to run sp_change_users_login after restoring and transfering the userlogins.
August 24, 2006 at 7:29 pm
I am running a few fulltext indexes. What we did so far was to stop the services copy the files and move them over to the new server and restart the services (old server is working just fine). Things have appeared to work on the new server, except that it's not allowing us to run over port 1433. It says it's because we have a vulnerability but we are 100% we have run all of the patches. The fulltext indexes are small so I can reindex them individually if necessary.
Thanks,
Ed.
August 25, 2006 at 8:22 am
I have used this method for moving User Databases between Instances with great success:
First I Copy the Users (with same SID and Password) using the sp_help_revlogin Stored Procedure - Instructions found here:
http://support.microsoft.com/kb/246133/EN-US/
Recreate all User Databases on the new Server:
Take a full Backup of all the User Databases (using SQL Server Maintenance plans) on the Source Server then move all of these .BAK files to A Folder on the Destination Server.
Create a Stored Procedure that will Restore all Users DBs on the New Server from a .BAK File:
On the Destination Server, I create a Database called ADMIN and then run this code against the ADMIN Database:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CSS_RestoreDir]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_CSS_RestoreDir]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE proc sp_CSS_RestoreDir
@restoreFromDir varchar(255),
@restoreToDataDir varchar(255)= null,
@restoreToLogDir varchar(255) = null,
@MatchFileList char(1) = 'N',
@OneDBName varchar(255) = null
as
--If a directory for the Log file is not supplied then use the data directory
If @restoreToLogDir is null
set @restoreToLogDir = @restoreToDataDir
set nocount on
SET quoted_identifier on
declare @filename varchar(255),
@cmd varchar(500),
@cmd2 varchar(500),
@DataName varchar (255),
@LogName varchar (255),
@LogicalName varchar(255),
@PhysicalName varchar(255),
@Type varchar(20),
@FileGroupName varchar(255),
@Size varchar(20),
@MaxSize varchar(20),
@restoreToDir varchar(255),
@searchName varchar(255),
@DBName varchar(255),
@PhysicalFileName varchar(255)
create table #dirList (filename varchar(100))
create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20) )
--Get the list of database backups that are in the restoreFromDir directory
if @OneDBName is null
select @cmd = 'dir /b /on "' +@restoreFromDir+ '"'
else
select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'
insert #dirList exec master..xp_cmdshell @cmd
select * from #dirList where filename like '%_db_%' --order by filename
if @OneDBName is null
declare BakFile_csr cursor for
select * from #dirList where filename like '%_db_%bak' order by filename
else
begin -- single db, don't order by filename, take default latest date /o-d parm in dir command above
select @searchName = @OneDBName + '_db_%bak'
declare BakFile_csr cursor for
select top 1 * from #dirList where filename like @searchName
end
open BakFile_csr
fetch BakFile_csr into @filename
while @@fetch_status = 0
begin
select @cmd = "RESTORE FILELISTONLY FROM disk = '" + @restoreFromDir + "\" + @filename + "'"
insert #filelist exec ( @cmd )
if @OneDBName is null
select @dbName = left(@filename,datalength(@filename) - patindex('%_bd_%',reverse(@filename))-3)
else
select @dbName = @OneDBName
select @cmd = "RESTORE DATABASE " + @dbName +
" FROM DISK = '" + @restoreFromDir + "\" + @filename + "' WITH "
PRINT ''
PRINT 'RESTORING DATABASE ' + @dbName
declare DataFileCursor cursor for
select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize
from #filelist
open DataFileCursor
fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize
while @@fetch_status = 0
begin
if @MatchFileList != 'Y'
begin -- RESTORE with MOVE option
select @PhysicalFileName = reverse(substring(reverse(rtrim(@PhysicalName)),1,patindex('%\%',reverse(rtrim(@PhysicalName)))-1 ))
if @Type = 'L'
select @restoreToDir = @restoreToLogDir
else
select @restoreToDir = @restoreToDataDir
select @cmd = @cmd +
" MOVE '" + @LogicalName + "' TO '" + @restoreToDir + "\" + @PhysicalFileName + "', "
end
else
begin -- Match the file list, attempt to create any missing directory
select @restoreToDir = left(@PhysicalName,datalength(@PhysicalName) - patindex('%\%',reverse(@PhysicalName)) )
select @cmd2 = "if not exist " +@restoreToDir+ " md " +@restoreToDir
exec master..xp_cmdshell @cmd2
end
fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize
end -- DataFileCursor loop
close DataFileCursor
deallocate DataFileCursor
select @cmd = @cmd + ' REPLACE, STATS = 10'
--select @cmd 'command'
EXEC (@CMD)
truncate table #filelist
fetch BakFile_csr into @filename
end -- BakFile_csr loop
close BakFile_csr
deallocate BakFile_csr
drop table #dirList
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I didn't write the code and honestly I don't remember who I got it from - but it's pretty slick. It creates a Stored Procedure which when run, will look for any .BAK files in a Folder which you specify (the backups need to be a FULL Backup created with a SQL Server Maintenance plan), will create the DB(s)
(if it doesnt' already exist) and restore Database(s) from the most recent backup - even if the backup is from another Server.. You can use this Method to restore one DB or 60 DBs.. I love it! I use it to restore One DB on Test from Production all the time.. We even use it at our DR site to recover ALL of our User Databases quickly!
Once you run the code to create the sp_CSS_RestoreDir Stored Procedure in the Admin Database, execute the Stored Procedure with your own parameters..
For Example....
This will restore any .BAK files created with SQL Server Maintenance plans found in the C:\sqldb\SQL_Backup folder and place the Data and Log Files in their own Folders:
exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', 'C:\sqldb\sql_data', 'C:\sqldb\sql_log'
Note that It will create the DB if it doesn't already exist!!!!
Once the Logins and DBs are restored - I run this:
EXEC sp_change_users_login 'Auto_fix', 'username' against all of the SQL logins to fix any orphaned users. I have found that doing it in this order will restore all user permissions and the only other thing you might need to fix is specific users default database.
As far as the System Databases, you may be able to use this article for moving them between Instances:
http://www.sqlservercentral.com/columnists/chedgate/movingsystemdatabasesachecklist.asp
Hope this helps a little : )
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply