Hi guys,

I need to immigrate the databases on my old SQL Server to a new one. My idea is to do a backup of the databases on a shared network drive,and then do a database restore on the new SQL server from the shared network drive. This network drive is mapped on both servers as F:\warehouse\storage\SQLbackup.

However, I could do backup for the old SQL server, but when I tried to restore database on the new SQL server, it could not see the mapped network drive. There were only two "Backup Device Location" for me choose: C and D, both local drives on the server.

Any idea?

Cinderella

Please refer to the following artile
http://www.windowsitpro.com/Article/...025/14025.html

The reason is that the MSSQLSERVER service is running under a separate set of NT credentials - all services are related to an NT account. It doesn't matter who YOU are logged on as (after all SQL runs quite happily when no-one is logged on locally to the server doesn't it?). Therefore your logon account and any mapped drives are irrelevant. It is SQL Server doing the restore, not you. This is the same forrestore done via SQL Executive/SQL Agent - they just pass the TSQL to SQL Server to run, so it's still MSSQLSERVER doing the backup/restore.

For this reason the restore gui does not show you mapped drives or allow a UNC path to be typed in. You have to use raw TSQL commands to do the restore.

Change the account the MSSQLSERVER service runs under to a user account with the relevant network rights.


You MUST also use a UNC name to reference the file required and not a drive letter.

How about copying those .bak files locally and trying a restore of the same? That should be easier too, and avoid network traffic, if the backup files are huge in size. Any intermittent connectivity issues between these two systems during restore from network share would result in error.

Also remember you restore them using WITH MOVE option to restore it to different paths/drives if at all, compared to that of original.

_________________________
- Vijay G
Strive for Perfection

Thanks happygv!I could do that. Could I just drag and drop these .bak files in windows explorer?

To shahchi1: I changed my MSSQLSERVER service to run under an NT account with administrator's rights, but the restore gui still does not show my mapped drives.

Any advice?

Cinderella

Hi

Yes you can just copy your.bak files at any folder of your local m/c and then restore the DB.

Amit

Thanks a lot for your advice!

BTW, I could also do backup to the network drive by typing in the UNC path now.

Cinderella

You are right

Do not use mapped drives for SQL Server.
Use a UNC name to reach the backup location:
\\SERVERNAME\SHARENAME

Also

look for sp_addumpdevice topic in SQL Server BOL:

USE master
EXEC sp_addumpdevice 'disk', 'networkdevice',
'\\servername\sharename\path\filename.ext'

Hi Shachi,

Thanks for your post.

I tried using the sp_addumpdevice.

I ran this in QA:

Use Master
exec sp_addumpdevice 'disk', 'networkdevice', '\\vivaci\sql_backups\IntranetDev\IntranetDevTest. bak'

However, when configuring a backup (All Tasks > Backup Database...), adding a destination, I could not get SQL to find the share or the file.

In the 'Select Backup Destination' window I could not get verification that the file or path exists when adding it to either 'File Name:' or as a 'Backup Device'.

Any help on how to configure a backup to this sp_addumpdevice would be greatly appreciated.

Thank you,
Richard

Richard, just curious account under which SQL Server is running (not your windows account) have accessed to that shared resource?

Hi Shahchi,

Security is mixed mode, SQL Server and Windows.

Startup service account:

System Account

The share permissions are everyone, full control.

Is that what the info you were curious about?

Thanks,
Richard