DB Backup every night... - 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 2000 Forums

SQL Server Administration (2000)

DB Backup every night...

Author

Topic

kiran
Starting Member

30 Posts

Posted - 2003-08-08 : 17:29:29

I have a production environment running SQL 2000. I want to take a complete backup of the database every night to a file and store it on a different machine.

Currently I am doing manually using "Backup" option. Is there any way I can schedule this by writing a script or something or use DTS ?

Thanks in advance....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-08 : 17:42:21

Just schedule your command in a job in SQL Server Agent. You could use the maintenance plan wizard as well, but you'll have to add a job step for the copying to another machine part.

In the job, create a second step that copies the backup file to a different machine. You could use the copy command using xp_cmdshell.

So your job could look like this:

First step:

BACKUP DATABASE DBNameGoesHere
TO DISK = 'E:\Somepath\Somefile.bak'
WITH INIT

Second step:

EXEC master.dbo.xp_cmdshell 'copy E:\Somepath\Somefile.bak \\SomeServer1\Someshare\Somedirectory\'

Tara

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-08-08 : 19:53:17

I think you can also map the Network Share as a Drive and backup directly to it. I'm pretty sure I've made that work before.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

kiran
Starting Member

30 Posts

Posted - 2003-08-08 : 20:40:15

I ran this command :

BACKUP DATABASE test
TO DISK = 'c:\Somepath\Somefile.bak'
WITH INIT

somefile.bak file is getting created but the size is showing 0 bytes. But when I do a complete backup using "Backup" option in the SQL Enterprise Manager, i see the file of 10KB.

Do I need to add more options to the above command for Complete backup?

Thanks in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-08 : 20:47:09

Are you sure the backup is complete?
Try restoring from the file.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kiran
Starting Member

30 Posts

Posted - 2003-08-08 : 20:49:59

Sorry, i did not mention my whole script in the last reply, this is what i am doing

DECLARE @filename VARCHAR(300)
set @filename = 'E:\test\testdb_'+ cast(getdate() AS VARCHAR) + '.bak'
BACKUP DATABASE test
TO DISK = @filename
WITH INIT

Can I do like this , to create files with a new name everyday?

What mistake i am making here for the files getting created with 0 bytes ?

Thx again

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-08 : 21:13:25

Yep.
You might want to change the date format though to something like yyyymmdd_hhmm

see http://www.nigelrivett.net/BackupAllDatabases.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-11 : 12:20:02

quote:
Originally posted by MichaelP

I think you can also map the Network Share as a Drive and backup directly to it. I'm pretty sure I've made that work before.

Doing it this way though isn't recommended. The recommended approach is to backup to the local disk then copy that file to the other location.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources