Migration of DB from server to server

  • Hi All,

    I have recently come across a new error with little info about why this is happening.  I am currently copying and moving the mdf and log file from one server to another.  I was able to do this with several smaller sized databases. I would zip the two files, and copy/paste the compressed file to the new server.  It's a little time consuming, but it worked.
    Now I am ready to move my 2 large databases, but I am getting an error message.  The target drive has 1 TB of space and the compressed file is about 21 gb.  When I try to copy/paste, I get the following error message:

    Error copying file to folder
    Unspecified error

    Any thoughts on what I am doing wrong or is there a more efficient way of doing this?
    Thank you for you time and help

    Edited Note:  I am moving a DB from SQL 2008 to SQL 2017

  • Hmm... are you using a special program to copy it?  It might just be disliking the 21GB size.

  • I'm not sure how large your large databases are, but would you consider trying a native backup & restore instead? You could stripe the backups across multiple files and then robocopy them over, or something.

  • I believe it has to do with the size of the file.  Does anyone know if a dropbox would work for this?

  • skaggs.andrew - Tuesday, April 17, 2018 10:13 AM

    I believe it has to do with the size of the file.  Does anyone know if a dropbox would work for this?

    Using Dropbox is not safe for the database migration.

  • Evgeny Garaev - Tuesday, April 17, 2018 5:07 PM

    skaggs.andrew - Tuesday, April 17, 2018 10:13 AM

    I believe it has to do with the size of the file.  Does anyone know if a dropbox would work for this?

    Using Dropbox is not safe for the database migration.

    Thank you for that.  Any suggestions of what to use?  FTP?

  • This was removed by the editor as SPAM

  • skaggs.andrew - Tuesday, April 17, 2018 6:15 PM

    Evgeny Garaev - Tuesday, April 17, 2018 5:07 PM

    /quote]

    Thank you for that.  Any suggestions of what to use?  FTP?

    You could try RoboCopy.

  • Perhaps there is an issue with zipping a file that big.  I would try backing up (using the built-in compression) and then copying that file over to the other server and restoring.  Also, try drag and drop to avoid putting something that big in the clipboard.  Many people don't know you can right-click drag and drop to ensure you are moving rather than copying.

    You'll have to script out the database and run the script on the target server before you do the restore, and you'll want to SET COMPATIBLE on the target server to take advantage of the 2017 features.



    Del Lee

  • The Copy Database Wizard moves or copies databases and certain server objects easily from one instance of SQL Server to another instance, with no server downtime.

    Formaciones para ingenieros industriales - VITC

  • Lempster - Wednesday, April 18, 2018 1:57 AM

    skaggs.andrew - Tuesday, April 17, 2018 6:15 PM

    Evgeny Garaev - Tuesday, April 17, 2018 5:07 PM

    /quote]

    Thank you for that.  Any suggestions of what to use?  FTP?

    You could try RoboCopy.

    This is exactly what I would use,  its more resilient in the event of a failure which could be for numerous reasons like a temporary network issue.  You can even generate the robocopy.exe calls from your SQL server like so,  note the use of <> in the code to indicate what you can change.  Obviously you could change your requirements in terms of locations etc.

    use master

    IF OBJECT_ID('fnGetFileName') IS NOT NULL
        DROP FUNCTION fnGetFileName
    GO

    Create FUNCTION fnGetFileName
    (
      @fullpath nvarchar(260)
    )
    RETURNS nvarchar(260)
    AS
    BEGIN
      IF(CHARINDEX('\', @fullpath) > 0)
       SELECT @fullpath = LTRIM(RTRIM(RIGHT(@fullpath, CHARINDEX('\', REVERSE(@fullpath)) -1)))
       RETURN @fullpath
    END
    GO

    WITH CTE_metadata AS (
    SELECT CASE WHEN CHARINDEX('\', [physical_name]) > 0 THEN substring(physical_name,1,charindex(reverse(substring(reverse(physical_name),1,charindex('\',reverse(physical_name)) ) ),physical_name) ) ELSE physical_name END AS SourcePath,
            dbo.fnGetFileName(physical_name) AS FileName
    FROM sys.master_files
    WHERE DB_NAME(database_id) IN (<List Your DB's Here>)
    )

    SELECT 'START robocopy '
            + SourcePath
            + CASE WHEN [FileName] like '%LOG%' THEN ' \\<Destination server>\<Destination Drive>$\MSSQL\LOGS' ELSE ' \\<Destination server>\<Destination Drive>$\MSSQL\DATA' END
            +' ' + LTRIM(RTRIM([FileName]))
            +' /R:3 /log:?:\robocopy\log\'
            + [FileName] + '.log'

    FROM CTE_metadata

    MCITP SQL 2005, MCSA SQL 2012

  • Del Lee - Wednesday, April 18, 2018 12:26 PM

    Perhaps there is an issue with zipping a file that big.  I would try backing up (using the built-in compression) and then copying that file over to the other server and restoring.  Also, try drag and drop to avoid putting something that big in the clipboard.  Many people don't know you can right-click drag and drop to ensure you are moving rather than copying.

    You'll have to script out the database and run the script on the target server before you do the restore, and you'll want to SET COMPATIBLE on the target server to take advantage of the 2017 features.

    +1There is a limit on how big a file can be zipped even when using 7zip, however this can be avoided by zipping into several files.

    ...

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply