Restore DB from a backup on a Remote location

SQL Server Administration
,
1

Hi guys, I hope this mail finds you all well. I'm in a very tight situation, and I need some guidance, not sure if this is possible:
Problem: I have 999GB of storage on my test sever and I need to restore a backup with 1.2GB worth of data, that is stored on a remote location that i can access from the test server, and i have mapped this drive onto the server. Getting extra space on this drive is proving to be a mission, so I need to come back with another solution.

Suggestion; would it possible to restore the DB straight from this remote location, as well as store the mdf on this drive as well, as space on this remote location is not a problem.

please assist ASAP.

2

very confusing to me.
ServerA 999gb
ServerB ?? where the 1.2gb located at?
one point you say you mapped a drive to remote server which has space issues and then you say remote server has no space issues.

3

Ok. Let me rephrase.
Server A has only 999GB of disk space, I have a backup of 1.2Tb that is stored on a remote location (B), outside of the Server A. From Server A i have access to the remote location(B), and I have mapped this drive B on Server A. If I attempt to restore the backup I get space issues, so what I wanted is to restore the backup and store the mdf directly on B and not locally on A. Does it make sense now?

4

so when you say remote how remote is this ServerB? physically/virtually at another location? or in the same building?

5

is Remote virtually, but same network. The storage is not located locally on the server, is on a SAN that is accessible from server A

6

try something like this

alter database your_database set   RESTRICTED_USER  with rollback immediate;
			
RESTORE DATABASE your_database FROM  DISK = N'D:\RestoreDB\12TB.bak' WITH  FILE = 1,  
MOVE N'your_database ' TO N'D:\DATA\your_database.mdf',  
MOVE N'your_database_log' TO N'D:\LOGS\your_database.ldf',  NOUNLOAD,  REPLACE,  
STATS = 10
			
alter database your_database set  multi_user with rollback immediate;
7

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\Remedy2\ARsystem-Full Database backup_04.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

8

The account running SQL Service must have permissions on the server where the backup is. It also has no mapped drive D: so that might explain it. Try using the network path to the backup file like

FROM DISK ='\\ServerB\BackupFolderShare\Backupfile.bak'
1 Like
9

You cannot use a mapped location or UNC path for a database file - that option will not work. Also - the fact that the backup is 1.2TB means the data file (mdf) and the transaction log (ldf) combined total at least 1.2TB.

Since database backups only contain the data - the actual file sizes can be much larger. And even worse - if the database is compressed the actual size can be as much as 70% larger than the size of the backup file.

You really only have one solution - you need to get more space assigned to Server A. The amount of space assigned should match Server B at a minimum, depending on the purpose of this new server.

1 Like
10

Cool, thanks Jeff.