Setting up maintenance after the fact...

[Denda]

Hi All -
I have a SQL database that the maintenance was never configured by the consultant who built it. We have a server that 1.5G is the data_data & 33G is the data_log, full recovery set. We have only 7G available on the server.

I need to be able to setup the maintenance, but I'm concerned that if I set it up prior to backing up the database in full, I will lose some of the transaction log. With me only having 7G available, I don't know how I will get a full complete backup of the database. Will the first backup cover us or is there another way to accomplish this?

Thanks in advance

 

[SQLBill]

A FULL backup does not backup the transaction log. It backs up the Database files. To backup the log you must use BACKUP LOG command.

A FULL backup 'resets' the transaction log.

My suggestion:
Do a full backup
Truncate the transaction log
(BACKUP LOG dbname WITH TRUNCATE ONLY)
Do a full backup
Set up a maintenance plan to do regular Full backups and transaction log backups. (or preferably, create the backup jobs yourself).

Refer to the BOL for more information on doing backups. Also check out the FAQs in this forum and Forum183.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[Denda]

Thanks Bill -
I just want to confirm what I'm understanding to do.

Do a full backup...
I'm understanding that this is the database backup, NOT the backup log command, is that correct? If you mean the backup log command, I can't do it this way since I have no room on my server.

Thanks a ton

 

[Denda]

Here's what I did.
Full database backup
Truncated the trans log
(BACKUP LOG dbname WITH TRUNCATE_ONLY)
Full database backup
Ran DBCC SHRINKFILE (data_log)

All this worked.


Thanks a ton!