Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
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.
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.
