Restoring the Database

Question

Restoring the Database

ramana3327

SSCoach

Points: 19722
More actions
September 23, 2014 at 12:16 pm

#296823

Hi,
While doing the restore of the database is it better to copy the backup file locally and restore or restore from the network path is good option?
What kind of the problems we may get restoring the backup from the network path?

Viewing 15 posts - 1 through 15 (of 27 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Restoring from network works. Problems that can arise are related to network latency and or network hiccups that could cause the restore to take a long time or cause the restore to fail.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ramana3327 SSCoach Points: 19722 More actions · Answer 2

Correct me my understand is wrong

so compare to the copy the backup into the local and restore, it is better to restore from network

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

ramana3327 (9/23/2014)
Correct me my understand is wrong
so compare to the copy the backup into the local and restore, it is better to restore from network

No. It is better to copy local and restore in my opinion. But that also means you have to have enough storage locally for the restore. Local files can lead to faster restores. But network restores have their benefits too.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Adding to what Jason said, restores over the network are usually not affected as much by network latency as backups over the network. Whether you move to local and restore or restore over the network really depends on the network.

At a previous employer, move (or copy) local then restore was faster on the old hardware we had in place. When we moved to new hardware (blade servers and new SAN) restoring over the network dropped from 60+ minutes (30 minutes or so if copied local) to under 10 minutes over the network or local.

After you do a few you will get a feel for which is better for your environment.

ramana3327 SSCoach Points: 19722 More actions · Answer 5

Before restoring the database do we need to kill the existing connections?

Do we need to set the database property option set to restricteduser

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Restricted user just changes it so only users with elevated perms can access the database. It does not prevent multiple connections from being established and preventing the restore.

You will need to kill user connections. Commonly people use single_user mode to restore the database so long as they are establishing the single user connection in the same session to perform the restore.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ramana3327 SSCoach Points: 19722 More actions · Answer 7

I don't have enough space to copy the backup file locally to restore the database.

I need to restore from the network path only.

When I gave the network path in ssms backup media file add and gave the UNC but it is showing the path doens't exist or the current login doesn't have access to it.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Have you confirmed that the path is reachable from your server?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ramana3327 SSCoach Points: 19722 More actions · Answer 9

I gave extra .. Now it is working fine.

Thanks

But one question regarding the logins when we are restoring the database from the prod to production support still we need to script the logins and create those back after the database restore

ramana3327 SSCoach Points: 19722 More actions · Answer 10

Also I am little confuse about the keeping the database in single usermode.

If we did from the GUI to keep the database in single user mode and another window might not get access to restore. Why because in case if any another process use that db, it might be problem right?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

After you restore the database you will want to ensure it is back to multi_user mode.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ramana3327 SSCoach Points: 19722 More actions · Answer 12

I use the command

alter database Test1

set single_user

with rollback immediate;

go

Restore database Test1

from disk ='\\Test\DBdumps\test1.bak

with recovery

in the same spid

but I am getting error restore can't process the database Test1 because this is in use by this session.

It recommended that use master database to be used when performing this operation

What I have to do now?

Immediate help would be appreciate

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Alter the database and perform the restore from the master database.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Run the commands you ran while in the master database.