0

I have been tasked with moving a .bak file from a live DB, from its backup location to another server where it gets re-imported into a test environment. If I copy and paste the file, it copies fine. If I use robocopy, or Copy-Item or 7z to zip and then copy, it imports with errors. To make matters worse it is a 2003 Server where the live database sits, so getting it to backup to a networked location has proved to be difficult. Just wondering if there is a better solution, as I need to do this daily? Thanks in advance..

1
  • I really recommend upgrading. 2005 hasn't had any kind of support for over 5 years; it is long past time you finished those upgrade plans. Putting them off just makes things harder, as your only upgrade path (SQL Server 2012) runs out of support in months.
    – Thom A
    Dec 19, 2021 at 20:50

3 Answers 3

2

If I copy and paste the file, it copies fine. If I use robocopy, or Copy-Item or 7z to zip and then copy, it imports with errors. . . it is a 2003 Server where the live database sit

That is bad. You're running ancient and unsupported software, and you may have a storage issue or something else bad, and should treat that environment as possibly quite sick. And at that age, you shouldn't try to fix anything.

You should make a plan to migrate your production database to a stable and supportable environment. Not try to work around the symptoms.

1

If the test environment server has access to the production database backup, then you don't need to copy it as you can restore it directly from the test SQL Server instance.

If the test environment is segregated, then I don't know how you could do a copy & paste, unless you're using some type of remote connection software.

I don't think ROBOCOPY can compress on your version of Windows Server 2003. PowerShell's Copy-Item doesn't compress either.

So, either 7zip doesn't compress correctly, or you have some type of network issues when copying files that corrupts your ZIP file, or there are disk IO issues on the test environment IO subsystem.

You can try a different compression software, like the default included with Windows (right-click, Send to, Compressed file), or another one of your choice.

SFTP may also be an option for you.

0

I resolved this by:

  1. Updating 7Zip on the old server (Yes I know its really old and I would love to update it, not my call 😏)
  2. Using a batch file to compress as a .7z instead of .Zip (this took the transaction logs YAYY)
  3. Move-Item to new 2016 Server
  4. Unpacking file

Thanks for your help and suggestions 👍

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.