Recovery Model Question.

Hello to everyone,
I want to ask if there is a way ,maybe through a strored procedure or sql
script
to change massive in 200 databases for example the recovery model from full
to simple ?
If anybody has an idea or knows i will appreciate it.
Thanx a lot.
Cipher.
See ALTER DATABASE in BOL
"Cipher" <cipher@.aol.com> wrote in message
news:eIh22lkZHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hello to everyone,
> I want to ask if there is a way ,maybe through a strored procedure or sql
> script
> to change massive in 200 databases for example the recovery model from
> full to simple ?
> If anybody has an idea or knows i will appreciate it.
> Thanx a lot.
> Cipher.
>
|||Are you asking for a way to change this value against 200 different
databases?
"Cipher" <cipher@.aol.com> wrote in message
news:eIh22lkZHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hello to everyone,
> I want to ask if there is a way ,maybe through a strored procedure or sql
> script
> to change massive in 200 databases for example the recovery model from
> full to simple ?
> If anybody has an idea or knows i will appreciate it.
> Thanx a lot.
> Cipher.
>
|||Yes,exactly.
I want to change massive all the 200 databases recovery
model option from full to simple but i dont want to achieve this manually
because of the huge overhead.
For that reason i asked ,if theres a more quicker and better way to achive
this.
Thnx a lot.
Cipher.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23DV67OlZHHA.348@.TK2MSFTNGP02.phx.gbl...
> Are you asking for a way to change this value against 200 different
> databases?
> "Cipher" <cipher@.aol.com> wrote in message
> news:eIh22lkZHHA.4772@.TK2MSFTNGP05.phx.gbl...
>
|||Tibor,
I will even keep MSDB in simple recovery mode and takes the differential
backup twice a day.
SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL'
FROM sys.databases
WHERE name NOT IN('master', 'tempdb','MSDB')
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eSAEbjnZHHA.5044@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> No version posted, assuming 2005:
> Modify below to your liking, run it, take the result, tidy up and verify,
> and execute it.
> SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' FROM sys.databases
> WHERE name NOT IN('master', 'tempdb')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Morgan" <morgan@.heaven.com> wrote in message
> news:%23V1czMnZHHA.1400@.TK2MSFTNGP06.phx.gbl...
|||First,thnx a lot guys for your support.
I run this sql script through Query Analyzer :
use master;
select 'alter database' +name+ 'set recovery simple'
from dbo.sysdatabases
where name not in('master','tempdb','msdb','model')
go
but the weird is that the script runs succefully without errors ,but it
didnt make any change
to any databases!I check the recovery model and remains unfortunately full
in all databases..
i dont understand..
Also because i forgot to mention it,the SQL version is SQL 2000 SP 4
Cipher.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVZMFxpZHHA.1400@.TK2MSFTNGP06.phx.gbl...
> Tibor,
> I will even keep MSDB in simple recovery mode and takes the differential
> backup twice a day.
> SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL'
> FROM sys.databases
> WHERE name NOT IN('master', 'tempdb','MSDB')
> Thanks
> Hari
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:eSAEbjnZHHA.5044@.TK2MSFTNGP05.phx.gbl...
>
|||Yes,it works fine now.
The script achieve a massive recovery model change!
Thx a lot Tibor!
Cipher.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uKlFswtZHHA.4808@.TK2MSFTNGP04.phx.gbl...
> The script generates your ALTER DATABASE commands for you. Take the result
> of the script, paste in into a query window and execute it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Cipher" <cipher@.aol.com> wrote in message
> news:eZOTxetZHHA.2448@.TK2MSFTNGP02.phx.gbl...
>
|||Hi Tibor,
I agree with you...
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OJpY2gtZHHA.1508@.TK2MSFTNGP06.phx.gbl...
> Hi Hari,
> We all feel differently about msdb. That is why I said "Modify below to
> your liking". I prefer to do log backups for msdb, and have msdb as the
> last database for each backup execution. This way I always have backup of
> the most recent backup history. I might not be the one who is doing the
> restore, and if somebody restore msdb and then the other databases based
> on backuphistory, they won't get the databases as up to date as the
> backups would allow. Many find this overkill, though. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eVZMFxpZHHA.1400@.TK2MSFTNGP06.phx.gbl...
>

Recovery Model of Subscription Database

Hi:

I am having lot of log problems with Subscription databases. Currently all my subscription databases are on Full recovery mode. I am thinking to change them to simple because I don't I will be doing point in time recovery of them.

Do the subcription databases have to be on Full mode? Can I change them to simple to keep my log small and then I do not have to backups of my logs also? Please let me know.

Thanks

No they don't, simple will be fine, but what's the problem you are having?|||

Thanks for replying. Log of the subscription database is about 96% full. I tired to truncate it but still size stays around the same. Even though I changed the recovery model to simple, it should truncate it at every checkpoint. The size is staying about the same.

There are some active transactions sitting in the log. By truncating it multiple times, it should remove (purge) them. Actually log agent was stopped manually. Probably these active transactions may be partial and do not how to commit. Any ideas will be helpful.

Thanks

|||

Mike Husar wrote:

There are some active transactions sitting in the log. By truncating it multiple times, it should remove (purge) them. Actually log agent was stopped manually. Probably these active transactions may be partial and do not how to commit. Any ideas will be helpful.

You mention logreader agent, which should only affect published database. Are you talking about published or subscriber databases? If published database, only transactions that are marked as replicated (txns that the log reader agent has already replicated) can be truncated. Non-replicated txns will not be truncated.

|||

Actually it is not the logreader agent. It is the log of subcription database that I was not able to truncate.

