Backup Database 1

[scon44]

Ok Thanks for reading...

So I want to automate a procedure to backup my database. I have the command to backup it up. Now my question is: How do I use a UNC path that would require a username and password?

Like for instance right now when I run the below it can save on the local drive "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\feb8.bak"

BACKUP DATABASE [Dynamo] TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\feb8.bak' WITH NOFORMAT, NOINIT, NAME = N'Dynamo-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

What I'm looking to do is save it to "\\10.0.0.5\backup" but since I'm not logged in as a domain user I need to supply a username and password to be get the file on that other server.

What syntax do I follow? Also a follow up to that is how do I automate this statement in an stored procedure or scheduled sql task, so when I'm logged out it will take care of it's self.

Thanks!

 

[ptheriault]

you're going to need two files to do this.
1st Create a file named backup.sql with your backup command in it. Then create a bat file with a the following syntax.

isql -Uuser -Ppassword -Sserver -i "d:\path to .sql" -o "d:\path to output file.out"

If this is 2005 you will use sqlcmd instead of isql.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

I forgot to add, you will exec the bat file from windows NT schedule tasks. I would put these files on the server that you want the backup on. 10.10.0.5?

- Paul
- If at first you don't succeed, find out if the loser gets anything.