December 9, 2021 at 8:27 pm
I'm trying to restore a Sql2012(Std) backup to my Sql2017 Env(Std) and getting the error above. I have done a dbcc checkdb on the source db and everything looks fine. The Log for the backup from Source shows it completed without errors. I can run a
RESTORE HEADERONLY FROM DISK = 'Z:\Backups\VSSQL2\Backupfile.bak' and everything looks fine
I watched restore get to 30% and then fail. The backup is a 45gig file that is using compression for backup as original db is 245gig on the SQL 2012 box.
any thoughts or things to try.
thx
December 9, 2021 at 10:07 pm
As a thought - is it a native backup or a 3rd party tool (like redgate) backup? Is the backup encrypted? Can you restore the backup to another 2012 STD instance (or the same one)? Can you make a new, empty database on 2012, make a backup and restore it to the 2017 one?
Restore error on page to me sounds like the backup is corrupt, or the method you are using to transfer the backup between machines (assuming it is 2 different machines) is not valid.
This post has a lot of information on that error but it sounds to me like you are trying to restore a corrupt backup:
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 10, 2021 at 1:44 am
It just a native SQL backup process.
December 10, 2021 at 12:53 pm
Would there be any advantage(speed) to strip my backup even though my drive that I'm backing up to is part of a San
where it's not on it own storage location.
I guess what I'm asking would there be any advantage to a single 45gig file versus 5 9gig files if it's all located on say drive "F' the server, and Drive "F" on the San is in he same pool as the database files themselves.
Thanks.
December 10, 2021 at 1:49 pm
Would there be any advantage(speed) to strip my backup even though my drive that I'm backing up to is part of a San where it's not on it own storage location.
I guess what I'm asking would there be any advantage to a single 45gig file versus 5 9gig files if it's all located on say drive "F' the server, and Drive "F" on the San is in he same pool as the database files themselves.
Thanks.
that has nothing to do with your original question about the error - please stop doing things like this - a thread is for one query and if you really need to ask a unrelated question create a new thread.
regarding this particular question - most time yes - but as with everything related to SQL YOU have to test it with different combinations and see which ones are better for you - in most cases on my org 4 files is better but we do have a few big db's where 8 performs better.
but all the performance is heavily dependent on your machine setup (CPU/RAM/Network/Disk) and what is good for 1 machine may not be good for another - hence the TEST TEST TEST you need to do yourself instead of asking others what their opinion is.
December 10, 2021 at 2:09 pm
I agree with Fredrico_Fonseca on the performance question - it depends on a LOT of factors and the EASIEST way to know if it helps or not is to test it out on your system. Plus, like Fredrico_Fonseca said, adding new questions/problems to a thread often results in no replies from anyone who may know the answer. For example, Had Fredrico_Fonseca seen that there were replies to this and thought "meh, someone else is helping on that thread already" and decided to ignore it, my response would have been "test it out and see". Something you should ALSO test though is how breaking the backup into multiple files affects restores. For me, I want my restore to be as fast as possible as I have downtime windows for doing backups and maintenance as long as it doesn't cause outages (ie the company doesn't run overnight, so I can do processes that slow down the database, but I don't want to be woken up at 2:00 AM due to a database going offline expectedly and not coming back online).
Now, I see you answered my question about native backup vs 3rd party, but what about the rest of the questions? "Is the backup encrypted? Can you restore the backup to another 2012 STD instance (or the same one)? Can you make a new, empty database on 2012, make a backup and restore it to the 2017 one?"
And a question that I thought was implied but maybe not - how are you moving the backup from the machine with 2012 to the machine with 2017? If you are using an invalid method (like a home grown app optimized for transfers of text files) or a misconfigured method (FTP with ASCII transfers instead of BINARY for example), you may have problems. If they are on the same machine, I would also try restoring the backup onto the 2012 instance with a new database name to confirm your backup is valid. I have a suspicion that your backup is invalid which COULD indicate hardware problems on your backup drive.
TL;DR - are you sure your backup is valid and not corrupt? When was the last time you tested the backup?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 10, 2021 at 2:55 pm
I was asking the question of a stripped backup because maybe Transferring the 45gig caused a problem. I thought maybe smaller files
could be moved quickly and eliminate file moving issue. I'm going from Win2012 machine to One-Drive then over to the Sql2017 box.
Thx.
December 13, 2021 at 6:27 pm
I was asking the question of a stripped backup because maybe Transferring the 45gig caused a problem. I thought maybe smaller files could be moved quickly and eliminate file moving issue. I'm going from Win2012 machine to One-Drive then over to the Sql2017 box.
Thx.
Can you make a bogus database in SQL 2012, run a backup, and then successfully restore it?
Are you restoring from enterprise to standard?
And, why the extra steps? What can't you simply restore from the original backup location, or, run the backup directly to the 2017 server?
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/
December 13, 2021 at 6:59 pm
I took a smaller db backup from the STD2012, and was able to restore to my SQL2017 instance. I can't move the backup to the sql2017 Box as it would transverse the Network and kill it. I need a fast way to copy the DB backup over to the sql2017 box, thats why I trying to split the backup files into smaller files.
December 13, 2021 at 7:21 pm
My opinion, if you want to toss the full backup on onedrive in smaller chunks, I would include MD5's with the backups.
I am a bit confused what you mean by "it would transverse the network and kill it"... pushing it up to OneDrive would "transverse" the network too, right? Just you MAY have slower connection to the cloud and back. I know my internet speeds are nowhere NEAR the 1 Gbps I can get keeping it all on-site and my IT team would likely get grumpy if I started pegging our internet upload and download bandwidth for a 45 GB file.
Now, if you ARE breaking it down into multiple smaller files, I would definitely want some hash to verify the backup is the exact same on both boxes. My opinion the advantage to the smaller files is if you have 1 45 GB file and it is a corrupt download, you need to download the whole 45 GB file. If you have 45 1 GB files and you know that 1 of those 45 files has a bad hash, you can just download that 1 file again much faster than a 45 GB file.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 13, 2021 at 7:53 pm
I took a smaller db backup from the STD2012, and was able to restore to my SQL2017 instance. I can't move the backup to the sql2017 Box as it would transverse the Network and kill it. I need a fast way to copy the DB backup over to the sql2017 box, thats why I trying to split the backup files into smaller files.
Huh? I'm not following. Copying a backup from one server to another would kill the network, but copying it to One-Drive and then back down won't? The file is only 45 GB.
I do suggest you stripe the backups to 4 (or more) files.
I would also loo0k into possibly resource throttling the backups, which could eliminate the network issues.
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/
December 13, 2021 at 7:56 pm
I currently have it busted out to 5 9-gig files.
December 15, 2021 at 6:27 pm
Using the 5 9gig files the Restore worked on the SQL2017 STD box.
Thanks.
December 21, 2021 at 11:44 am
This was removed by the editor as SPAM
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply