1

I currently a have a db backup, which is very big. So big that I can't restore my db on the same drive.

Is it possible to delete the backup while it is being restored? Such that the new db takes up the space which the backup file was taking?

My backup is currently on our prod server, and there is a link between our test and prod environments. Could I somehow expose that? Is it possible to restore my db over the net?

3 Answers 3

2

Yes, you can restore over the net. RESTORE DATABASE .. FROM FILE = '\server\share...'. I.e., an UNC path. Note that the service account for the SQL Server service need permissions on that share.

1

No, you need the restore to complete before deleting the bak file.

Would recommend you perform a compressed SQL backup if you need additional space, but this is SQL Edition dependent, plus not sure how much space you have or require, but could provide you with the additional space you need. Also, we have no clarity on the data types you store, so compression might not save you all that much space in the end.

Alternative, move the bak to a remote server and restore over network.

4
  • FWIW, compressed backups are in Standard Edition as of 2008 R2. In 2008, it required Enterprise Edition. Jun 18, 2018 at 8:06
  • correct, as mentioned in my reply "but this is SQL Edition dependent" Jun 18, 2018 at 8:07
  • What access credential would i need? just an account that has access to that drive or?
    – nano
    Jun 19, 2018 at 7:36
  • As I mentioned in my other post, the account used by the SQL Server service need permissions to create a file on that share/folder. Jun 19, 2018 at 8:19
0

There is the ability to perform piecemeal restores. With these each filegroup is backed up to a separate bak file. They can be restored one after another. So only one backup file need be on the target server at any point in time. Also you can bring the DB online with only some of the filegroups restored, reducing downtime in real DR. There are many restrictions, however and it can be fiddly to get right.

I understand this may not be applicable for you immediate needs, since you have an existing backup file. If you are able to refactor the backup procedure it may help in the future.

Your Answer

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

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