Database copy issue through - COPY DB WIZARD

  • I have a production server with SQL server 2016 version (13.0.5026.0) and a database with 25 GB of Data and 525 tables in it.

    Now, I want to transfer this production DB to my development environment with SQL server 2016 version (13.0.5026.0). I am using DB copy wizard for DB copy to the new instance with remain the existing DB remaining online as it's a production server.

    Also as it's a huge file size of the main.mdf file at the source Db server, which is also creating a barrier to transferring this into a new instance

    In brief: Can't copy DB through Copy Database wizard & huge size, and can't transfer the main .mdf file

    Error message: Failed - Invoked on: 2023-05-31 06:10:19 - Message: The job failed. The Job was invoked by User sa. The last step to run was step 1

    Can anyone give me any suggestions?

    Attachments:
    You must be logged in to view attached files.
  • Restore from a backup file instead.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • The backup file also generates at the source instance with the same 25 GB size.

    Do you have any suggestions on how can I transfer the backup file to the new server ?

  • If it's not compressed, you could try zipping it first. But one way or another, the data has to get from A to B. Is there a reason why you cannot simply copy it over the network?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • It is a client's production Windows server. And there is no FTP or any file-sharing service available. Then how can I copy the backup file to the new server ?

  • There is no UNC based access between the two machines?

     

    \\ServerB\backups\copy

    Like this?

  • Kaushik Parui wrote:

    It is a client's production Windows server. And there is no FTP or any file-sharing service available. Then how can I copy the backup file to the new server ?

    COPY <Your local file> \\DesrinationServerName\SharedFolderYouCreate\

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Hi,

    you can backup db on production and copy .bak file to dev env and then restore it there?

    You can compress db bak file, zip it and then copy it to dev

    or

    If  Internet available at prod server, upload it thru google drive and download it from dev server (Internet availability)

    • This reply was modified 1 year ago by  SkyBVI.

    Regards
    Sushant Kumar
    MCTS,MCP

  • SkyBVI wrote:

    If  Internet available at prod server, upload it thru google drive and download it from dev server (Internet availability)

    I would NOT do that.  That will likely violate rules that the company may need to adhere to.  In my case, that would result in termination.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Ant-Green wrote:

    There is no UNC based access between the two machines?

    \\ServerB\backups\copy

    Like this?

    To elaborate, on the destination server, there may be a folder that is shared that you can access using the UNC path.  If there is not a shared folder, you can create one and share it to your login.

    If you have administrative rights on the destination server, you can use the admin share.  \\DestinationServer\c$\A folder\

    Copying a backup from one server to another is a very basic part of a DBA's responsibilities.  Actually any administrator's responsibilities.

    You really need to learn how to do these things.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • If you have no file-sharing options (i.e. no SMB) and no FTP, then one alternative is WinRM (Powershell Remoting).  Enabled by default on modern Windows Servers, but of course might be blocked by local firewall rules, firewall devices, policies, who knows what.

    Example script:

    # name of the remote server

    $myComputer = "nameofserver"

    # check WinRM port is open; TCP ports 5985 (HTTP) and 5986 (HTTPS)

    Test-NetConnection -ComputerName $myComputer -Port 5985 -InformationLevel Detailed

    Test-NetConnection -ComputerName $myComputer -Port 5986 -InformationLevel Detailed

    # create a persistent session on the remote computer

    $myCred = Get-Credential

    $session = New-PSSession -ComputerName $myComputer -Credential $myCred

    # generic copy: copy an entire folder; creates new destination folder; if destination folder exists, continues without error;

    # if a file exists, error will be thrown; use -FORCE to always overwrite

    $srcFolder = "C:\tempfiles\database\"

    $destFolder = "D:\temp\"

    Invoke-Command -Session $session -ScriptBlock { Get-ChildItem $Using:destFolder -Recurse }

    Copy-Item -Path $srcFolder -Destination $destFolder -ToSession $session -Recurse # copy files from local to destination; existing destination files will cause an error

    Copy-Item -Path $srcFolder -Destination $destFolder -ToSession $session -Recurse -Force # overwrite existing destination files

Viewing 11 posts - 1 through 10 (of 10 total)

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