Upload local backup bak file to restore remote DB? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Upload local backup bak file to restore remote DB?

Author

Topic

coffeejunkie
Starting Member

2 Posts

Posted - 2011-11-30 : 07:18:23

Hi. I am trying to do something that i've never done before. i have an online database which is empty, and i need to make it an exact copy of the one i have locally. i'm using SQL server management studio, and i never had trouble backing up and restoring, but today for the first time i managed to use management studio to connect to the online server and see my empty database.
when i go to TASK->RESTORE->DATABASE-> click "from device" and add backup source i get an error saying:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.
If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

when i click OK on that error alert box, it just gives me a blank page where i'm supposed to locate the backup file but no folders are displayed. i tried to enter the link manually (the path to the ftp server where i have uploaded a backup file previously) and it gave me an error saying it can't find the file specified, BECAUSE IT'S LOOKING FOR A BACKUP FOLDER ON A C: DRIVE, and it just added my manually-entered URL at the end of "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup"
but the problem is, there is not C: drive on my online server!
please help how do i point management studio to the right place where my backup is located, either local or online?

sandy123
Starting Member

14 Posts

Posted - 2011-11-30 : 12:40:46

You can just change the DB name in below query and run. you will get the exact location of backups.

SELECT b.physical_device_name, a.backup_set_id, b.family_sequence_number, a.position, a.backup_start_date, a.backup_finish_date
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.database_name = 'DBNAME'
AND a.type = 'D'
ORDER BY a.backup_start_date, b.family_sequence_number

sandy

coffeejunkie
Starting Member

2 Posts

Posted - 2011-11-30 : 13:13:23

no i got 0 results when running it from management studio while connected to the remote server. besides, i know where the backup is located, is on my local C: drive in the backup folder of microsoft sql server. the DB i want to restore is on a remote server. when i connect to the remote DB with management studio, i get no explorer to bring me to a location of the local backup.

quote:
Originally posted by sandy123

You can just change the DB name in below query and run. you will get the exact location of backups.

SELECT b.physical_device_name, a.backup_set_id, b.family_sequence_number, a.position, a.backup_start_date, a.backup_finish_date
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.database_name = 'DBNAME'
AND a.type = 'D'
ORDER BY a.backup_start_date, b.family_sequence_number

sandy

steve_r18
Yak Posting Veteran

59 Posts

Posted - 2011-12-01 : 13:03:57

Just to confirm, you want to restore a DB to a server that is local to you. So you connect into the remote server's MS and can't access the DB to do a restore?

quote:
Originally posted by coffeejunkie

no i got 0 results when running it from management studio while connected to the remote server. besides, i know where the backup is located, is on my local C: drive in the backup folder of microsoft sql server. the DB i want to restore is on a remote server. when i connect to the remote DB with management studio, i get no explorer to bring me to a location of the local backup.

quote:
Originally posted by sandy123

You can just change the DB name in below query and run. you will get the exact location of backups.

SELECT b.physical_device_name, a.backup_set_id, b.family_sequence_number, a.position, a.backup_start_date, a.backup_finish_date
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.database_name = 'DBNAME'
AND a.type = 'D'
ORDER BY a.backup_start_date, b.family_sequence_number

sandy

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources