May 31, 2023 at 2:54 pm
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?
May 31, 2023 at 2:59 pm
Restore from a backup file instead.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 31, 2023 at 3:02 pm
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 ?
May 31, 2023 at 3:06 pm
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 31, 2023 at 3:49 pm
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 ?
May 31, 2023 at 3:53 pm
There is no UNC based access between the two machines?
\\ServerB\backups\copy
Like this?
May 31, 2023 at 3:53 pm
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/
May 31, 2023 at 4:00 pm
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)
Regards
Sushant Kumar
MCTS,MCP
May 31, 2023 at 4:27 pm
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/
May 31, 2023 at 4:31 pm
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/
June 19, 2023 at 12:45 pm
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