|||Then you need to find out what's blocking. Usually this is due to active backup or restore, an active transaction, or some kind of diagnostic log scan.|||You can also try restarting the server to clear those active/open transactions, but then the uncommitted transactions will get rolled back.sql

Recovery Model Full

I have a SQL Server 2000 databases that has Windows 2000 Advanced Server as
operating system. I am trying to set sp_dboption database_name, 'trunc. log
on chkpt.','TRUE' with the recovery model set to full.
When I set the database recovery model to full and then execute sp_dboption
acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
model back to simple.
Please help me resolve this issue with recovery model and 'trunc. log on
chkpt.','TRUE'.
Thank You,The recovery model concept *replaces* the two old database options "trunc."
and "select into".
Setting trunc to TRUE is the very same thing as setting recovery model to si
mple.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
> I have a SQL Server 2000 databases that has Windows 2000 Advanced Server a
s
> operating system. I am trying to set sp_dboption database_name, 'trunc. l
og
> on chkpt.','TRUE' with the recovery model set to full.
> When I set the database recovery model to full and then execute sp_dboptio
n
> acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
> model back to simple.
> Please help me resolve this issue with recovery model and 'trunc. log on
> chkpt.','TRUE'.
> Thank You,|||That's really there for backwards compatibility. Use ALTER DATABASE.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
I have a SQL Server 2000 databases that has Windows 2000 Advanced Server as
operating system. I am trying to set sp_dboption database_name, 'trunc. log
on chkpt.','TRUE' with the recovery model set to full.
When I set the database recovery model to full and then execute sp_dboption
acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
model back to simple.
Please help me resolve this issue with recovery model and 'trunc. log on
chkpt.','TRUE'.
Thank You,|||Joe,
SQL Server doesnt allow you truncate the log on checkpoint when the
recovery model is FULL. ie...either a database can be in Simple Recovery
Model (truncate log on checkpoint) or Full Reovery Model but not both.
Jayesh
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
> I have a SQL Server 2000 databases that has Windows 2000 Advanced Server
> as
> operating system. I am trying to set sp_dboption database_name, 'trunc.
> log
> on chkpt.','TRUE' with the recovery model set to full.
> When I set the database recovery model to full and then execute
> sp_dboption
> acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
> model back to simple.
> Please help me resolve this issue with recovery model and 'trunc. log on
> chkpt.','TRUE'.
> Thank You,

Recovery Model Full

I have a SQL Server 2000 databases that has Windows 2000 Advanced Server as
operating system. I am trying to set sp_dboption database_name, 'trunc. log
on chkpt.','TRUE' with the recovery model set to full.
When I set the database recovery model to full and then execute sp_dboption
acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
model back to simple.
Please help me resolve this issue with recovery model and 'trunc. log on
chkpt.','TRUE'.
Thank You,The recovery model concept *replaces* the two old database options "trunc." and "select into".
Setting trunc to TRUE is the very same thing as setting recovery model to simple.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
> I have a SQL Server 2000 databases that has Windows 2000 Advanced Server as
> operating system. I am trying to set sp_dboption database_name, 'trunc. log
> on chkpt.','TRUE' with the recovery model set to full.
> When I set the database recovery model to full and then execute sp_dboption
> acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
> model back to simple.
> Please help me resolve this issue with recovery model and 'trunc. log on
> chkpt.','TRUE'.
> Thank You,|||That's really there for backwards compatibility. Use ALTER DATABASE.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
I have a SQL Server 2000 databases that has Windows 2000 Advanced Server as
operating system. I am trying to set sp_dboption database_name, 'trunc. log
on chkpt.','TRUE' with the recovery model set to full.
When I set the database recovery model to full and then execute sp_dboption
acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
model back to simple.
Please help me resolve this issue with recovery model and 'trunc. log on
chkpt.','TRUE'.
Thank You,|||Joe,
SQL Server doesnt allow you truncate the log on checkpoint when the
recovery model is FULL. ie...either a database can be in Simple Recovery
Model (truncate log on checkpoint) or Full Reovery Model but not both.
Jayesh
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1875F20C-FD1E-4691-9240-7DCF7BD93F16@.microsoft.com...
> I have a SQL Server 2000 databases that has Windows 2000 Advanced Server
> as
> operating system. I am trying to set sp_dboption database_name, 'trunc.
> log
> on chkpt.','TRUE' with the recovery model set to full.
> When I set the database recovery model to full and then execute
> sp_dboption
> acn_sideB, 'trunc. log on chkpt.','TRUE' SQL Server changes the recovery
> model back to simple.
> Please help me resolve this issue with recovery model and 'trunc. log on
> chkpt.','TRUE'.
> Thank You,

Recovery Model code in SQL 2000

In SQL 2005, sys.databases has a column named recovery_model that stores a code for the type of recovery model used by the database. Where is the recovery_model column in the SQL 2000 master database?

Thanks,

Craig

Code Snippet

select name, Databasepropertyex(name, 'Recovery') recovery from master..sysdatabases

sql

Recovery mode "SIMPLE" & SP2

I have several databases that perform daily backups. Ever since I installed SP2, the backup jobs are failing. The logs state that it's because the database recovery mode cannot be SIMPLE, but need to be either FULL or Bulk-Logged.

Can anyone tell me if this is true, and that I do have to change my recovery mode?

Here's the error:

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
Report was generated on "ICIS-SQL-SERVER".
Maintenance Plan: DB Backup
Duration: 00:08:05
Status: Warning: One or more tasks failed..
Details:
Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [hl7 db_SamirTesting] TO DISK = N'D:\\mssql\\backup\\hl7 db_SamirTesting\\hl7 db_SamirTesting_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'hl7 db_SamirTesting_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:BACKUP LOG [hl7 db_SamirTesting] TO DISK = N''D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [dg_efilm_153] TO DISK = N''D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [hl7 db] TO DISK = N''D:\mssql\backup\hl7 db\hl7 db_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [dg_efilm_153] TO DISK = N'D:\\mssql\\backup\\dg_efilm_153\\dg_efilm_153_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'dg_efilm_153_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:BACKUP LOG [hl7 db_SamirTesting] TO DISK = N''D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [dg_efilm_153] TO DISK = N''D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [hl7 db] TO DISK = N''D:\mssql\backup\hl7 db\hl7 db_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [hl7 db] TO DISK = N'D:\\mssql\\backup\\hl7 db\\hl7 db_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'hl7 db_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:BACKUP LOG [hl7 db_SamirTesting] TO DISK = N''D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [dg_efilm_153] TO DISK = N''D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [hl7 db] TO DISK = N''D:\mssql\backup\hl7 db\hl7 db_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:08.
Task end: 2007-03-10T05:08:09.
Failed:(0) Database 'hl7 db_SamirTesting' will not be backed up because it does not have its recovery model set to Full or BulkLogged.

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:08.
Task end: 2007-03-10T05:08:09.
Failed:(0) Database 'dg_efilm_153' will not be backed up because it does not have its recovery model set to Full or BulkLogged.

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:08.
Task end: 2007-03-10T05:08:09.
Failed:(0) Database 'hl7 db' will not be backed up because it does not have its recovery model set to Full or BulkLogged.

Back Up Database (Full) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All databases
Type: Full
Append existing
Task start: 2007-03-10T05:00:06.
Task end: 2007-03-10T05:08:08.
Success
Command:EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\master''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\model''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\msdb''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\hl7 db_SamirTesting''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\dg_efilm_153''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\hl7 db''
GO
BACKUP DATABASE [master] TO DISK = N''D:\mssql\backup\master\master_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''master_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO DISK = N''D:\mssql\backup\model\model_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''model_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO DISK = N''D:\mssql\backup\msdb\msdb_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''msdb_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [hl7 db_SamirTesting] TO DISK = N''D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [dg_efilm_153] TO DISK = N''D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [hl7 db] TO DISK = N''D:\mssql\backup\hl7 db\hl7 db_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10

Please help,

-tim

Check what kind of recovery model is adopted on the databases that are involved in this backup schedule.|||

I have this issue aswell but with the ReportServer database and the ReportServerTempDB database. ReportServer recovery mode is set to full

ReportServerTempDB recovery mode is set to simple.

