Restore a database in oneserver from a backup file in another server

• pvsrpk

  SSChasing Mays

  Points: 602

  More actions

  July 16, 2009 at 7:18 am

  #202578

  Hi all,

  I had a database backup database1.bak in one server and i need to restore it to some other database in a different server.

  I dont want to copy the database backup file between the servers.

  How to Restore a database in oneserver from backup file in another server?

  Can any one suggest a method (using TSQL or SSIS package).

  Thanks in advance.

  pvsrpk 🙂

• Kev Riley

  SSCrazy Eights

  Points: 9075

  More actions

  July 16, 2009 at 7:50 am

  #1025655

  What is the reason for not copying the backup file?

  The data will have to traverse the network anyway.

  Can you access the first server from the second, i.e. via a mapped/net use drive?

  If so then it's a simple RESTORE DATABASE databasename FROM DISK = 'X:\path\path\database1.bak'

  Kev

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  July 16, 2009 at 8:18 am

  #1025683

  Sample restore command with backup on network share:

  restore database MyDatabase

  from disk =

  '\\MyServer\MyBackupShare\MyDatabase_db_200907160525.BAK'

  with

  move 'SYS' to 'D:\MSSQL\Data\MyDatabase_SYS.MDF' ,

  move 'DATA1' to 'D:\MSSQL\Data\MyDatabase_DATA1.NDF' ,

  move 'DATA2' to 'D:\MSSQL\Data\MyDatabase_DATA2.NDF' ,

  move 'DATA3' to 'D:\MSSQL\Data\MyDatabase_DATA3.NDF' ,

  move 'LOG' to 'H:\MSSQL\Data\MyDatabase_LOG.LDF' ,

  replace, stats = 5 , recovery