search

Backup and restore sql database from one server to another

1 years ago
Comments: 0
Views: 68

  • Home
  • Databases
  • Microsoft SQL Server

Posted by RGee

Show

I am trying to restore a SQL backup and my backup is located on a different server. I know I can easily move the file but I was wondering, if there is a way to write a query to grab the file  from another server?

14 Replies

  • Backup and restore sql database from one server to another

    That's restoring a backup from the same server... I need to restore from a backup that is located on another server.

    Was this post helpful? thumb_up thumb_down

  • Basically all you need to do is use RESTORE <database name> FROM DISK = <unc path to backup file> and  the WITH MOVE option.  The tricky part is making sure the SQL Server service account has the correct share and NTFS permissions on the remote server.

    SQL Server RESTORE command.

    1 found this helpful thumb_up thumb_down

  • Backup and restore sql database from one server to another

    Alec6638

    Backup and restore sql database from one server to another

    This person is a verified professional.

    Verify your account to enable IT peers to see that you are a professional.

    thai pepper

    Microsoft SQL Server Expert

    • check 129 Best Answers
    • thumb_up 309 Helpful Votes

    The GUI doesn't allow the use of network drives or UNC paths, however the TSQL command line does work with such.

    RESTORE DATABASE [MyDatabaseName] FROM DISK = N'\\servername\sharename\backupfilename.bak' WITH REPLACE

    Was this post helpful? thumb_up thumb_down

  • Backup and restore sql database from one server to another

    So basically  I can set up a job but cant run that query?

    Was this post helpful? thumb_up thumb_down

  • One point here:  Was your backup taken from your local server and simply stored remotely or was the backup taken from a SQL Server instance on the remote server and you're effectively looking to make a copy of that database on your local instance?

    Was this post helpful? thumb_up thumb_down

  • Backup and restore sql database from one server to another

    Larry Shanahan wrote:

    One point here:  Was your backup taken from your local server and simply stored remotely or was the backup taken from a SQL Server instance on the remote server and you're effectively looking to make a copy of that database on your local instance?

    The backup was taken from a remote location and stored is being dumped there weekly. However, the SQL version that is on the Server isnt compatible with the backup source. Therefore, I am having to move the file to another server to restore.

    I cannot upgrade the server that the file is dumped to yet and it just makes the most sense to automate the process of the restore to another server...

    Was this post helpful? thumb_up thumb_down

  • Backup and restore sql database from one server to another

    I am getting an error that the network path is not valid.

    RESTORE DATABASE [Datebase Name]
    FROM DISK = N'\\ServerName\d$\foldername\filename.bak' WITH REPLACE

    Was this post helpful? thumb_up thumb_down

  • Okay, first thing you should do is run this in a query in SSMS:

    SQL

    RESTORE FILELISTONLY from disk=<unc path to backup file>;

    That will show you where the engine is expecting the restored MDF and LDF files to be.

    If they will match precisely, you can use RESTORE <database> FROM DISK=<unc path to backup file>;.

    If not, you will need to use the WITH MOVE option to specify where the files will go on the local server.  Also use the REPLACE option Alec mentioned if the database already exists on the local server.

    You also need to be sure the SQL Server service account has permissions to the location of the backup file on the remote server.  Depending upon what account is used, it may not be one that is allowed to access network resources (which is usually the case).

    1 found this helpful thumb_up thumb_down

  • It is sort of is the same thing. Make a backup and then restore it on the other server. You will be asked if you want to restore into the current database. This is the method I use to migrate my Sharepoint.

    https://community.spiceworks.com/how_to/35831-migrate-from-sharepoint-3-0-2007-to-sharepoint-2010

    Was this post helpful? thumb_up thumb_down

  • Denis Kelley wrote:

    It is sort of is the same thing. Make a backup and then restore it on the other server. You will be asked if you want to restore into the current database. This is the method I use to migrate my Sharepoint.

    https://community.spiceworks.com/how_to/35831-migrate-from-sharepoint-3-0-2007-to-sharepoint-2010

    The OP is asking about restoring directly from a backup file on a remote server without copying it locally.

    Was this post helpful? thumb_up thumb_down

  • Backup and restore sql database from one server to another

    Perhaps, using the D$ share (or an admin share) is likely causing you issues. Your agent account for sql is likely running in the context of the current server's system account or some other similar canned account. it will likely require that you grant permission to server$ (the computer account of that sql server) to the root of D on the destination if you are going to use that path.

    Alternatively, you could go to that  "foldername" and share it out granting permission to the computer account that your other sql server is running on and it will not be an admin share. 

    you could also test it by using a common admin level or sufficiently granted access level account on both systems to run as the agent account (meaning in sql config manager change the account that sql agent runs as to one that most definitely can open that full path at a command line (perhaps your account) . then run your test.

      

    Was this post helpful? thumb_up thumb_down

  • Backup and restore sql database from one server to another

    grantpe

    Backup and restore sql database from one server to another

    This person is a verified professional.

    Verify your account to enable IT peers to see that you are a professional.

    jalapeno

    The problem you'll likely to be getting is that the SQL restore runs with the permissions of the SQL service is running as - by default a local user. It's the same reason it won't normally restore from inside your user folder on the server.

    If you really want to do this or will need to do it often, create a new account to run SQL as in your AD (I assume both servers are on the same AD!) and change the SQL server service to run as that account and ensure it has permission to the share. Or if this is occasional (or there's other security concerns) then just copy that file.

    Remeber that when you change the service user, it doesn't take effect until you stop and restart the service.

    Was this post helpful? thumb_up thumb_down

  • Alec6638 wrote:

    The GUI doesn't allow the use of network drives or UNC paths, however the TSQL command line does work with such.

    RESTORE DATABASE [MyDatabaseName] FROM DISK = N'\\servername\sharename\backupfilename.bak' WITH REPLACE

    Just FYI you can use UNCs through the UI, you just can't navigate to them directly via the UI. You have to paste the full UNC in the file name box and hit OK. But for automation your way is best. Just wanted to throw that out there. 

    Was this post helpful? thumb_up thumb_down

Read these next...

  • Backup and restore sql database from one server to another

    Looking for a simple CCTV cam which can be accessed through a URL for staff

    Security

    Hello,I am looking to get a CCTV came, wifi connected and battery operated (or with USB charging) which we can use to overlook over office parking lot.Our staff would like to see the parking lot in the evenings so they can decide to leave a bit early or l...

  • Backup and restore sql database from one server to another

    How do you get windows to require a password after timeout??

    Windows

    OK,  I am losing my mind here.I have set up GP to turn on screen saver after 10 minutes, and to password protect. It does not work. It does launch the screen saver, but it does not require a password. I then tried to set the security policy of Inactivity ...

  • Backup and restore sql database from one server to another

    Spark! Pro Series - October 14th, 2022 - The Spice it Up and Win Contest

    Spiceworks Originals

    The buzz has been building for weeks and finally the time has come. Everyone likes to win once in a while, right? Even those who would never admit it, have a deep-seated desire to win at something. Last piece of the jigsaw puzzle, best dish at the compa...

  • Backup and restore sql database from one server to another

    Snap! Magniber ransomware, Linux desktop, RTX 4090 priority program, Brain Cells

    Spiceworks Originals

    Your daily dose of tech news, in brief. How is Friday already here? Not that I'm complaining, but this week has seemed to fly by. Speaking of time flying by, did you realize that it has been 65 years since the British Computer Society (BCS) was inc...

  • Backup and restore sql database from one server to another

    Snap! MSFT's SUG, CommonSpirit Health cyberattack, Cyber Expo, RTX 4080, etc.

    Spiceworks Originals

    Your daily dose of tech news, in brief. While I believe the first commercial cellular network was launched in 1979 by Nippon Telegraph and Telephone (NTT), on October 13, 1983, the first commercial wireless phone call was made (in the U.S.). It was...

backup restore sql database server

Related Posts

Restore oracle database from rman backup to another server
Restore oracle database from rman backup to another server

Hardwired smoke detector with 10 year battery backup
Hardwired smoke detector with 10 year battery backup

How to recover deleted whatsapp photos without backup
How to recover deleted whatsapp photos without backup

How to transfer all photos from google photos to gallery
How to transfer all photos from google photos to gallery

How to move photos from samsung gallery to google photos
How to move photos from samsung gallery to google photos

How to retrieve deleted whatsapp messages on iphone without backup
How to retrieve deleted whatsapp messages on iphone without backup

4ft led battery backup emergency stairwell light
4ft led battery backup emergency stairwell light

Advertising

LATEST NEWS

Bone in skin on chicken thighs in air fryer
1 years ago . by ComfortingClearance
Nike air force 1 high top white
1 years ago . by IndelibleHeadset
What is a newborn screening test for
1 years ago . by TouchyMajority
How many calories in a packet of sugar
1 years ago . by FantasticFreestyle
How to cook a sweet potato in an air fryer
1 years ago . by ReceptiveNourishment
The best wrist brace for carpal tunnel
1 years ago . by Eight-hourDrilling
How to setup wifi for brother printer
1 years ago . by WeeklyPrecedence
Whirlpool 4 door refrigerator not making ice
1 years ago . by UtterEncampment
How much money is taken from my paycheck
1 years ago . by Down-to-earthAppendix
What are normal blood oxygen levels during exercise
1 years ago . by AmusingPneumonia

Advertising

Populer

Advertising

About

Legal

Help

Social

homeendefrjakoptzhhiittr
Copyright © 2024 paraquee Inc.