backups via dts - 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

Import/Export (DTS) and Replication (2000)

backups via dts

Author

Topic

rjung
Starting Member

1 Post

Posted - 2005-05-17 : 05:28:55

Hi folks, i made a dts-script to backup my databases,
but i cant restore my createt db :-(

declare
@backupFile nvarchar(100),
@MyDBName nvarchar(100),
@MyDBPath nvarchar(100),

@yearStr nvarchar(4),
@monthStr nvarchar(2),
@dayStr nvarchar(2),
@hourStr nvarchar(2),
@minuteStr nvarchar(2),
@secondStr nvarchar(2)

set @MyDBName = 'sgs_online'
set @MyDBPath = 'k:\database\'

set @yearStr = year(getdate())
set @monthStr = month(getdate())
set @dayStr = day(getdate())
set @hourStr = datepart(hour,getdate())
set @minuteStr = datepart(minute,getdate())
set @secondStr = datepart(second,getdate())

if (@monthStr < 10) begin set @monthStr ='0'+@monthStr end
if (@dayStr < 10) begin set @dayStr ='0'+@dayStr end
if (@hourStr < 10) begin set @hourStr ='0'+@hourStr end
if (@minuteStr < 10) begin set @minuteStr ='0'+@minuteStr end
if (@secondStr < 10) begin set @secondStr ='0'+@secondStr end

set @backupFile = @MyDBPath+@MyDBName+'_'+@yearStr+@monthStr+@dayStr+'_'+@hourStr+@minuteStr+@secondStr+'.bak'

BACKUP DATABASE @MyDBName TO DISK = @backupFile WITH NOINIT, NoSKIP, STATS = 10

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-17 : 07:59:50

Why dts?

set @monthStr = month(getdate())
if (@monthStr < 10) begin set @monthStr ='0'+@monthStr end
easier is
select @monthStr = right('0' + convert(varchar(2),month(getdate())), 2)

in fact you could get rid of them all with
select @dte = convert(varchar(8),getdate(),112) + '_' + replace(convert(varchar(8),getdate(),108), ':','')

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources