Trying to understand BACKUP Database for SQL server 2000 and find answer to the error "not allowed while the recovery model is SIMPLE."

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'
  WITH INIT,
  STATS=10

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'
  WITH INIT,
  STATS=10

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.

QUESTION:
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?
donpickAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
Tying order to backup the log file and have the restore on tine capability, you gave to change the databse from the simple mode to the full mode where the transactions are retained in the log file until the log is backed up.
Use enterprise manager, or SQL 2005 ssms to connect to this server/db and alter the option thrown/reflected by the error.

In simple mode, once the transaction completes, the space within the ldf file is released for other transactions.
0
arnoldCommented:
OH,
1) the database is restored to the backup, the ldf has no useful data, but is restored at the size of the backup.
2) there is nothing wrong with your backup statement, the issue is the configuration of the database. You would need to either use the graphical tool, and change the recovery model to full, or use tsql alter database databasename to change the recovery model to full.
3) the application relying on the data is of little consequence. It queries the database server for data, what and how the database setver manages the data is of little consequence to the application.
Think of it as you place a call to get information, how the information accessed or stored on the other side is of little use to you.

Often you could use ms login transfer to setup SQL 2005/2008 and restore a backup of this db on the new server while making sure the db operating mode is SQL 8.0 compatibility mode.
Then test one workstation with this application to retarget to the new db/setup to confirm it works.
Note the login transfer is important to retain access of the application in the event you do not know/can not determine which logins it uses. This will also allow the restored DB to have logins/sids matched.

Sql2005 will likely be successful. 2008 might require some ....
0
donpickAuthor Commented:
Thank you for your response.
I don't fully understand your response.  I ask again: 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?

The client is not going to purchase sql 2005 or any other sql product as he will finally stop using this ancient program soon.  I'm not sure what will happen if I start alerting the database .  My concern is the ancient application may not be able to communicate with the ldf file if it is changed from simple mode.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

arnoldCommented:
Yes and NO.
In a SIMPLE recovery mode, the backup DB directive backsup the database content (FULL BACKUP) and the LDF file size.
i.e. when you ran the backup the MDF file was 1.2Gb and LDF 300MB.
when you restore, the data in the MDF file will remain at 1.2GB, the LDF file will be restored to a 300MB but there might not be anything in it.
To make it simpler,
The recovery mode simple means you can only backup the DATABASE fully or Differentially.
To have the ability to restore a database to a point in time, YOUR Database Recovery model has to be at least FULL.

The error you received clearly states the reason why the Transaction Log CAN NOT BE BACKED UP.
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options
alter database databasename
set recovery='FULL'
one you do this, you will be able to backup the transaction log.(LDF) file.

Depending on the size of the database, and the number of users, going to the free sql server 2005/2008/R2 express might be an option you would want to help your client to transition to.
though with the express version, SQL agent is no longer available, so you would need to use windows taskscheduler with either powershell, or similar scripting tool to handle the backup/maintenance of the database....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
Backup database, backups the data and the file structure (not the files itself by information about them). When you run the Restore command, it will recreate the files (will overwrite them if you're restoring over an existing database) to their existing location (you can provide new location if you want to) with the size they had when the Backup ran.

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?
Your backup log statement is correct. The problem is that won't work for databases on Simple Recover Mode. It works for the other recover modes (Bulk-Logged and Full). So, you just don't need to run that 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?
The Simple Recover Mode doesn't allow you to recover to a point-in-time. Imagine that an error occurred today at 10:05AM but your last backup is from yesterday. This mean that you can only restore an yesterday image. All that was done today was just lost forever.
0
donpickAuthor Commented:
Thank you for your help. Thank you for answering promptly.
Here is what I did to solve the problem:
  First, remember:  the host is Windows 7 and the guest is Windows 2000.
  There was a large SQL Server 2000 database which was  occupying large amounts of space on the C drive in Virtualbox
  I tried to detach the large SQL Server 2000 database .  When I viewed the database using spwho it told me the database was in multi user mode.  So, in the Enterprise Manager, I changed the database to single user.  When I viewed the database using spwho, it told me the database was in multi user mode.  

So  I finally shut down the SQL Server 2000 service and started the SQL Server 2000 service in single user mode.  When I viewed the large database using spwho it still said the database was in multi user mode.  I decided to detach the database anyway and it detached successfully.

Windows 2000 only knows about IDE and SCSI controllers.  There were 4 drives on the IDE controller .  I created a SCSI controller and added a large vdi file.  I moved the large databases to this new large vdi file.  I reattached the databases to SQL Server 2000  .  I then started SQL Server 2000 in multi user mode.  This has solved the problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.