REcovery model for a backup/restore operation

Hello,
I want to do a backup and restore operations of my base, using SQLCMD.exe.
For that, I use backup database mybase to disk='File'
Then, I try to do a backup of transact log but like this:
backup log mybase to disk='File'
But I have this message : Cannot do a backup log on database which is a
simple recovery model.
I read the BOL and I saw there are to type of recovery model: Simple and
full. But I didn't understand very well explications in BOL.
My questions are:
What are this models?
When are they used ?
ThanksIn simple mode, not very much is logged in the transaction log. In full,
almost everything is logged in the transaction log. Thats the basic
difference. Theres a whole lot more to it, so it wouldn be a bad idea to
read up on it.
oh yeah, theres also bulk logged recovery model :)
MC
"bubixx" <bubixx@.discussions.microsoft.com> wrote in message
news:A0AF25DC-B42D-4AF4-9C3E-0F5722B689F8@.microsoft.com...
> Hello,
> I want to do a backup and restore operations of my base, using SQLCMD.exe.
> For that, I use backup database mybase to disk='File'
> Then, I try to do a backup of transact log but like this:
> backup log mybase to disk='File'
> But I have this message : Cannot do a backup log on database which is a
> simple recovery model.
> I read the BOL and I saw there are to type of recovery model: Simple and
> full. But I didn't understand very well explications in BOL.
> My questions are:
> What are this models?
> When are they used ?
> Thanks
>|||Hi, bubixx.
http://vyaskn.tripod.com/ sql_serve...ices
.htm
In this URL you will find a very good white paper about backups and other
administration practices.
Good luck.
Pau.
"bubixx" <bubixx@.discussions.microsoft.com> escribi en el mensaje
news:A0AF25DC-B42D-4AF4-9C3E-0F5722B689F8@.microsoft.com...
> Hello,
> I want to do a backup and restore operations of my base, using SQLCMD.exe.
> For that, I use backup database mybase to disk='File'
> Then, I try to do a backup of transact log but like this:
> backup log mybase to disk='File'
> But I have this message : Cannot do a backup log on database which is a
> simple recovery model.
> I read the BOL and I saw there are to type of recovery model: Simple and
> full. But I didn't understand very well explications in BOL.
> My questions are:
> What are this models?
> When are they used ?
> Thanks
>|||> In simple mode, not very much is logged in the transaction log. In full, almost everythin
g is
> logged in the transaction log.
IMO, that is too much of a simplification. For all but a few special operati
ons, the amount logged
is the same for all recovery models. In simple, SQL Server will by itself em
pty the log each time it
does a checkpoint. In full mode, you have to empty the log using BACKUP LOG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message news:uOAqxL07FHA.2092@.TK2MSFTNGP12.phx.gb
l...
> In simple mode, not very much is logged in the transaction log. In full, a
lmost everything is
> logged in the transaction log. Thats the basic difference. Theres a whole
lot more to it, so it
> wouldn be a bad idea to read up on it.
> oh yeah, theres also bulk logged recovery model :)
>
> MC
> "bubixx" <bubixx@.discussions.microsoft.com> wrote in message
> news:A0AF25DC-B42D-4AF4-9C3E-0F5722B689F8@.microsoft.com...
>|||Well, thank you for that.
I thought to simplify as much as I can, since I didnt think that I should go
into explaining the transaction log as such. You managed to write a really
short explanation though, I'll have to use it next time someone asks me to
explain the difference ;).
MC
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eDetSr17FHA.2816@.tk2msftngp13.phx.gbl...
> IMO, that is too much of a simplification. For all but a few special
> operations, the amount logged is the same for all recovery models. In
> simple, SQL Server will by itself empty the log each time it does a
> checkpoint. In full mode, you have to empty the log using BACKUP LOG.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:uOAqxL07FHA.2092@.TK2MSFTNGP12.phx.gbl...
>|||> I thought to simplify as much as I can, since I didnt think that I should go into explain
ing the
> transaction log as such.
I figured that was the case. :-)

> , I'll have to use it next time someone asks me to explain the difference ;).[/col
or]
Feel free... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message news:%23Sk2Iv17FHA.3660@.TK2MSFTNGP09.phx.
gbl...
> Well, thank you for that.
> I thought to simplify as much as I can, since I didnt think that I should
go into explaining the
> transaction log as such. You managed to write a really short explanation t
hough, I'll have to use
> it next time someone asks me to explain the difference ;).
>
> MC
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eDetSr17FHA.2816@.tk2msftngp13.phx.gbl...
>|||Are you the admin of this server? I assume since you are performing backups.
If you are not sure about the difference between 'full' and 'simple'
recovery and how it affects backups, then it would be best to leave the
recovery model at it's default of 'full'.
SQL Server 2000 Administrator's Pocket Consultant: Database Backup and
Recovery
http://www.microsoft.com/technet/pr...s/c11ppcsq.mspx
SQL Server 2000 Operations Guide: System Administration
http://www.microsoft.com/technet/pr...in/sqlops4.mspx
"bubixx" <bubixx@.discussions.microsoft.com> wrote in message
news:A0AF25DC-B42D-4AF4-9C3E-0F5722B689F8@.microsoft.com...
> Hello,
> I want to do a backup and restore operations of my base, using SQLCMD.exe.
> For that, I use backup database mybase to disk='File'
> Then, I try to do a backup of transact log but like this:
> backup log mybase to disk='File'
> But I have this message : Cannot do a backup log on database which is a
> simple recovery model.
> I read the BOL and I saw there are to type of recovery model: Simple and
> full. But I didn't understand very well explications in BOL.
> My questions are:
> What are this models?
> When are they used ?
> Thanks
>

Recovery model backup and restore database

I want to do a backup and restore operations of my base, using SQLCMD.exe.

For that, I use backup database mybase to disk='File'

Then, I try to do a backup of transact log but like this:

backup log mybase to disk='File'

But I have this message : Cannot do a backup log on database which is a simple recovery model.

I read the BOL and I saw there are to type of recovery model: Simple and full. But I didn't understand very bien explications in BOL.

My questions are:

What is this models?

When are they used ?

ThanksRecovery model determines how the transaction log files are handled in the database. This is in turn affects performance, log file usage, backup/recovery, amount of data loss etc. Please refer to topic below for overview:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/606e0487-0c76-4875-af44-ef763cc5a73a.htm

You should also look at the transaction log architecture topic and why it is necessary in a relational database system.

recovery a SQL Server db from mdf/Ldf files

hi to all,

I'm newbie with Sql Server,

I've a serious problem. After a crash of one of my disk I loose all the data about the SQL Server db but I've the mdf and ldf files because were placed on another hard disk different from the SQL Serve Installation disk. I reinstalled the Sql Server (in a new path, a new hard disk letter) but I don't know how to rebuild the db starting from the mdf/ldf files. Is it possible?

Please help me, I'm in a ugly Situation.

If you can access the mdf/ldf files, simply doing an attach will do the trick ... sometimes|||

can you teach me how to attach the files? please help me, I need a solution for this problem

thanks a lot

Claudio

|||

What version are you using...SQL Server 2000 or 2005?

|||...I'm using SQL Server 2000 developer + sp3|||Open Books Online. Do a search for Attach. It will have instructions and sample code.

Recovering from hard disk crash

SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
Don
Hi, Don! As long as the db files are not corrupt, you can try using the sp_attach_db after you reinstall. See BOL for details. HTH. =)
|||Prerequisite:
Copy all the MDF and LDF files (User databases and System databases) to a safe directory,
Installation:
Since the C drive is crashed, you have to Install the Windows NT 4.0 with the same service pack.
After that install SQL 7.0 with same directory structure and the same service pack as before.
Now follow the below steps:-
1. Stop the SQL Server
2. Move the current MDF and LDF files for system databases (Created on the new installation) to a new folder
3. Copy back all the MDF and LDF files to the same old folders ( Files copied in prerequisite step)
4. Start the SQL server and check all the databases are accessible.
The above step will work fine if you have the system files safe.
If the above steps fail then you will have to restore from good backup files.
-- Don wrote: --
SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
Don

Recovering from hard disk crash

SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
DonHi, Don! As long as the db files are not corrupt, you can try using the sp_a
ttach_db after you reinstall. See BOL for details. HTH. =)|||Prerequisite:
Copy all the MDF and LDF files (User databases and System databases) to a sa
fe directory,
Installation:
Since the C drive is crashed, you have to Install the Windows NT 4.0 with th
e same service pack.
After that install SQL 7.0 with same directory structure and the same servic
e pack as before.
Now follow the below steps:-
1. Stop the SQL Server
2. Move the current MDF and LDF files for system databases (Created on the n
ew installation) to a new folder
3. Copy back all the MDF and LDF files to the same old folders ( Files copie
d in prerequisite step)
4. Start the SQL server and check all the databases are accessible.
The above step will work fine if you have the system files safe.
If the above steps fail then you will have to restore from good backup files
.
-- Don wrote: --
SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
Don

Recovering from hard disk crash

SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
DonHi, Don! As long as the db files are not corrupt, you can try using the sp_attach_db after you reinstall. See BOL for details. HTH. =)|||Prerequisite
Copy all the MDF and LDF files (User databases and System databases) to a safe directory
Installation
Since the C drive is crashed, you have to Install the Windows NT 4.0 with the same service pack.
After that install SQL 7.0 with same directory structure and the same service pack as before
Now follow the below steps:
1. Stop the SQL Server
2. Move the current MDF and LDF files for system databases (Created on the new installation) to a new folde
3. Copy back all the MDF and LDF files to the same old folders ( Files copied in prerequisite step
4. Start the SQL server and check all the databases are accessible
The above step will work fine if you have the system files safe
If the above steps fail then you will have to restore from good backup files
-- Don wrote: --
SQL 7.0/NT 4.
The C drive has crashed. The MDF and LDF files are on
Drive D
Is there any path to recovery
Thanks
Do

Recovering a database

Our disk was corrupted and all we maneged to get was the mdf file of a
database (ldf file is lost).
Can I get at least the structure of a database from mdf file or everything
is lost?
regards,Ssimon
Did you do BACKUP of the database, didn't you?
Try to attach a .MDF file by using sp_attach_single_file_db but it might not
work because you did not detach the database firstly
This one I have just found on internet
-- Encountered a situation where the physical log files
-- for a database were deleted and as a result the
-- database was marked suspect.
-- This was the solution to overcoming that issue. The
-- databases in question successfully came back up on
-- line at the end of this.
select dbid, status, name
from sysdatabases
sp_configure 'allow updates', 1
go
reconfigure with override
go
-- puts in emergency repair mode
update sysdatabases
set status = 32768
where dbid = 8
go
-- stop and start sql server
dbcc rebuild_log ('database_name','physical_path_to_log_f
ile')
-- puts in single user mode
update sysdatabases
set status = 4096
where dbid = 8
-- stop and start sql server
use database_name
dbcc checkdb('database_name')
-- reset the status
update master..sysdatabases
set status = 0
where dbid = 8
sp_configure 'allow updates', 0
go
reconfigure with override
go
-- stop and start sql server
select dbid, status
from master..sysdatabases
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:uYVfCowpFHA.616@.TK2MSFTNGP15.phx.gbl...
> Our disk was corrupted and all we maneged to get was the mdf file of a
> database (ldf file is lost).
> Can I get at least the structure of a database from mdf file or everything
> is lost?
> regards,S
>|||<snip>
> This one I have just found on internet
<snip>
> dbcc rebuild_log ('database_name','physical_path_to_log_f
ile')
<snip>
I suggest you also warn about the downsides using this command. It doesn't c
heck for any type of
consistency. You can have half-finished transactions, missing rollbacks etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23GiWUswpFHA.3084@.TK2MSFTNGP09.phx.g
bl...
> simon
> Did you do BACKUP of the database, didn't you?
> Try to attach a .MDF file by using sp_attach_single_file_db but it might n
ot work because you did
> not detach the database firstly
>
> This one I have just found on internet
> -- Encountered a situation where the physical log files
> -- for a database were deleted and as a result the
> -- database was marked suspect.
> -- This was the solution to overcoming that issue. The
> -- databases in question successfully came back up on
> -- line at the end of this.
> select dbid, status, name
> from sysdatabases
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> -- puts in emergency repair mode
> update sysdatabases
> set status = 32768
> where dbid = 8
> go
> -- stop and start sql server
> dbcc rebuild_log ('database_name','physical_path_to_log_f
ile')
> -- puts in single user mode
> update sysdatabases
> set status = 4096
> where dbid = 8
> -- stop and start sql server
> use database_name
> dbcc checkdb('database_name')
> -- reset the status
> update master..sysdatabases
> set status = 0
> where dbid = 8
> sp_configure 'allow updates', 0
> go
> reconfigure with override
> go
> -- stop and start sql server
> select dbid, status
> from master..sysdatabases
>
> "simon" <simon.zupan@.stud-moderna.si> wrote in message
> news:uYVfCowpFHA.616@.TK2MSFTNGP15.phx.gbl...
>|||thanks for your answers.
How can I rebuild log if I don't have log file?
I have only mdb file.
regards,S
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OxEKCmxpFHA.4056@.TK2MSFTNGP10.phx.gbl...
> <snip>
> <snip>
> <snip>
> I suggest you also warn about the downsides using this command. It doesn't
> check for any type of consistency. You can have half-finished
> transactions, missing rollbacks etc.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23GiWUswpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>|||there's no magic. If SQL Server has recovery work to perform, and you don't
have the ldf file, you
don't have the information needed to perform that recovery. Either live with
an inconsistent
database, or restore from a good backup. If you do decide to live with a pos
sibly inconsistent
database, I suggest opening a case with MS Support as they might have inform
ation that can be
relevant to you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"simon" <simon.zupan@.stud-moderna.si> wrote in message news:Ob6plYypFHA.3568@.TK2MSFTNGP10.p
hx.gbl...
> thanks for your answers.
> How can I rebuild log if I don't have log file?
> I have only mdb file.
> regards,S
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OxEKCmxpFHA.4056@.TK2MSFTNGP10.phx.gbl...
>

Recover stored procedures

Before my hard disk failed , I had backup MSSQL7 folder.After I re format it and attached the database [MDF ,LDF ] I lost my stored procedures.Can someone help me or show me how to recover my stored procedures.
Thanks.The SPs are part of the database and should be there if the database is not corrupt.
Maybe they are owned by a different user or mapped to a different login.
Try select * from sysobjects where type = 'P'
and look in syscomments

select text from syscomments where text like '%myspname%'|||Thanks for your fast response , I am so lucky that get back my 150 stored procedures back from my old 500 MB hard disk !!!

Thanks.sql

Recover RDL file

Hello,
I've got a huge problem with SQL Server 2005. I've lost the RDL file built
with SQL Server BI Studio (developper hard disk format, with no backup), and
the only thing that I've got now is the server itself, with the ReportServer
database.
Is it possible to re-create a RDL file from the tables in this database?
Thanks for your help.
JN.In report manager click on the report, properties tab, click the edit button
under Report Definition. Give is a local directory (different from where the
project will be). Then create a new project and add and existing item to it.
It will copy the rdl over into the project directly. Not very discoverable
but works as advertized.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jean-Nicolas BERGER" <j-n.enlevezmoi.berger@.club-internet.fr> wrote in
message news:O3BwKmCMHHA.3668@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I've got a huge problem with SQL Server 2005. I've lost the RDL file built
> with SQL Server BI Studio (developper hard disk format, with no backup),
> and the only thing that I've got now is the server itself, with the
> ReportServer database.
> Is it possible to re-create a RDL file from the tables in this database?
> Thanks for your help.
> JN.
>

Recover A Database From Another Hard Disk With A Corrupted Windows 2000 Installation.

Hello,

I am trying to recover a SQL Server 7 database from another hard disk
drive that has a corrupted Windows 2000 Advanced Server installation. I
am not able to repair the corrupted Windows 2000 Advanced Server
installation but the file system is intact. I have installed a new copy
of SQL Server 7 onto a new hard disk and have used the sp_attach_db
system stored procedure to attach the database from the old hard drive
into the new installation of SQL Server 7 on the new hard drive. The
database shows up in the Enterprise Manager View and all the data and
structure are there, but when I try to run a Web site that uses ODBC
connections to the database I get the following error:

ODBC Error Code = 08001 (Unable to connect to data source)

[Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server
not found.

I have double-checked that all the NTFS permissions, ODBC DSNs, and Web
Server settings are correct and when I run the SQL Server ODBC driver
test at the completion of each ODBC DSN setup, it is successfull.

1) Why am I getting this ODBC error?

2) What steps have I missed in the recovery process?

3) If I have done something wrong recovering my database, can I still
recover it since I still have: a) The old master database file and log
file; b) The old database (non-master) file and log file; c)A backed-up
(Enterprise Manager -> Right Click A Database -> All Tasks -> Backup
Database) copy of the old database (non-master) file; ?

My System Specifications:
-Windows 2000 Advanced Server
-SQL Server 7
-IIS 5.0
-Cold Fusion Server 4.5 Professional Full Edition
Thanks For Your Time,

NathanIn my first post, I gave the wrong error. The error should read:

ODBC Error Code = S0002 (Base table not found)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'dboLogs'.

Sorry for the inconvenience. -Nate|||Nevermind...I have solved the problem myself. The problem was in the
ODBC DSN configuration. When I setup a SQL Server 7 ODBC DSN, there is
a checkbox "Change the default database to:" with a disabled drop-down
list showing the name of the database that I was trying to connect to.
The way the name is displayed, it looks as though SQL Server is
connecting to this database by default. In reality, I had to checkmark
the box for SQL Server to connect to this database.

I hope this helps the next database professional from wasting as much
time as I (...'d hate to admit that I...) did.