SQL Backup File

pts.
Tags:
Microsoft SQL Server 2000
SQL Server backup
I have two computers running sql server, sqlserver01 and sqlserver01. On sqlserver01 I backup the database locally to C:SQLBackupbackupfile.bak (db size: 150GB) then I copy and paste the *.bak file to sqlserver02 via the network. It finishes copying but when I try to restore it on sqlserver02 it fails. I have used tools like Eseutil from exchange but no luck. It seems like the bak file corrupts on the transfer. What is the best way to copy over a 150GB *.bak file to another server then restore the *.bak file in a timely manner?

Answer Wiki

Thanks. We'll let you know when a new response is added.

when restoring a .bak file to a SQL Server database where the .bak file was not originally backed up from, right click –> All Tasks –> Restore Database –> From device –> Select Devices –> Add your .bak file. Click OK. Click the Options tab. Now on the left side you should see the Logical file name of the database from the .bak file. On the right side under the label ‘Move to physical file name’, should be the path that the .bak file originally existed as. This field needs to be changed to the path & file name of the database you’re restoring too. After modifying those two fields, check the ‘Force restore over existing database’ and click ‘OK’.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information. What's the error message that you get when you are restoring the database? The odds of the file getting corrupt when being copied from one server to another is almost 0.
    66,190 pointsBadges:
    report
  • Our answer to our problem was log shipping. We could not transfer over a 240 GB bak file over our network it would fail. So instead of transfering a 240 GB file we just transfer the log backup and restore them on the secondary server.
    0 pointsBadges:
    report
  • 1109
    You could split the backups by running SQL queries sonhting like this: --Backup backup Database "PREPAYDEV" to disk = 'F:BackupTestPREPAYDEV_DB101.bak', disk = 'F:BackupTestPREPAYDEV_DB102.bak', disk = 'F:BackupTestPREPAYDEV_DB103.bak', disk = 'F:BackupTestPREPAYDEV_DB104.bak', disk = 'F:BackupTestPREPAYDEV_DB105.bak', disk = 'F:BackupTestPREPAYDEV_DB106.bak' with init -- Move them to the destination server then Restore it. Make sure you change the MDF and LDF file location accrodingly. --Restore restore DATABASE RestoreTest from disk = 'F:BackupTestPREPAYDEV_DB101.bak', disk = 'F:BackupTestPREPAYDEV_DB102.bak', disk = 'F:BackupTestPREPAYDEV_DB103.bak', disk = 'F:BackupTestPREPAYDEV_DB104.bak', disk = 'F:BackupTestPREPAYDEV_DB105.bak', disk = 'F:BackupTestPREPAYDEV_DB106.bak' with replace, move 'PREPAYPRD_DATA' to 'F:MSSQL.1MSSQLDATARestoreTest.MDF', --'PREPAYPRD_DATA' from the source move 'PREPAYPRD_LOG' to 'F:MSSQL.1MSSQLDATARestoreTest_log.LDF' -- 'PREPAYPRD_LOG'from the source
    10 pointsBadges:
    report
  • Thanks 1109, we are now splitting our backup to 20 files. Using robocopy to copy the files over makes it much easier.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following