SQL Server Restore

Question

Post reply

SQL Server Restore

PJ_SQL

SSCertifiable

Points: 6703
More actions
April 6, 2022 at 11:08 pm

#4011889

Hello!
I am trying to restore 3 TB of existing database, there is still 300GB free space on the drive.
Since I am doing restore with replace, it should overwrite the existing DB and the the space available should not be concern (I do not need additional 3 TB of space if I try to overwrite) isn't that correct?
RESTORE DATABASE DBName FROM DISK = 'C:\DBName.BAK'
WITH REPLACE
GO
Thanks in advance.
April 19, 2022 at 8:59 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

My suggestion is that you REALLY need to STUDY RESTORE in the official documentation. Here's the link...

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

There are several caveats and possible gotchas and if you've not done a restore before, you should probably practice it a few times on something smaller and that won't matter if you screw it up.

I don't know the reason why you're trying to do this restore nor the cause for the need but do you know what a "tail log" backup is, for example?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 2

I am trying to restore prod backup to test instance, so overwriting it should not be a problem.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

PJ_SQL wrote:

I am trying to restore prod backup to test instance, so overwriting it should not be a problem.

Ok... first of all, remember that none of the privs you established for the other people in the Dev version are coming along for the ride so you should make sure you have all those in a save place. If you've never done this before, remember that synonyms and "pass through views" might be pointing to objects in other databases that simply aren't there and the same can hold true for linked servers, etc. To be honest, I'd make a backup of the dev database just to be extra sure. you can recover from something unforeseen.

Also remember that your prod database files may life on different drives in different directories and so may not work on your Dev box unless you use the MOVE directives in the restore whether by GUI or by code.

If that's all ok (and I might have missed some things because I'm a bit tired tonight), then one of the first things that SQL Server does during a restore is to figure out if there's enough room and tell you if there isn't. You should be OK.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 4

As an alternate suggestion, you could drop the existing database and then do the restore as then you should have the disk space. This is the approach I usually take as it ensures I do have the disk space prior to starting the restore. Step 1 of MOST of my restores is to run an SP_WHO2 to check if anyone is using the system and step 2 is to drop all databases that I plan to restore. Then I do the restore followed by any post-restore steps (master key encryption, syncing users and logins, shrinking databases, adjust autogrow settings, etc). Shrink doesn't always occur but we have some databases that have 100's of GB of empty space and the developers are generally only adding or changing a few rows when doing some dev/test work, so freeing up those few hundred GB and setting autogrow to 10-50 MB rather than several GB means that we have more free space on the disk and don't need as much disk for test/dev environments.

But to add to what Jeff said, any objects referenced that are not part of the restore will fail. This can (but doesn't always) include logins and server roles. Database roles and users will come across as they are database objects. But, like Jeff said, linked servers and anything that relies on cross database query will break UNLESS the other database still exists. One thing that is likely to break is any encryption on the database. You will need to know the password to your master key so you can alter it to add encryption by service master key - if you use that, you may only use the password or may not even care about the database master key for anything.

And, like Jeff said, if you have the disk, I'd back up the dev system before the restore to make sure you can get back to the current state. If you blow out a months worth of some devs work because you forgot to let one dev know OR one dev forgot to let you know, and a project is now delayed by a month, you are the one they will be coming to.

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.