April 16, 2003 at 9:28 am
Hi All,
I have a basic question. Will a Tape Backup job properly backup .MDF and .LDF files while the SQL Server is running on the server? Or do we need to stop the SQL Server while the tape backup is running?
Regards,
Murali Damera.
.
April 16, 2003 at 9:59 am
No problems with users being in system while backups are running. There is no difference in system availability between tape and disk backups.
April 16, 2003 at 10:17 am
No. My question is different. Here is the scenario.
I have a new server coming on board with SQL2K on WIN2K. If we run a TAPE BACKUP from NT (to backup the files & directories on NT - This is NOT SQL Server Backup), Can we recover SQL Server Database using .MDF and .LDF files on the table? Or Do we need to stop the SQL Server for the proper backing up of the SQL database files onto TAPE?
.
April 16, 2003 at 10:31 am
Your backup software will not back them up while the service is running. You will need to stop the service prior to taking a backup.
Best solution in my opinion is schedule SQL Server backups to disk and have your backup software grab those files off disk. Very reliable and no downtime.
HTH
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 16, 2003 at 11:55 am
You can recover a database by using the .mdf and .ldf files. This is actually the easiest way to RECOVER a database since you only need to do an attach command. However, it is the hardest way to BACKUP a database since as others stated, you need to stop the services and then backup the files.
The easiest way overall is to do an SQL Server backup (to disk or tape) and then recover using SQL Server.
-SQLBill
April 16, 2003 at 1:02 pm
Thank You Bill. Thats the same way I am doing on the other servers. I jsut wanted to take extra coution and take Data directory backup as well atleast biweekly or monthly, during the sql server downtime.
Is that a bad idea?
.
April 17, 2003 at 5:59 am
It's never a bad idea to copy the .mdf and .ldf during down times. It gives you the best way to restore data. Just remember:
If you restore using the .mdf/.ldf files, you can never restore current transaction log backups or differential backups to those files. (i.e. Sunday you copy the .mdf/.ldf files, Monday you do a differential or transaction log backup. Tuesday you restore using the .mdf/.ldf files, you cannot also restore the Monday diff or transaction logs).
-SQLBill
April 17, 2003 at 7:00 am
One thought - you cannot restore to a point in time this way. I would let SQL backup to disk, then save disk to tape.
April 18, 2003 at 10:25 am
i think most DBAs do this: use SQL backup to backup the database and log to physical backup file and use NT Backup of third-party s/w to backup the backup files to tape. No down time. Easy to restore, and able to restore to a point in time. 🙂
April 18, 2003 at 11:02 am
Thank You. I am doing the same. But I thought I would take Monthly backup of .mdf and .ldf files.
Thanks for your suggestion.
.
April 21, 2003 at 5:10 am
as a side-note another GREAT reason to perform backups as you have all stated is:
By allowing SQL to do the backups to specified location the 3rd party software will never lock-up your SQL server due to jobs abending.
This is a problem with BackUp EXEC. It locks the database(s) performs the backup and releases. This is different than SQL which takes a snap-shot of the log(s) does the back-up and then checks to see if log(s) are the same.
In the 1st case if the tape backup fails you have (at minimum) to stop/restart services. With the 2nd if the job fails it has no effect on the server still running.
AJ Ahrens
SQL DBA
Revenue Assurance Management - AT&T
Good Hunting!
AJ Ahrens
webmaster@kritter.net
April 21, 2003 at 8:20 am
what are mdf. and wmf documents?
April 21, 2003 at 8:36 am
.mdf = the physical file that your database uses to store data
.ldf = transaction log(s)
AJ Ahrens
SQL DBA
Revenue Assurance Management - AT&T
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply