Moving Large Database
-
HELP! I need to move a 52 GB database to a different SQL server. I am planning on detaching, copying the .mdf over to the new server and then attaching, but have been reading horror stories about getting large file copies to work. Can anyone suggest a method of making the copy of the 52 GB file go as quickly and successfully as possible (or other means of moving the database altogether)?
All suggestions/comments are welcomed.
Thanks,
Anne
-
I haven't had any problems moving files of this size over a network (it just takes a lot of time), but that really depends on the quality of your LAN/WAN, network cards, utilization of your servers, etc. Look into xcopy (with the /Z setting) so you can restart the transfer if it fails in the middle.
-
If this is a one time shot, you should be ok just copying. Be aware that the progress bar in Windows Explorer may go haywire, showing incredibly large positive/negative numbers. Ignore it and let it copy.
If your database has a lot of free space in it, you might consider shrinking it first, or alternatively, take a backup and copy the backup file.
Finally, you might take a look at some of the 3rd party backup compression utilities, especially if this is something you're going to do regularly. LiteSpeed from IMCEDA, SQL Safe from Idera, or SQL Backup from Red Gate are all good products and will reduce the size of the backup considerably, and do it in less time than a native backup takes.
Steve
edit - Co-incidentally, I'm currently testing the products I mentioned on a 54GB database.
SQL Safe and SQL Backup both backed up the database in a little over 10 minutes, (I don't have the time for the native backup on this server yet). The native restore took an hour and 20 minutes. My SQL Backup restore just completed, and it took 28 minutes. The backup size for both products is approximately 6.2GB, and could be compressed quite a bit more at the expense of backup speed.
-
I faced the same problem 2 months ago, when i moved 287 GB of database to our new production server.
Here are the steps you can follow.
1. Truncate LOG files of the database.
2. Take full backup of the database.
3. Shrink Data and Log files.
4. Deattach database.
5. Copy files to destination.
6. Attach Database.
7. Create Logins for every user in database.
8. Map those logins with the orphaned users in Database.
9. Backup System Database on new Server.
10. DONT worry
-
Somewhere here there's a script for dealing with the User logins and the orphaned users on the new server. Maybe someone else will point it out to you.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
-
One script to accomplish this is in the scripts section under something like orphan users. Here's a copy:
declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
select @command=' sp_change_users_login ''auto_fix'', '''+@usrname+''' '
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs
-
As hoo-t mentioned earlier by utililizing SQL LiteSpeed you could do a backup ( backup using SQL LiteSpeed could be around 8-10GB) and then you could do a restore over the network (latest version of SQL LiteSpeed supports that) or do a copy and restore.
-
Hi,
This is just a login idea. If I move SQL logins to a new server then I use 3 parameters to sp_addlogin: login name, password and SID that I get from restored database's sysusers that contains the source server's SIDs. This way logins are created with SIDs that are already matched.
Yelena
Regards,Yelena Varsha
-
Yelena,
I started to mention a little earlier that I have a stored procedure that will copy the login, password (encrypted), and SID and create a script that you can cut and paste into the new server. No more orphaned logins. This was just a script til this morning, when I made a stored proc out of it, then saw this thread. Its written to go into my "afDBA" database, so modify accordingly...
-- af_Migrate_Login
USE afDBA
IF EXISTS
(SELECT *
FROM sysobjects
WHERE id = object_id(N'[dbo].[af_Migrate_Login]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[af_Migrate_Login]
GO
CREATE PROCEDURE af_Migrate_Login
( @name sysname )
AS
SET NOCOUNT ON
/*************************************************************/
--
-- Module Name: af_Migrate_Login
--
-- Description:
-- n input parm(s).
-- @name sysname : loginid that you wish to migrate
-- n output parm(s).
-- @name format : description
--
-- Procedure description.
-- This script was pulled out of af_LogShipping_Synch_Logins. Its purpose
-- is to pull a login from the production server, and create a script that
-- can be run against the standby server to duplicate the login (using the same sid)
-- before log shipping is set up. It was created primarily to migrate the
-- LogShipper login on the standby server, but can be used to migrate any login.
-- Populate the @name variable with the login you wish to migrate.
-- Written By: Steve Phelps
--
-- Date: May 19, 2005
--
-- Modified :
-- Date:
--
-- USAGE:
--
-- exec af_Migrate_Login @name
--
/*************************************************************/
-- the following declare must be removed. its here for testing.
-- DECLARE
-- @name format
-- SELECT
-- @name = 'logshipper'
-- end of test logic
DECLARE
@xstatus int,
@binpwd varbinary (256),
@txtpwd sysname,
@tmpstr varchar (1000),
@SID_varbinary varbinary(85),
@SID_string varchar(256),
@ncommand nvarchar(2000),
@nparmlist nvarchar(250)
SET @txtpwd = ''
-- Get password, SID, and status for Login from the production server.
SET @ncommand = 'SELECT
@SID_varbinary = sid,
@xstatus = xstatus,
@binpwd = [password]
FROM master.dbo.sysxlogins
WHERE srvid IS NULL
AND [name] = @name'
SET @nparmlist = '@SID_varbinary varbinary(85) OUTPUT, @xstatus int OUTPUT, @binpwd varbinary(256) OUTPUT, @name sysname'
EXEC sp_executesql @ncommand, @nparmlist,
@SID_varbinary OUTPUT,
@xstatus OUTPUT,
@binpwd OUTPUT,
@name
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END -- IF (@xstatus & 1) = 1
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END -- IF (@xstatus & 1) = 1
END -- IF (@xstatus & 4) = 4
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
SET @tmpstr = 'DECLARE @pwd sysname '
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = @tmpstr + 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ') '
ELSE -- IF (@xstatus & 2048) = 2048
BEGIN
SET @tmpstr = @tmpstr + 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ') '
END -- IF (@xstatus & 2048) = 2048
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = @tmpstr + 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END -- IF (@binpwd IS NOT NULL)
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END -- IF (@binpwd IS NOT NULL) ELSE CLAUSE
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE BEGIN -- IF (@xstatus & 2048) = 2048
SET @tmpstr = @tmpstr + '''skip_encryption'''
END -- IF (@xstatus & 2048) = 2048 ELSE CLAUSE
END -- IF (@xstatus & 4) = 4
PRINT @tmpstr
-
Thanks everyone for your help! Although I really am more interested in the moving of the file than I am concerned on fixing the logins - any more ideas in that arena?
Thanks again,
Anne
-
52 Gb is not that large. based on all of the discussion the most efficient method seems to be:
1) backup the transaction log --> of course I'd also do a full backup just in case !!!
2) shrink the database data portion
3) shrink the database transaction log
4) detach the database
5) copy the .mdf & .ldf files
6) attach the database
7) alter the database trasnaction log to it's perferred size/growth
8) alter the database data portion to it's preferred size/growth
The time intensive operations are #2 & #5. I have no way of guessing on #2. However on #5 if you've got a gigbit backbone the file copy should be no more than 5-10 minutes. On 100 base-T I'd guess about 45 minutes. All in all from start to finish I'd guess that your downtime could range from 2 to 5 hours.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply