My client has an ancient medical records system which uses SQL Server 2000. It's running in Virtualbox The host is Windows 7, the guest is Windows 2000.
I have created a script to backup the main database . The database name is MDDataServer
There is no documentation for the application so I don't know whether any information is written to the ldf file. The mdf file keeps growing in size; the ldf file remains at around 300 megabytes.
I created a script because this particular database seems to hold the majority of the data so I want to back it up on a regular schedule.
The script is very simple:
BACKUP DATABASE [MDDataServer]
TO DISK = N'U:\MDDataServer.bak'
The above script ran successfully.
So I executed the following script to backup the log file:
BACKUP LOG [MDDataServer]
TO DISK = N'U:\MDDataServer_LOG.bak'
This script failed with the error:
Server: Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
1.) Does the command BACKUP DATABASE [MDDataServer] backup BOTH the mdf and ldf files? If so what command do I use to restore both the database and log files?
2.) I have searched for an answer to the “ is not allowed while the recovery model is SIMPLE” error. I'm really confused. What's wrong with my backup log statement?
3.) Remember, there is no documentation to the ancient application. Since the log file seems to be in simple mode then I guess it's best to leave it simple mode. I know nothing about SIMPLE mode. What are your thoughts?