Re: SQL backup questions
From: Geoff N. Hiten (SRDBA_at_Careerbuilder.com)
Date: 04/19/04
- Next message: chris: "Re: DBCC DBREINDEX and diskspace"
- Previous message: John Bell: "Re: Training"
- In reply to: pohkeat: "SQL backup questions"
- Next in thread: John Bell: "Re: SQL backup questions"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 19 Apr 2004 13:33:59 -0400
Comments Inline
-- Geoff N. Hiten Microsoft SQL Server MVP Senior Database Administrator Careerbuilder.com I support the Professional Association for SQL Server www.sqlpass.org "pohkeat" <pohkeat@hotmail.com> wrote in message news:OW3A8tiJEHA.1132@TK2MSFTNGP12.phx.gbl... > got a list of backup questions re SQL2000 (on W2K Svr), any advice,comments > welcome > > 1) Backing & restore a DB on another svr will not include logins, scheduled > jobs, is there anything else ? > Logins are at the server level. Users are at the database level and are copied but may need relinking to the logins. Scheduled jobs are stored in MSDB. You can script the jobs and run the scripts against the new server. > 2) Copy DB however will include the whole lot ? > Copy DB is functionally the same as Backup and Restore. > 3) If so , for the purpose of easier restore can we scheduled running COPY > DB wizard every night, instead of a SQL backup job ? is it b'coz COPY DB > will produce a larger file compared to SQL bak file ? I would restore and then run a script to make any changes necessary. > > 4) If a DB is set toFULL recovery model, a common problem is it will result > in really huge ldf file. must we live with it or is there a workaround , > other then setting it to SIMPLE model. Once a FULL backup is made, can we > safely truncate the ldf so as to shrink the log file. Look at Backup and Restore. You will need to periodically backup the transaction log. That will auto-truncate the unused portion of the log, allowing SQL to treat the log file(s) like a giant tape loop. This will not shrink the log file, merely mark parts of it as reusable. > > 5) Does this Backup Strategey make sense : Full DB Bckup nightly, and hourly > LDF backup. Consider the DB is of fairly critical nature. Reason why I dun > use differential back up nightly is because I realized there is no drastic > difference in the bak file size between Differential and Full Backup. That is a good start. I would back up to a remote file share and use a commercial tape management product to archive those files off to tape to give yourself some historical rotation. > > > TIA > pk > NP > > > >
- Next message: chris: "Re: DBCC DBREINDEX and diskspace"
- Previous message: John Bell: "Re: Training"
- In reply to: pohkeat: "SQL backup questions"
- Next in thread: John Bell: "Re: SQL backup questions"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
