Backup Script - 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

SQL Server Administration (2005)

Backup Script

Author

Topic

langdu
Starting Member

9 Posts

Posted - 2007-10-03 : 10:18:14

Hi All,

I would like to backup a full database everyday, let's say at midnight. I use statement BACKUP DATABASE pubs TO DISK = 'c:\backup\pubs.bak'. My question is how I attach a time to the backup file, for example c:\backup\pubs_200710022400.bak (yyyymmddhhmimi)

Thanks.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-03 : 11:52:35

you could use tara's backup script http://weblogs.sqlteam.com/tarad/archive/2007/02/26/60120.aspx

that probably does everything you want.

-ec

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-03 : 11:54:32

DECLARE @path sysname
set @path='C:\backup\pubs_'
+Cast(datepart(yyyy,getdate())as varchar(4))
+RIGHT('00' + Cast(datepart(mm,getdate())as varchar(2)),2)
+RIGHT('00' + Cast(datepart(dd,getdate())as varchar(2)),2)
+RIGHT('00' + Cast(datepart(hh,getdate())as varchar(2)),2)
+RIGHT('00' + Cast(datepart(mm,getdate())as varchar(2)),2)
+'.bak'
BACKUP DATABASE pubs TO DISK =@path
GO

Future guru in the making.

Haywood
Posting Yak Master

221 Posts

Posted - 2007-10-03 : 13:43:48

A timestamp similar to the Maintenance Plan output can be generated easily enough (but carries out to the milisecond for more accuraccy)...

DECLARE @TimeStamp char(17)

SET @TimeStamp = (SELECT REPLACE(CONVERT(varchar(10),getdate(),101),'/','')
+ REPLACE(CONVERT(varchar(12),getdate(),114),':',''))

Tack that onto your string and away you go. I use this format for my alert & t-log backup subsystemm...

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-03 : 22:26:45

Or use maintenance plan.

langdu
Starting Member

9 Posts

Posted - 2007-10-04 : 05:07:04

Thanks for your guides. I am successful with them.

I put them in a backup.sql file, then call backup.sql file by command line (C:\>OSQL -Usa -Ppassword -SNODE-31886\TEST -n -iC:\backup\backup.sql).

The output is 'Processed 216 pages for database 'pubs', file 'pubs' on file 1.
Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.
BACKUP DATABASE successfully processed 217 pages in 0.557 seconds (3.178 MB/sec).'

I would like to save that output in a text file for record such as C:\backup\result_200710022400.txt (yyyymmddhhnn). What do you think ?

tincupal
Starting Member

24 Posts

Posted - 2007-10-04 : 12:20:32

I recently implemented database maintenance plans for SQL 2000 via the SQL Enterprise Manager. The plan created individual SQL server agent jobs. You can set the frequency and time for each job. Each job has its own log file and messages are also entered into the SQL log file. I suggest using the GUI interface instead of the manual jobs/SQL commands.

Al

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-04 : 12:26:56

quote:
Originally posted by tincupal

I suggest using the GUI interface instead of the manual jobs/SQL commands.

Do you have good reasons for this or just lack of experience with custom maintenance routines?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-10-05 : 11:00:25

@tincupal

Some of the problems I have had with SQL 2000 Maintenance Plans are described in this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62868&SearchTerms=Unreliable+behaviour

langdu
Starting Member

9 Posts

Posted - 2007-10-09 : 03:48:33

quote:
Originally posted by tkizer

quote:
Originally posted by tincupal

I suggest using the GUI interface instead of the manual jobs/SQL commands.

Do you have good reasons for this or just lack of experience with custom maintenance routines?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Hi Tara,
Enterprise works perfect in SQL Server 2000 but not in SQL Server 2005. It says: Method not found: 'Void Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIUtils.ctor()', (Microsoft.SqlServer.MaintenancePlanTasksUI). Unfortunately, I could not install or update on our company's server.
Thanks.

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 04:29:24

"Enterprise works perfect in SQL Server 2000"

if you are referring to the Maintenance Plans (rather than general usefulness of Enterprise manager!!) you should take a read of my link above

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources