donpick
asked on
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?
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?
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 ....
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 ....
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.