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.
| Author |
Topic |
|
Vijaykumar_Patil
Posting Yak Master
121 Posts |
 Posted - 2007-01-17 : 12:38:55
|
| Hi All,I am trying to create full backup of a database from the server to a local machine. I am not able to figure out how to give the path to folder where I need the back to sit in.Necessity is the mother of all inventions! |
|
|
agossage
Starting Member
9 Posts |
Posted - 2007-01-17 : 12:45:28
|
| The backup database functionality is a server side function. (i.e. You are requesting the server make a backup. And the server does the work) The server can only backup to devices it can see. Backup the database to a server folder, then copy the backup to your local machine via file sharing, ftp, etc.Adam GossageLake Wylie, SC, USA |
 |
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-01-17 : 15:02:24
|
You NEVER want to allow anyone to backup the server to a local machine (Its not too bad with a full backup but Tlog backups is a big no no). AS has been said already do ALL your backups to the a different disk on the server and then copy the file to the local machine (SQL 2005 has this functionality embeded with the MIRROR option of the Backup Command, as does Red Gates SQL Backup utility.Why not? If you want to do a restore to a point in time, you need the lastest full backup and all the Tran log backup. If someone has one of thes on a local machine YOU may not have access to it to do the restore. . . So you can't restore past the missing TLog BackupYou can use the xp_CmdShell extended procedure in your script to copy the file Look at Books online for adviceBACKUP MyDB TO DISK ='D:\Backups\MyDB\MyDB_db_200701172000.BAK' WITH INITDeclare @cmd Varchar (256)SELECT @Cmd = 'COPY D:\Backups\MyDB\MyDB_db_200701172000.BAK \\LOCALMachine\LocalShare\MyDB\*.*'Execute xp_cmdshell @cmd --, no_output Shows the basic principle-- RegardsTony The DBA |
|
|
|
Vijaykumar_Patil
Posting Yak Master
121 Posts |
Posted - 2007-01-18 : 06:20:59
|
| Thank you Tony . The reason why I wanted to back it up to a local machine is cause I have a DB sitting in a Server which is US , I am sitting in India, And not to say I have admin access to the SQL but not to the server to remote login. So copy file from there is out of question. I will have to work a way with xp_CmdShell extendent procedure.Necessity is the mother of all inventions! |
|
|
|
|
|
|
DataBase Backup