February 9, 2008 at 6:06 pm
Hello..
I have 55 DB's that I have being backed up daily through the DB Maintenance Plans.
Problem is that I cannot tell it just to overwrite the backups daily, it is creating a new backup
each day. Is using DTS the best way for a large number of databases? If so, how would one get started that does not know DTS?
February 10, 2008 at 3:47 am
in your maintenance plan choose to backup to a file, and then select to overwrite the file rather than append. If you dont want to do that for all 55 databases do 1 and get the script (option to see the sql code is at the bottom) and then amend the script for each db so you have 55 backup commands. Run the script instead of your backup plan.
The other way to do it is using the maintenance cleanup task. If you dont have space to hold 2 full backups (the last one and the current one your about to run) then cleanup before you start the new backup with anything over 1 minute old (this will clear everything). Probably not recommended because if your new backup fails you don't have any backup on disk then.
February 10, 2008 at 9:02 am
I'd agree with the above advice in general. DTS or SSIS could be used, or a simple script in a job. However maintenance plans work great.
February 10, 2008 at 10:27 am
One thing you also should be aware of is that you always have the option of executing a SQL Task in your maintenance plan.
So, you don't have to use the built in plug-ins, you can use your own backup command or stored procedures.
I do this with one of my systems. For example, to check database integrity I use the Execute SQL Task and run: DBCC CHECKDB(database) WITH PHYSICAL_ONLY (I also schedule a full integrity check weekly). I also have a custom procedure for rebuilding indexes, etc...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 11, 2008 at 8:46 am
I use a slightly different approach in that I have the db's do a differential backup for 6 days and then a full backup on Sunday. That in combination with a delete script to eliminate any files older than 2 weeks keeps the spindle clean and usable. It also provides me with a way to restore anything I tear up. (I'm uusally the culprit when a db is down :w00t:).
[font="Arial"]Thank You,
[/font]
[font="Comic Sans MS"]Charlie[/font]
[font="Arial"]Charles Eaton[/font]
February 11, 2008 at 8:58 am
Hello/Hola...
ENGLISH:
Your problem can be easily solved by implementing a simple JOB in your SQL Server Agent that compacts and backups all databases in your server.
SPANISH:
Tu problema puede ser fácilmente resuelto implementando una simple tarea en tu Agente de SQL Server, la cual compacta y respalda todas las bases de datos de tu servidor.
Here the code/Aqui tienes el código:
-------------------------------------------------------------------------------------------------------------
-- MANTENIMIENTO.SQL: ---------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-- AUTOR: Nelson Ochoa --
-- DESARROLLADO: 2008-01-15 --
-------------------------------------------------------------------------------------------------------------
-- SPANISH: Realiza un mantenimiento dinámico de todas las bases de datos almacenadas en el servidor --
-- ENGLISH: Make a dynamic maintenance of all databases stored on the server --
-------------------------------------------------------------------------------------------------------------
SET NOCOUNT ON
USE master
DECLARE@NombreBD AS nvarchar(255)
DECLARE @NombreBak AS nvarchar(500)
DECLARE @DescrBak AS nvarchar(255)
DECLARE @Fecha AS nvarchar(8)
DECLARE @destino AS nvarchar(255)
DECLARE@Extension AS nvarchar(4)
DECLARE@Descripcion AS nvarchar(255)
DECLARE@Descripcio2 AS nvarchar(255)
DECLARE@Separador AS nvarchar(1)
DECLARE@Reemplazar AS bit
SET@Reemplazar=1
SET@destino='D:\Respaldos\'
SET@Extension='.bak'
SET@Descripcion='Respaldo completo de '
SET@Descripcio2=' del dia ' + CONVERT(nvarchar(4),YEAR(GETDATE()))+'-'+RIGHT('00'+CONVERT(nvarchar(2),MONTH(GETDATE())),2)+'-'+RIGHT('00'+CONVERT(nvarchar(2),DAY(GETDATE())),2)
SET@Separador='_'
SET@Fecha=CONVERT(nvarchar(4),YEAR(GETDATE()))+RIGHT('00'+CONVERT(nvarchar(2),MONTH(GETDATE())),2)+RIGHT('00'+CONVERT(nvarchar(2),DAY(GETDATE())),2)
DECLAREResultados CURSOR FOR
SELECTname FROM sysdatabases WHERE (dbid NOT IN(2))
OPENResultados
FETCH NEXT FROM Resultados INTO @NombreBD
WHILE @@FETCH_STATUS = 0
BEGIN
-- CAMBIAMOS EL COLLATION... (DE SER NECESARIO, EN CASO DE QUERER ESTANDARIZAR)...
-- ALTER DATABASE @NombreBD COLLATE SQL_Latin1_General_CP1_CI_AS
-- COMPACTAMOS LA BASE DE DATOS (metodo 1)...
BACKUP LOG @NombreBD WITH NO_LOG
-- COMPACTAMOS LA BASE DE DATOS (metodo 2)...
--DBCC SHRINKDATABASE(@NombreBD, TRUNCATEONLY)
-- RESPALDAMOS LA BASE DE DATOS...
IF (@Reemplazar=1) THEN
BEGIN
SET @NombreBak = @destino + @NombreBD + @Extension
END
ELSE
BEGIN
SET @NombreBak = @destino + @NombreBD + @Separador + @Fecha + @Extension
SET @DescrBak = @Descripcion + @NombreBD
END
BACKUP DATABASE @NombreBD
TO DISK=@NombreBak
WITHDESCRIPTION=@DescrBak,
INIT
FETCH NEXT FROM Resultados INTO @NombreBD
END
CLOSEResultados
DEALLOCATEResultados
I hope this can be useful for all of you. Greetings / Espero que sea de utilidad a todos. Saludos
PS: Any questions, can write me at loconelson@gmail.com
PD: Cualquier pregunta, me pueden escribir a loconelson@gmail.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply