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

Other SQL Server Topics (2005)

Backup issues

Author

Topic

tomislavg
Yak Posting Veteran

51 Posts

Posted - 2010-02-01 : 02:41:58

Hi,

I have a problem which I would like you to help me out.

I have 2 HDD, each 500 GB on the server, and I was running all the backups (Backup device) on the first drive(C) full and diff, but as db are growing I created another backup (device) and I am only doing full backup on D drive now and other still on C.

Now on the C I still have some smaller backup running but not too much in size.
Now my question is, Can I select and delete specific backups of the databases or do I need to delete the whole backup device to reduce the backup size (350Gb) on the C drive?

How should I do it in the future, what is the best practice?

Thanks a lot,
Tomislav

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-01 : 05:44:06

Hm, do you apply all you backups to the same file? If you do then you probably should change your backup strategy to create an individual file for each backup.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-01 : 05:52:10

Check out this link, it basically says that it's impossible to delete a single backup in a file with multiple backups in it:

http://social.msdn.microsoft.com/forums/en-US/sqldisasterrecovery/thread/34bef98d-3dc4-4ef2-ba6d-8a14b9037da6/

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

tomislavg
Yak Posting Veteran

51 Posts

Posted - 2010-02-01 : 08:07:48

Hi Lumbago, sorry I mixed some things above. I am doing each DB in one device. But for the biggest DB (bckup device) I am trying to find the solution, 350Gb.
So in the end I am doing Full backup of that DB on D drive (new device) and diff and translog on C drive (old device). But I was hoping to delete some backups on the C drive from the old device... hm.....

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-01 : 08:27:54

If you backup to disk you should not use logical devices at all, just take the backup directly to disk. You can modify this script to your liking but the default behavior is to backup all databases to disk in individual backup files, with a timestamp in the filename.

SET NOCOUNT ON

DECLARE
  @tmpDate datetime,
  @TimeStamp varchar(50),
  @totCount int,
  @Current int,
  @dbName varchar(50),
  @BackupFolder varchar(500),
  @BackupFilename varchar(500)

SET @BackupFolder = 'f:\Backup\'

DECLARE @Databases TABLE (
  ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  dbName varchar(50)
)

SET @tmpDate = GETDATE()
SET @TimeStamp = CONVERT(varchar(50), @tmpDate, 112) + '_' +
  RIGHT('00' + CAST(DATEPART(hh, @tmpDate) AS varchar(4)), 2) +
  RIGHT('00' + CAST(DATEPART(mi, @tmpDate) AS varchar(4)), 2)

INSERT INTO @Databases (dbName)
SELECT name
FROM sys.databases
WHERE name NOT IN ('model', 'tempdb')

SET @totCount = (SELECT @@ROWCOUNT)
SET @Current = 1

WHILE (@Current <= @totCount)
BEGIN
  SELECT @dbName = dbName FROM @Databases WHERE ID = @Current
  SET @BackupFilename = @BackupFolder + @TimeStamp + '_' + @dbName + '_full.bak'

  BACKUP DATABASE @dbName TO DISK = @BackupFilename
  SET @Current = @Current + 1
END

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

tomislavg
Yak Posting Veteran

51 Posts

Posted - 2010-02-02 : 03:46:27

Thanks Lumbago, so this will backup the each db (which we select) on the disk.
So for each db I will have to do it. How do I choose the schedule?

Sorry, I can do the basic TSQL but I get a bit confused when trying to do something from the script, especially regarding the variables, much easier thru the GUI but I am still learning.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-02 : 04:11:06

No problem, we are all learning

If you check the select statement in the middle (SELECT name FROM sys.databases WHERE name NOT IN ('model', 'tempdb')) these are the databases that will be backed up. Run this query by itself and you will see which ones they will be and modify it to your needs.

By replacing this line
BACKUP DATABASE @dbName TO DISK = @BackupFilename

with this
PRINT 'BACKUP DATABASE ' + @dbName + ' TO DISK = ''' + @BackupFilename + ''''

you can run the script without it actually doing anything. Try it and play with it a little to get the feel of how it works.

If you decide to use it and you need to schedule it you must create a job under SQL Server Agent in management studio. It's pretty straight forward...if you have any problems don't be shy. The only stupid question is the one you don't ask

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

tomislavg
Yak Posting Veteran

51 Posts

Posted - 2010-02-02 : 06:36:38

Thanks, I'll try it....

of course not on the live db...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources