Backup/restore/maintenance multiple databases -- sql server programming

tom.nys NO[at]SPAM agfa.com
6/28/2004 11:52:44 PM

Hi,

Our software uses 6 databases, each having 2 tables. We have a
ReplicationService, which makes full/incremental backups on a regular
time interval.

Below is the sequence of commands which are being executed during full
backup (sequence is executed for each database):
USE <db>
DBCC SHRINKFILE(<db>_Log, 1)
BACKUP LOG <db> WITH TRUNCATE ONLY
DBCC SHRINKFILE(<db>_Log, 1)
ALTER DATABASE <db> SET RECOVERY FULL
BACKUP DATABASE <db> TO DISK = 'c:\backup\<db>' WITH INIT
BACKUP LOG <db> TO DISK = 'C:\backup\<db>'

This sequence is executed e.g. 1 time a day.

Below is the sequence of commands which are being executed during
incremental backup:

USE <db>
DBCC SHRINKFILE(<db>_Log, 1)
BACKUP LOG <db> WITH TRUNCATE ONLY
DBCC SHRINKFILE(<db>_Log, 1)
ALTER DATABASE <db> SET RECOVERY FULL
BACKUP DATABASE <db> TO DISK = 'c:\backup\<db>_<uniqueid>' WITH INIT,
DIFFERENTIAL
BACKUP LOG <db> TO DISK = 'C:\backup\<db>_<uniqueid>'

This sequence is executed e.g. 1 time per 30 minutes.

Restoring the databases is done using following commands:

RESTORE DATABASE <db> FROM DISK = 'c:\backup\<db>' WITH FILE = 1,
NORECOVERY, REPLACE, MOVE '<db>' TO 'c:\db\<db>', MOVE '<db>_log' TO
'C:\db\<db>.mdl'
if there is an incremental backup
RESTORE DATABASE <db> FROM DISK ='c:\backup\<db>_<lastid>' WITH FILE
= 1, NORECOVERY, REPLACE
RESTORE LOG <db> FROM DISK = 'c:\backup\<db>_<lastid>' WITH FILE =
2, RECOVERY
else
RESTORE LOG <db> FROM DISK = 'c:\backup\<db>' WITH FILE = 2,
RECOVERY

Are these commands ok? Could we improve? How about making sure all 6
databases are in sync?

Thanks for your help,

Tibor Karaszi
6/29/2004 10:41:45 AM

Tom,

I'm afraid you have been given bad advices and you have made things much more difficult for you
compared to what it has to be. Some point:

Why do you shrink the database all the time? SQL Server, like all real DBMS's, uses pre-allocated
storage. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for more information.

Why do you keep doing the BACKUP LOG using TRUNCATE_ONLY. As soon as you do this, you break the log
backup sequence, which makes all your log backups worthless. And, while on the topic of log backups,
why do you do diff backups instead of log backups? Below is what I suggest. Db should be in FULL
recovery all the time:

Every full backup occasion, for instance every day:
BACKUP DATABASE <db> TO DISK = 'c:\backup\<db>' WITH INIT

Every log backup occasion, for instance every hour:
BACKUP LOG<db> TO DISK = 'c:\backup\<db>_<uniqueid>' WITH INIT,

You have to figure out for yourself how you want to handle backup generation etc for your backup
files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

[quoted text, click to view]

sql server programming : Backup/restore/maintenance multiple databases