Backup - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Backup

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-18 : 12:23:49

I'm getting a little confused trying to implement a backup strategy for our databases. I want to back up to a mapped network drive on a nightly basis.

1. First question I right click on the database and select backup, then at the bottom when I try and specify a destination it only shows me the local hard drives, does it not support network drives?

2. Is there any way I can schedule the backup operation to run each way, I know I can script it into a stored procedure, but can the procedure itself be scheduled?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-18 : 12:34:04

BACKUP DATABASE Db1
TO DISK = '\\SomeServer1\SomeShare\Db1.BAK'
WITH INIT

Yes procedures can be scheduled.

Check out my backup stored procedure:
http://weblogs.sqlteam.com/tarad/archive/2007/02/26/60120.aspx

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-19 : 06:06:07

Okay I have this

AS
BEGIN
BACKUP DATABASE DbPubResults
TO DISK = '\\10.10.6.5\backup$\backup\database\DbPubResults.BAK'
WITH INIT
END

When I run it I get this error

Operating system error 1326(Logon failure: unknown user name or bad password.)

Is there anyway I can specify a username and password?

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-19 : 10:39:41

No, you have to start sql services with domain account that has permission on the share.

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-20 : 09:06:34

Okay thanks, got a workaround for this.

One other thing, this line

TO DISK = '\\10.10.6.5\backup$\backup\database\DbPubResults.BAK'

How can I append the current date and time to the filename, ideally in the following format however any format will be acceptable providing it can be part of a filename.

10102005-132654 (10th October 2005, 13:26:54)

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-20 : 10:52:51

You can build file name with getdate () and datepart functions.

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-20 : 11:58:07

Ah, I see, thanks.

Okay I tried this

SELECT GETDATE()

I got

20/07/2007 16:55:51

I need to get rid of the / and :, so I tried

SELECT REPLACE(GETDATE(), '/', ' ')

The result was

Jul 20 2007 4:57PM

Why did it do that?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-20 : 12:26:00

Look at the code in my link. It does this already.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources