SQL 2005 Backup Database using T-SQL ServerObjects-->Backup Devices

In SQL 2005 I created a Backup Device that points to the backup location

I this scripted the following:

BACKUP DATABASE Database
TO DISK = N'BackupDeviceName\TrackIT.bak'
WITH Description =N'Full Database Backup'
, INIT
, NAME = N'DatabaseFullBackup'
GO

It doesn't look for the backup in the device.  It is looking for the file in C Drive.  Will I actually have to specify the drive D:\backups or can I use the backup device that I created that is already pointing to the drive?  Since the Maintnance Jobs are not available in SQL 2005, I planned on running the statement in a scheduled job, is this the best way to do this or is there another way in SQL 2005?
yanci1179Asked:
Who is Participating?
 
itdrmsConnect With a Mentor Commented:
Even if you fix the syntax of your statement, if your backup device is that one file, you have it set to start fresh each time (INIT) rather than append.  You would only have one backup, which is bad practice.
I created this sp and run it in a scheduled job.  It tacks on a timestamp so each file is unique.  You can either run it for 1 DB, or let it run through all your DBs.  Then you run a clean up job to clear out old files.

CREATE PROCEDURE DBA_MaintBackup (@Backup_Dir       NVARCHAR(500),@Backup_Type VARCHAR(8), @dbasename       VARCHAR(35) = NULL)
--EXEC DBA_MaintBackup 'S:\MSSQLTransaction\daily\','LOG'
--EXEC DBA_MaintBackup 'F:\MSSQLBackup\','DATABASE','Northwind'
AS

SET ROWCOUNT 0
SET NOCOUNT ON

DECLARE @fileinfo TABLE (   dbname varchar(25))
DECLARE @SQLString       NVARCHAR(500)
DECLARE @rc int
DECLARE @datestamp varchar(15), @server varchar(100),@bakfile varchar(100)
SET @datestamp = replace(replace(replace(convert(varchar(16),getdate(),120),'-',''),':',''),' ','_')

IF @dbasename is null
BEGIN
      INSERT INTO @fileinfo
      SELECT name
      FROM sys.databases d
      where name not in ('tempdb','pubs','Northwind')
      and state = 0
      and (CASE WHEN @Backup_Type = 'LOG' AND recovery_model_desc = 'full' THEN 1
            WHEN @Backup_Type = 'LOG' AND recovery_model_desc != 'full' THEN 0
            ELSE 1 END) = 1
END
ELSE
      INSERT INTO @fileinfo
      SELECT @dbasename

SET ROWCOUNT 1
SELECT @dbasename = dbname
FROM @fileinfo

WHILE @@ROWCOUNT = 1
BEGIN
      SET @bakfile = @Backup_Dir+ @dbasename+'_db_'+@datestamp
      IF @Backup_Type = 'DATABASE'
            SET @bakfile = @bakfile+'.BAK'
      ELSE
            SET @bakfile = @bakfile+'.TRN'
      SET @SQLString= 'BACKUP '+@Backup_Type+' '    + QUOTENAME(@dbasename)
           + char(13) + ' TO DISK = N'''+@bakfile+''''
      EXEC @rc=sp_executesql @SQLString
      if @rc !=0 PRINT 'Error backing up ' +@dbasename      
      ELSE
      BEGIN
            SET @SQLString= 'RESTORE VERIFYONLY FROM DISK = N'''+@bakfile+''''
            EXEC @rc=sp_executesql @SQLString
            if @rc !=0 PRINT 'Error verifying backup file ' +@bakfile
            ELSE PRINT 'Successfully verified backup file '+@bakfile
      END

      DELETE FROM @fileinfo
      WHERE dbname = @dbasename

      SELECT @dbasename = dbname
      FROM @fileinfo
END

0
 
itdrmsCommented:
drop the disk=

BACKUP DATABASE Database
TO BackupDeviceName

-----
Not sure what you mean by "Since the Maintnance Jobs are not available in SQL 2005".  New plans are under Management, Maintenance Plans -- imported plans from the previous version are under Mangement, Legacy, Database Maintenance Plans.

-----
Personally, I am not a fan of the new 2005 maintenance plans -- MS doesn't have a way to script them out so you can't keep a script backup, move to other instances, or edit easily without the GUI -- I find the GUI too slow, and has frequently errored out so that I have to scrap the plan and start from scratch.  I used plans in 00, but I am moving to T-SQL too.

0
 
itdrmsCommented:
I reread your post a little closer...How did you create the backup device?  A SQL backup device doesn't point to a drive -- it points to a file.  I assumed you used the system stored proc "sp_addumpdevice" to set up a file.
0
 
yanci1179Author Commented:
sp_addumpdevice
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.