Take the backup to Shared disk

• Lavanyasri

  SSCarpal Tunnel

  Points: 4907

  More actions

  June 20, 2012 at 3:55 am

  #392562

  Hi ,

  i want to take the full database backup. but i don't have enough space to take the backup in present server ,so i am planning to take the backup to other server [both are in the same domain] at the time of backup.

  i treid with the below command :

  BACKUP DATABASE [database name] TO

  DISK =N'\\servername\C$\DBBackup\databasename.bak' WITH NOFORMAT, INIT,

  NAME = N'databasename-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,

  STATS = 10,copy_only

  But i am getting the error.

  Could you please suggest me how to take the backup to other server shared folder.

  1. By using UNC

  2. By using linked server

  Thanks

  Lavanya

• Suresh B.

  SSC-Insane

  Points: 22986

  More actions

  June 20, 2012 at 4:03 am

  #1503305

  You cannot take backup using linked server. Linked server is meant for querying only.

  What error did you get?

  I guess, it was related to permission.

  Service account should have write access to this share.

  Try to take backup to a shared folder instead of C$:

  BACKUP DATABASE [database name] TO

  DISK =N'\\servername\ShareName\DBBackup\databasename.bak' WITH NOFORMAT, INIT,

  NAME = N'databasename-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,

  STATS = 10,copy_only

• Johan Bijnens

  SSC Guru

  Points: 135375

  More actions

  June 20, 2012 at 5:15 am

  #1503325

  please don't use the C$ share for this purpose ! It is supposed to be for administrative stuff.

  Create your own backup share on your target server, grant the service account write auth on that share and user the share in your file definition.

  disk='\\yourserver\yourbackupshare($)\yourdbname.bak'

  Keep in mind, using copy_only sqlserver will not maintain its backup chain with regards to your backup.

  ( I guess/hope you put it there for testing purposes )

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  June 21, 2012 at 10:22 am

  #1504059

  Suresh B. (6/20/2012)

  ---

  You cannot take backup using linked server. Linked server is meant for querying only.

  That is actually not true, although a Linked Server will not do what the OP was probably imagining it would do in their scenario:

  EXEC('BACKUP DATABASE [msdb]

  TO DISK = N''C:\msdb.bak''

  WITH NOFORMAT,

  NOINIT,

  NAME = N''Full Database Backup'',

  SKIP,

  NOREWIND,

  NOUNLOAD,

  STATS = 10') AT [LinkedServerName];

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  June 21, 2012 at 10:27 am

  #1504061

  ALZDBA (6/20/2012)

  ---

  please don't use the C$ share for this purpose ! It is supposed to be for administrative stuff.

  Create your own backup share on your target server, grant the service account write auth on that share and user the share in your file definition.

  disk='\\yourserver\yourbackupshare($)\yourdbname.bak'

  Agreed. Create a backup share and stop using C$.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events