Home > IT Answers > SQL Server > SQL Backup File
0 pts.
 SQL Backup File
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?

Software/Hardware used:
ASKED: October 27, 2008  5:52 PM
UPDATED: June 24, 2009  5:34 PM
RELATED QUESTIONS
Answer Wiki:
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’.
Last Wiki Answer Submitted:  October 28, 2008  1:30 pm  by  Karl Gechlik   9,815 pts.
All Answer Wiki Contributors:  Karl Gechlik   9,815 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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.

Denny Cherry
 64,505 pts.
 

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.

Hydra
 0 pts.
 

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

1109
 10 pts.
 

Thanks 1109, we are now splitting our backup to 20 files. Using robocopy to copy the files over makes it much easier.

Hydra
 0 pts.