We have a database maintenance plan set to back up all user databases (at the top of the window it says that simple will be ignored'. The maintenance plan runs but fails as it tries to back up the ReportServerTempDB database.

The messages in the history of the plan say…

9/5/2007 09:15 Database 'ReportServerTempDB' will not be backed up because it does not have its recovery model set to Full or Bulk Logged.

9/5/2007 09:15 Executing the query "BACKUP LOG [ReportServerTempDB] TO DISK = N'G:\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\ReportServerTempDB\\ReportServerTempDB_backup_200705091638.trn' WITH NOFORMAT, NOINIT, NAME = N'ReportServerTempDB_backup_20070509163838', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It appears that it reports it as an error when it checks the recovery mode, but then tries to back it up anyway. I do not have any problems with any other databases except the reporting services ones. At the moment I have both set to full recovery mode, just to get the maintenance plan to work. Any ideas anyone? This worked fine on Sp1 with the database set to Simple recover mode..

Thank you!

|||You cannot backup the system database and report server based databases in the transaction log schedule, as the error is obvious due to the configuration for system databases recovery model and you will not be able change that.

Recovery mode "SIMPLE" & SP2

I have several databases that perform daily backups. Ever since I installed SP2, the backup jobs are failing. The logs state that it's because the database recovery mode cannot be SIMPLE, but need to be either FULL or Bulk-Logged.

Can anyone tell me if this is true, and that I do have to change my recovery mode?

Here's the error:

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
Report was generated on "ICIS-SQL-SERVER".
Maintenance Plan: DB Backup
Duration: 00:08:05
Status: Warning: One or more tasks failed..
Details:
Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [hl7 db_SamirTesting] TO DISK = N'D:\\mssql\\backup\\hl7 db_SamirTesting\\hl7 db_SamirTesting_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'hl7 db_SamirTesting_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:BACKUP LOG [hl7 db_SamirTesting] TO DISK = N''D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [dg_efilm_153] TO DISK = N''D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [hl7 db] TO DISK = N''D:\mssql\backup\hl7 db\hl7 db_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [dg_efilm_153] TO DISK = N'D:\\mssql\\backup\\dg_efilm_153\\dg_efilm_153_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'dg_efilm_153_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:BACKUP LOG [hl7 db_SamirTesting] TO DISK = N''D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [dg_efilm_153] TO DISK = N''D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [hl7 db] TO DISK = N''D:\mssql\backup\hl7 db\hl7 db_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [hl7 db] TO DISK = N'D:\\mssql\\backup\\hl7 db\\hl7 db_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'hl7 db_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:BACKUP LOG [hl7 db_SamirTesting] TO DISK = N''D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [dg_efilm_153] TO DISK = N''D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [hl7 db] TO DISK = N''D:\mssql\backup\hl7 db\hl7 db_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:08.
Task end: 2007-03-10T05:08:09.
Failed:(0) Database 'hl7 db_SamirTesting' will not be backed up because it does not have its recovery model set to Full or BulkLogged.

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:08.
Task end: 2007-03-10T05:08:09.
Failed:(0) Database 'dg_efilm_153' will not be backed up because it does not have its recovery model set to Full or BulkLogged.

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:08.
Task end: 2007-03-10T05:08:09.
Failed:(0) Database 'hl7 db' will not be backed up because it does not have its recovery model set to Full or BulkLogged.

Back Up Database (Full) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All databases
Type: Full
Append existing
Task start: 2007-03-10T05:00:06.
Task end: 2007-03-10T05:08:08.
Success
Command:EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\master''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\model''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\msdb''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\hl7 db_SamirTesting''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\dg_efilm_153''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\hl7 db''
GO
BACKUP DATABASE [master] TO DISK = N''D:\mssql\backup\master\master_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''master_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO DISK = N''D:\mssql\backup\model\model_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''model_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO DISK = N''D:\mssql\backup\msdb\msdb_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''msdb_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [hl7 db_SamirTesting] TO DISK = N''D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [dg_efilm_153] TO DISK = N''D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [hl7 db] TO DISK = N''D:\mssql\backup\hl7 db\hl7 db_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10

Please help,

-tim

Check what kind of recovery model is adopted on the databases that are involved in this backup schedule.|||

I have this issue aswell but with the ReportServer database and the ReportServerTempDB database. ReportServer recovery mode is set to full

ReportServerTempDB recovery mode is set to simple.

We have a database maintenance plan set to back up all user databases (at the top of the window it says that simple will be ignored'. The maintenance plan runs but fails as it tries to back up the ReportServerTempDB database.

The messages in the history of the plan say…

9/5/2007 09:15 Database 'ReportServerTempDB' will not be backed up because it does not have its recovery model set to Full or Bulk Logged.

9/5/2007 09:15 Executing the query "BACKUP LOG [ReportServerTempDB] TO DISK = N'G:\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\ReportServerTempDB\\ReportServerTempDB_backup_200705091638.trn' WITH NOFORMAT, NOINIT, NAME = N'ReportServerTempDB_backup_20070509163838', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It appears that it reports it as an error when it checks the recovery mode, but then tries to back it up anyway. I do not have any problems with any other databases except the reporting services ones. At the moment I have both set to full recovery mode, just to get the maintenance plan to work. Any ideas anyone? This worked fine on Sp1 with the database set to Simple recover mode..

Thank you!

|||You cannot backup the system database and report server based databases in the transaction log schedule, as the error is obvious due to the configuration for system databases recovery model and you will not be able change that.

Recovery from a database Image

Hello,
First...this isn't a production recover issue.
I have a image of my hard drive that includes all my sql server databases
"in the raw". I suffered a hard disk crash and have reinstalled SQL Server
etc...How can I get these databases back. I tried just copying the two data
files from the "Data" directory off my backup onto the new SQL Server
install...but no cigar.
Surely there is a way to do this.
Regards,
Paul Reed
Jackson-Reed, Inc. www.jacksonreed.com
You can try to attach each database using sp_attach_db (or the Enterprise
Manager attach). This might not work if the databases weren't cleanly
detached, though. See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"paul" <paul@.discussions.microsoft.com> wrote in message
news:6515DCCF-A02E-4ACD-B59A-C22BB63D087B@.microsoft.com...
> Hello,
> First...this isn't a production recover issue.
> I have a image of my hard drive that includes all my sql server databases
> "in the raw". I suffered a hard disk crash and have reinstalled SQL Server
> etc...How can I get these databases back. I tried just copying the two
> data
> files from the "Data" directory off my backup onto the new SQL Server
> install...but no cigar.
> Surely there is a way to do this.
> Regards,
> Paul Reed
> --
> Jackson-Reed, Inc. www.jacksonreed.com
|||You might also
1. Open a call to MS PSS, maybe they have some magic ( but I doubt you'll be
able to recover.) Try them anyway, only $250
2. When you get past all of this, set up a good database maintenance plan to
backup and maintain your databases..
Good luck friend!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"paul" <paul@.discussions.microsoft.com> wrote in message
news:6515DCCF-A02E-4ACD-B59A-C22BB63D087B@.microsoft.com...
> Hello,
> First...this isn't a production recover issue.
> I have a image of my hard drive that includes all my sql server databases
> "in the raw". I suffered a hard disk crash and have reinstalled SQL Server
> etc...How can I get these databases back. I tried just copying the two
data
> files from the "Data" directory off my backup onto the new SQL Server
> install...but no cigar.
> Surely there is a way to do this.
> Regards,
> Paul Reed
> --
> Jackson-Reed, Inc. www.jacksonreed.com

Recovery from a database Image

Hello,
First...this isn't a production recover issue.
I have a image of my hard drive that includes all my sql server databases
"in the raw". I suffered a hard disk crash and have reinstalled SQL Server
etc...How can I get these databases back. I tried just copying the two data
files from the "Data" directory off my backup onto the new SQL Server
install...but no cigar.
Surely there is a way to do this.
Regards,
Paul Reed
--
---
Jackson-Reed, Inc. www.jacksonreed.comYou can try to attach each database using sp_attach_db (or the Enterprise
Manager attach). This might not work if the databases weren't cleanly
detached, though. See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"paul" <paul@.discussions.microsoft.com> wrote in message
news:6515DCCF-A02E-4ACD-B59A-C22BB63D087B@.microsoft.com...
> Hello,
> First...this isn't a production recover issue.
> I have a image of my hard drive that includes all my sql server databases
> "in the raw". I suffered a hard disk crash and have reinstalled SQL Server
> etc...How can I get these databases back. I tried just copying the two
> data
> files from the "Data" directory off my backup onto the new SQL Server
> install...but no cigar.
> Surely there is a way to do this.
> Regards,
> Paul Reed
> --
> ---
> Jackson-Reed, Inc. www.jacksonreed.com|||You might also
1. Open a call to MS PSS, maybe they have some magic ( but I doubt you'll be
able to recover.) Try them anyway, only $250
2. When you get past all of this, set up a good database maintenance plan to
backup and maintain your databases..
Good luck friend!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"paul" <paul@.discussions.microsoft.com> wrote in message
news:6515DCCF-A02E-4ACD-B59A-C22BB63D087B@.microsoft.com...
> Hello,
> First...this isn't a production recover issue.
> I have a image of my hard drive that includes all my sql server databases
> "in the raw". I suffered a hard disk crash and have reinstalled SQL Server
> etc...How can I get these databases back. I tried just copying the two
data
> files from the "Data" directory off my backup onto the new SQL Server
> install...but no cigar.
> Surely there is a way to do this.
> Regards,
> Paul Reed
> --
> ---
> Jackson-Reed, Inc. www.jacksonreed.com

Recovery from a database Image

Hello,
First...this isn't a production recover issue.
I have a image of my hard drive that includes all my sql server databases
"in the raw". I suffered a hard disk crash and have reinstalled SQL Server
etc...How can I get these databases back. I tried just copying the two data
files from the "Data" directory off my backup onto the new SQL Server
install...but no cigar.
Surely there is a way to do this.
Regards,
Paul Reed
--
---
Jackson-Reed, Inc. www.jacksonreed.comYou can try to attach each database using sp_attach_db (or the Enterprise
Manager attach). This might not work if the databases weren't cleanly
detached, though. See the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"paul" <paul@.discussions.microsoft.com> wrote in message
news:6515DCCF-A02E-4ACD-B59A-C22BB63D087B@.microsoft.com...
> Hello,
> First...this isn't a production recover issue.
> I have a image of my hard drive that includes all my sql server databases
> "in the raw". I suffered a hard disk crash and have reinstalled SQL Server
> etc...How can I get these databases back. I tried just copying the two
> data
> files from the "Data" directory off my backup onto the new SQL Server
> install...but no cigar.
> Surely there is a way to do this.
> Regards,
> Paul Reed
> --
> ---
> Jackson-Reed, Inc. www.jacksonreed.com|||You might also
1. Open a call to MS PSS, maybe they have some magic ( but I doubt you'll be
able to recover.) Try them anyway, only $250
2. When you get past all of this, set up a good database maintenance plan to
backup and maintain your databases..
Good luck friend!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"paul" <paul@.discussions.microsoft.com> wrote in message
news:6515DCCF-A02E-4ACD-B59A-C22BB63D087B@.microsoft.com...
> Hello,
> First...this isn't a production recover issue.
> I have a image of my hard drive that includes all my sql server databases
> "in the raw". I suffered a hard disk crash and have reinstalled SQL Server
> etc...How can I get these databases back. I tried just copying the two
data
> files from the "Data" directory off my backup onto the new SQL Server
> install...but no cigar.
> Surely there is a way to do this.
> Regards,
> Paul Reed
> --
> ---
> Jackson-Reed, Inc. www.jacksonreed.comsql

Recovering Sql server

I'm restoring the full sql server, what do I have to do
first restore all databases or restore master database?
Thanks in advance,
LuzTypically it would be:
Master - to get config details back
MSDB - to get backuphistory The rest - using the backuphistory from msdb
But it does depend a little on the exact situation that leads you to =restore it all!!
Good luck
Mike John
"luz" <luz_mariac@.hotmail.com> wrote in message =news:064501c344cd$88bba850$a101280a@.phx.gbl...
> I'm restoring the full sql server, what do I have to do > first restore all databases or restore master database?
> > Thanks in advance,
> Luz

Recovering SQL server

Hello all,
Last night we had 2 drives die on our test machine. The data (all our
databases)has been regretably lost. My question is;
How can I use the Production box and copy all the databases over to the
newly installed drives? At this point, I am unable to even start the sql
server service. SQL 7 on NT4 both machines.
Any advice on how to recover this nightmare would be appreciated.
Thanks in advance,
TomTom,
Use your backups instead of trying to move the database files. On case you, for some reason, don't want to use
the backups, you can try to copy the mdf, ndf and ldf files that constitutes each database and attach them to
the working SQL Server using sp_attach_db.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TLF" <T_DONOTSPAM_Furness@.simon.com> wrote in message news:uz2pqp5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> Hello all,
> Last night we had 2 drives die on our test machine. The data (all our
> databases)has been regretably lost. My question is;
> How can I use the Production box and copy all the databases over to the
> newly installed drives? At this point, I am unable to even start the sql
> server service. SQL 7 on NT4 both machines.
> Any advice on how to recover this nightmare would be appreciated.
> Thanks in advance,
> Tom
>|||Tibor,
Thanks for the fast response, but I think the problem is deeper that that.
I can not even get the instance of SQL server to start. When I try to start
the service I get an NT internal error. Could this be because I lost both
master and msdb databases as well as my user databases? I do have
backups(master,msdb,...), but without having SQL running , I cant restore
them. Any other suggestions?
Thanks for your time.
Tom
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uEzBcW6YEHA.3128@.TK2MSFTNGP09.phx.gbl...
> Tom,
> Use your backups instead of trying to move the database files. On case
you, for some reason, don't want to use
> the backups, you can try to copy the mdf, ndf and ldf files that
constitutes each database and attach them to
> the working SQL Server using sp_attach_db.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "TLF" <T_DONOTSPAM_Furness@.simon.com> wrote in message
news:uz2pqp5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > Hello all,
> >
> > Last night we had 2 drives die on our test machine. The data (all our
> > databases)has been regretably lost. My question is;
> > How can I use the Production box and copy all the databases over to the
> > newly installed drives? At this point, I am unable to even start the sql
> > server service. SQL 7 on NT4 both machines.
> > Any advice on how to recover this nightmare would be appreciated.
> > Thanks in advance,
> > Tom
> >
> >
>|||Check out the SQL Server errorlog.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TLF" <T_DONOTSPAM_Furness@.simon.com> wrote in message news:ui%23Zs4FZEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Tibor,
> Thanks for the fast response, but I think the problem is deeper that that.
> I can not even get the instance of SQL server to start. When I try to start
> the service I get an NT internal error. Could this be because I lost both
> master and msdb databases as well as my user databases? I do have
> backups(master,msdb,...), but without having SQL running , I cant restore
> them. Any other suggestions?
> Thanks for your time.
> Tom
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uEzBcW6YEHA.3128@.TK2MSFTNGP09.phx.gbl...
> > Tom,
> >
> > Use your backups instead of trying to move the database files. On case
> you, for some reason, don't want to use
> > the backups, you can try to copy the mdf, ndf and ldf files that
> constitutes each database and attach them to
> > the working SQL Server using sp_attach_db.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "TLF" <T_DONOTSPAM_Furness@.simon.com> wrote in message
> news:uz2pqp5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > > Hello all,
> > >
> > > Last night we had 2 drives die on our test machine. The data (all our
> > > databases)has been regretably lost. My question is;
> > > How can I use the Production box and copy all the databases over to the
> > > newly installed drives? At this point, I am unable to even start the sql
> > > server service. SQL 7 on NT4 both machines.
> > > Any advice on how to recover this nightmare would be appreciated.
> > > Thanks in advance,
> > > Tom
> > >
> > >
> >
> >
>

Recovering DBs with a "Suspect" state

Hello
(SQL Server 2000)
I have databases that appear as "Suspect" in the Enteprise Manager. What can I do to recover them or put them in a normal state?
Thanks a lot.Depends on why it is suspect. If you are lucky, someone simply moved the database files, and you just beed to move them back. if you have average luck, then you are looking at a database restore. What does the errorlog say?|||before you decide what to do, you ought to determine the cause.

but you can either...

a. you might be able to put your database in emergency mode and extract your data using bcp or dts.

or

b. restore from your last good backup.

I practice full recovery and can recover to any point in time, so I usually go with b and I have never done a, but I hear it is possible in some situations.

Recover the MSSQL Server 2000 from DLT Tape

Dear all,
I would like to ask if I can recover those databases from tape if I just
backup all the folders under the "C:\Program Files" ? I have export those
databases to files...
As my server's HDD is fail and I only get the DLT Tape back.
Please give me some advises!
Thanks all!if you have valid backup of c:\program files\ microsoft sql server\...\data
folder and your data files are in this folder you can attach the mdf and ldf
files to another servers.
Look for sp_attach_db in BOL
not sure what you meant with "I have export those databases to files..."
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:D8A6D646-6778-4DA1-AA50-7D50383E1B09@.microsoft.com...
> Dear all,
> I would like to ask if I can recover those databases from tape if I just
> backup all the folders under the "C:\Program Files" ? I have export those
> databases to files...
> As my server's HDD is fail and I only get the DLT Tape back.
> Please give me some advises!
> Thanks all!|||Thx for your reply!
actually, it's my typing mistake...
it should be "i haven't exported those database as a file"
"ME" wrote:
> if you have valid backup of c:\program files\ microsoft sql server\...\data
> folder and your data files are in this folder you can attach the mdf and ldf
> files to another servers.
> Look for sp_attach_db in BOL
> not sure what you meant with "I have export those databases to files..."
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:D8A6D646-6778-4DA1-AA50-7D50383E1B09@.microsoft.com...
> > Dear all,
> >
> > I would like to ask if I can recover those databases from tape if I just
> > backup all the folders under the "C:\Program Files" ? I have export those
> > databases to files...
> >
> > As my server's HDD is fail and I only get the DLT Tape back.
> >
> > Please give me some advises!
> >
> > Thanks all!
>
>|||so you were able to attach the mdf and ldf files?
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:FF8B6AC6-7F80-4D83-B14C-2A21235E56D8@.microsoft.com...
> Thx for your reply!
> actually, it's my typing mistake...
> it should be "i haven't exported those database as a file"
> "ME" wrote:
>> if you have valid backup of c:\program files\ microsoft sql
>> server\...\data
>> folder and your data files are in this folder you can attach the mdf and
>> ldf
>> files to another servers.
>> Look for sp_attach_db in BOL
>> not sure what you meant with "I have export those databases to files..."
>>
>> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
>> news:D8A6D646-6778-4DA1-AA50-7D50383E1B09@.microsoft.com...
>> > Dear all,
>> >
>> > I would like to ask if I can recover those databases from tape if I
>> > just
>> > backup all the folders under the "C:\Program Files" ? I have export
>> > those
>> > databases to files...
>> >
>> > As my server's HDD is fail and I only get the DLT Tape back.
>> >
>> > Please give me some advises!
>> >
>> > Thanks all!
>>

Recover the MSSQL Server 2000 from DLT Tape

Dear all,
I would like to ask if I can recover those databases from tape if I just
backup all the folders under the "C:\Program Files" ? I have export those
databases to files...
As my server's HDD is fail and I only get the DLT Tape back.
Please give me some advises!
Thanks all!if you have valid backup of c:\program files\ microsoft sql server\...\data
folder and your data files are in this folder you can attach the mdf and ldf
files to another servers.
Look for sp_attach_db in BOL
not sure what you meant with "I have export those databases to files..."
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:D8A6D646-6778-4DA1-AA50-7D50383E1B09@.microsoft.com...
> Dear all,
> I would like to ask if I can recover those databases from tape if I just
> backup all the folders under the "C:\Program Files" ? I have export those
> databases to files...
> As my server's HDD is fail and I only get the DLT Tape back.
> Please give me some advises!
> Thanks all!|||Thx for your reply!
actually, it's my typing mistake...
it should be "i haven't exported those database as a file"
"ME" wrote:

> if you have valid backup of c:\program files\ microsoft sql server\...\da
ta
> folder and your data files are in this folder you can attach the mdf and l
df
> files to another servers.
> Look for sp_attach_db in BOL
> not sure what you meant with "I have export those databases to files..."
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:D8A6D646-6778-4DA1-AA50-7D50383E1B09@.microsoft.com...
>
>|||so you were able to attach the mdf and ldf files?
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:FF8B6AC6-7F80-4D83-B14C-2A21235E56D8@.microsoft.com...[vbcol=seagreen]
> Thx for your reply!
> actually, it's my typing mistake...
> it should be "i haven't exported those database as a file"
> "ME" wrote:
>

Recover the MSSQL Server 2000 from DLT Tape

Dear all,
I would like to ask if I can recover those databases from tape if I just
backup all the folders under the "C:\Program Files" ? I have export those
databases to files...
As my server's HDD is fail and I only get the DLT Tape back.
Please give me some advises!
Thanks all!
if you have valid backup of c:\program files\ microsoft sql server\...\data
folder and your data files are in this folder you can attach the mdf and ldf
files to another servers.
Look for sp_attach_db in BOL
not sure what you meant with "I have export those databases to files..."
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:D8A6D646-6778-4DA1-AA50-7D50383E1B09@.microsoft.com...
> Dear all,
> I would like to ask if I can recover those databases from tape if I just
> backup all the folders under the "C:\Program Files" ? I have export those
> databases to files...
> As my server's HDD is fail and I only get the DLT Tape back.
> Please give me some advises!
> Thanks all!
|||Thx for your reply!
actually, it's my typing mistake...
it should be "i haven't exported those database as a file"
"ME" wrote:

> if you have valid backup of c:\program files\ microsoft sql server\...\data
> folder and your data files are in this folder you can attach the mdf and ldf
> files to another servers.
> Look for sp_attach_db in BOL
> not sure what you meant with "I have export those databases to files..."
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:D8A6D646-6778-4DA1-AA50-7D50383E1B09@.microsoft.com...
>
>
|||so you were able to attach the mdf and ldf files?
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:FF8B6AC6-7F80-4D83-B14C-2A21235E56D8@.microsoft.com...[vbcol=seagreen]
> Thx for your reply!
> actually, it's my typing mistake...
> it should be "i haven't exported those database as a file"
> "ME" wrote:

Recover from a nuked database

One of our apps has accidently nuked one of our live databases and for
some reason our last backup was 1 month ago
It will have done a drop schema then re-created the schema and added 2
test records
Is there any way of rolling back the database to the point before the
drop + create ?
The MDF and LDF are still the same size which means the logs are
intact
AdamWhat recovery model?
If simple, you are most probably toast.
If full, you might be able to do BACKUP LOG, then restore the most recent da
tabase backups and all
subsequent log backups where you specify STOPAT for the most recent log back
up. This assumes that
you have an unbroken chain of log records (didn't do things like BACKUP LOG
.. WITH TRUNCATE ONLY).
You could also check out some of the log reader tools and see if they can mi
ne out of the
transaction log, but of course they are totally dependent on the log records
being in the
transaction log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Adam // Internetware" <internetware@.gmail.com> wrote in message
news:1184081055.300348.184120@.22g2000hsm.googlegroups.com...
> One of our apps has accidently nuked one of our live databases and for
> some reason our last backup was 1 month ago
> It will have done a drop schema then re-created the schema and added 2
> test records
> Is there any way of rolling back the database to the point before the
> drop + create ?
> The MDF and LDF are still the same size which means the logs are
> intact
> Adam
>|||On Jul 10, 4:31 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> What recovery model?
> If simple, you are most probably toast.
> If full, you might be able to do BACKUP LOG, then restore the most recent
database backups and all
> subsequent log backups where you specify STOPAT for the most recent log ba
ckup. This assumes that
> you have an unbroken chain of log records (didn't do things like BACKUP LO
G ... WITH TRUNCATE ONLY).
> You could also check out some of the log reader tools and see if they can
mine out of the
> transaction log, but of course they are totally dependent on the log recor
ds being in the
> transaction log.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "Adam // Internetware" <internetw...@.gmail.com> wrote in messagenews:11840
81055.300348.184120@.22g2000hsm.googlegroups.com...
>
>
>
>
>
Looks like I'm Ok the Redgate log rescue seems to have picked up most
of the transaction log so I think I can rollback - give or take some
issues with FK's
Thx
Adam

Recover from a nuked database

One of our apps has accidently nuked one of our live databases and for
some reason our last backup was 1 month ago
It will have done a drop schema then re-created the schema and added 2
test records
Is there any way of rolling back the database to the point before the
drop + create ?
The MDF and LDF are still the same size which means the logs are
intact
Adam
What recovery model?
If simple, you are most probably toast.
If full, you might be able to do BACKUP LOG, then restore the most recent database backups and all
subsequent log backups where you specify STOPAT for the most recent log backup. This assumes that
you have an unbroken chain of log records (didn't do things like BACKUP LOG ... WITH TRUNCATE ONLY).
You could also check out some of the log reader tools and see if they can mine out of the
transaction log, but of course they are totally dependent on the log records being in the
transaction log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Adam // Internetware" <internetware@.gmail.com> wrote in message
news:1184081055.300348.184120@.22g2000hsm.googlegro ups.com...
> One of our apps has accidently nuked one of our live databases and for
> some reason our last backup was 1 month ago
> It will have done a drop schema then re-created the schema and added 2
> test records
> Is there any way of rolling back the database to the point before the
> drop + create ?
> The MDF and LDF are still the same size which means the logs are
> intact
> Adam
>
|||On Jul 10, 4:31 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> What recovery model?
> If simple, you are most probably toast.
> If full, you might be able to do BACKUP LOG, then restore the most recent database backups and all
> subsequent log backups where you specify STOPAT for the most recent log backup. This assumes that
> you have an unbroken chain of log records (didn't do things like BACKUP LOG ... WITH TRUNCATE ONLY).
> You could also check out some of the log reader tools and see if they can mine out of the
> transaction log, but of course they are totally dependent on the log records being in the
> transaction log.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Adam // Internetware" <internetw...@.gmail.com> wrote in messagenews:1184081055.300348.184120@.22g2000hsm.go oglegroups.com...
>
>
Looks like I'm Ok the Redgate log rescue seems to have picked up most
of the transaction log so I think I can rollback - give or take some
issues with FK's
Thx
Adam

Recover from a nuked database

One of our apps has accidently nuked one of our live databases and for
some reason our last backup was 1 month ago
It will have done a drop schema then re-created the schema and added 2
test records
Is there any way of rolling back the database to the point before the
drop + create ?
The MDF and LDF are still the same size which means the logs are
intact
AdamWhat recovery model?
If simple, you are most probably toast.
If full, you might be able to do BACKUP LOG, then restore the most recent database backups and all
subsequent log backups where you specify STOPAT for the most recent log backup. This assumes that
you have an unbroken chain of log records (didn't do things like BACKUP LOG ... WITH TRUNCATE ONLY).
You could also check out some of the log reader tools and see if they can mine out of the
transaction log, but of course they are totally dependent on the log records being in the
transaction log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Adam // Internetware" <internetware@.gmail.com> wrote in message
news:1184081055.300348.184120@.22g2000hsm.googlegroups.com...
> One of our apps has accidently nuked one of our live databases and for
> some reason our last backup was 1 month ago
> It will have done a drop schema then re-created the schema and added 2
> test records
> Is there any way of rolling back the database to the point before the
> drop + create ?
> The MDF and LDF are still the same size which means the logs are
> intact
> Adam
>|||On Jul 10, 4:31 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> What recovery model?
> If simple, you are most probably toast.
> If full, you might be able to do BACKUP LOG, then restore the most recent database backups and all
> subsequent log backups where you specify STOPAT for the most recent log backup. This assumes that
> you have an unbroken chain of log records (didn't do things like BACKUP LOG ... WITH TRUNCATE ONLY).
> You could also check out some of the log reader tools and see if they can mine out of the
> transaction log, but of course they are totally dependent on the log records being in the
> transaction log.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Adam // Internetware" <internetw...@.gmail.com> wrote in messagenews:1184081055.300348.184120@.22g2000hsm.googlegroups.com...
> > One of our apps has accidently nuked one of our live databases and for
> > some reason our last backup was 1 month ago
> > It will have done a drop schema then re-created the schema and added 2
> > test records
> > Is there any way of rolling back the database to the point before the
> > drop + create ?
> > The MDF and LDF are still the same size which means the logs are
> > intact
> > Adam
Looks like I'm Ok the Redgate log rescue seems to have picked up most
of the transaction log so I think I can rollback - give or take some
issues with FK's
Thx
Adam

Recover Databases after OS Crash

WE had a OS Crash and now we have to recover the databases.
We dont have any backup after we installed SQL Server. But
we have now all the data files *.mdf & *.ldf for all
databases including system databases.
Is there a way to recover the user and system databases.
Appreciate any help.
Thanks,
VCHi,
Easy way to recover the database after OS crash is,
1. After OS installation, Copy all .MDF and .LDF files to a new folder
(safe location)
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as old installation)
3. Stop the SQL server
4. Copy the .MDF and .LDF files (took in step 1) to the same folders (Same
as old installation).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
MCDBA
"VC" <anonymous@.discussions.microsoft.com> wrote in message
news:076001c46db4$a7183960$a501280a@.phx.gbl...
> WE had a OS Crash and now we have to recover the databases.
> We dont have any backup after we installed SQL Server. But
> we have now all the data files *.mdf & *.ldf for all
> databases including system databases.
> Is there a way to recover the user and system databases.
> Appreciate any help.
> Thanks,
> VC|||You can try sp_attach_db using the .mdf and ldf files. That is not
guaranteed since you didn't detach them beforehand. The files could be
corrupted by the server crash. If the log file(s) corrupted, you can also
try sp_attach_single_file_db. The fact that you don't have backups implies
that these databases are not important. Maybe you can find a way to recreate
them?
"VC" <anonymous@.discussions.microsoft.com> wrote in message
news:076001c46db4$a7183960$a501280a@.phx.gbl...
> WE had a OS Crash and now we have to recover the databases.
> We dont have any backup after we installed SQL Server. But
> we have now all the data files *.mdf & *.ldf for all
> databases including system databases.
> Is there a way to recover the user and system databases.
> Appreciate any help.
> Thanks,
> VC