backups via dts

Question

backups via dts

Reinhard Jung

Newbie

Points: 2
More actions
May 17, 2005 at 3:54 am

#60564

hi folks, i created the following script, but i can't restore the created db 🙁
Does anyone sees my mistake/s ? 🙁
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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47797 More actions · Answer 1

Well I simplified the code a bit. It's much easier to use the convert function to generate the required date/time format for the filename. The this code ran without a problem and I was able to restore the database as well.

declare
 @backupFile nvarchar(100),
 @MyDBName nvarchar(100),
 @MyDBPath nvarchar(100),
 @datestr nvarchar(8),
 @timestr nvarchar(8)

set @MyDBName = 'DBA'
set @MyDBPath = 'D:\SQLDATA\'

SET @datestr = CONVERT(varchar(12), GETDATE(), 112)
SET @timestr = LEFT(REPLACE(CONVERT(varchar(12), GETDATE(), 114), ':', ''), 6)

SET @backupFile = @MyDBPath + @MyDBName + '_' + @dateStr + '_' + @timeStr + '.bak'

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

What error were you getting?

--------------------
Colt 45 - the original point and click interface

Reinhard Jung Newbie Points: 2 More actions · Answer 2

Thanx Phill,

this helped me a lot!!!

The error was something like "this DB is in singlemode" 🙁

btw: Do you know how i could get a list of all existing DBs an loop over then.

Maybe incl. an exlusiveList like "@skipDBs = 'bd1, bd2'"

So i shurly backup every DB i got and will have in the future.

And if i don't need one of those DBs to backup ill just put them into the skipList?!

best wished from switzerland

JR

philcart SSC-Forever Points: 47797 More actions · Answer 3

There are various methods to get a list of databases (sp_MSForEachDB, sysdatabases, etc...) but the best method I found was to maintain table that lists the databases and where they're backed up to. The table also has a couple of flags to indicate if the database is active and the type of backup to perform.

Then running your backups becomes a simple T-SQL stored procedure that you can put in a SQL Agent job.

--------------------
Colt 45 - the original point and click interface