[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-08-08
4
Medium Priority
?
4,615 Views
Last Modified: 2013-11-05
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?
0
Comment
Question by:yanci1179
  • 3
4 Comments
 
LVL 6

Expert Comment

by:itdrms
ID: 19656117
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
 
LVL 6

Expert Comment

by:itdrms
ID: 19656187
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
 

Author Comment

by:yanci1179
ID: 19662030
sp_addumpdevice
0
 
LVL 6

Accepted Solution

by:
itdrms earned 2000 total points
ID: 19696406
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question