Restoring a backup SQL Server 2005 where is the data stored?

Question

I have two Sql Server database instances on two different machines across the network. Lets call these servers A and B. Due to some infrastructural issues, I had to make a complete backup of the database on server A and robocopy the A.bak over to a shared drive accessible by both A and B. What I want is to restore the database on B.

My first issue is to restore the backup on server B but the backup location does not display my shared drive.

My next issue is that server B's C: drive has barely any space left and there are some additional partitions that have more space and can house my backup file but I am not sure what happens to the data after I restore the database on B. Would the backup data fill up all the available space on C:?

It will be great if somebody explain how the data is laid out after the restore database is initiated on a target database server?

Thanks

score 1 · Accepted Answer · answered Jan 14 '11 at 14:44

If I remember correct, it's been a while, but SQL won't recognize a network share as a restore location if the account running SQL doesn't have permission (ie, is a local user account). Copy the backups to a local partition that has more space.

When doing restores using the GUI, you can specify where the files for the data, and logs will be restored to. When selecting the backup file, see the "Options" tab, and it should list the original path, and will allow you to change the destination path. Move these to a new location with additional disk space. Alternatively, you can use the restore tsql statement:

restore database yourdbname
  from disk 'm:\sqlbackups\yourbackup.bak'
  with norecovery,
  move 'yourdbname_data' to
    'f:\_sql\data\yourdbname_data.mdf',
  move 'yourdbname_log' to
    'f:\_sql\logs\yourdbname_log.ldf'

yourdbname_data and yourdbname_log are names of the "logical names" in the files list. If you have multiple files (you should have at least 2), then repeat the move statement for each line.

Thanks a lot. What is interesting is that I am using the same permissions as the one on the network drive to run sql. I was wondering what might be the issue there? — sc_ray, Jan 14 '11 at 15:07
@sc_ray Native SQL restore will only read off of local volumes when restoring. Other tools such as Quest's LiteSpeed and RedGate's SQL Backup will give you the ability to restore from network shares. — squillman, Jan 14 '11 at 18:42

Michael Eklöf · Answer 2 · 2011-01-14T18:38:52.547

0

If you have enabled xp_cmdshell in sp_configure, as described in this MSDN article you can use it to mount your file share:

xp_cmdshell 'net use x: \\server\share /user:<username> <password>'

You will then be able to access the file share in Management Studio.

edited Jan 14 '11 at 18:38

answered Jan 14 '11 at 18:33

Michael Eklöf

519
4
6

This is not true. Native SQL restore will not provide mapped drives as options for restore sources. Only local volumes are candidates for a restore source. – squillman Jan 14 '11 at 18:41
Hi squillman. I just verified my answer sucessfully on a server running SQL Server version 9.0.4053 as follows: xp_cmdshell 'net use x: \\server\share /user: ' GO RESTORE DATABASE [test2] FROM DISK = N'X:\.bak' WITH FILE = 1, MOVE N'MOM_DATA' TO N'D:\Default_DBData\test2.mdf', MOVE N'MOM_LOG' TO N'E:\Default_DBLog\test2_1.ldf', NOUNLOAD, STATS = 10 GO – Michael Eklöf Jan 14 '11 at 18:59
Sorry about the formatting on the comment, also, this was on a system with no third party utilities for backup/restore installed. – Michael Eklöf Jan 14 '11 at 19:10

score 0 · Answer 3 · answered Aug 21 '11 at 20:49

You can do a restore using T/SQL and specifying the network share location. This does require that the SQL Account that runs the SQL Server has rights to the network location. Simply specify the network location of the backup file.

RESTORE DATABASE MyDatabase from DISK='\\NetworkServer\Share\Folder\file.bak'

If you need to change where the physical files are stored then use the MOVE parameter.

RESTORE DATABASE MyDatabase from DISK='\\NetworkServer\Share\Folder\file.bak'
WITH MOVE 'LogicalFile' TO 'D:\folder\file.mdf',
MOVE 'LogFile' TO 'E:\folder\file.ldf'

Restoring a backup SQL Server 2005 where is the data stored?

3 Answers3