Restore DB without Transaction Log

Hi,

How can I restore a DB without TRN log. As database transaction logs grows too much and I only want to restore the database.

Thanks

Shafiq

If you only restore the transaction log then the database will be transactionally inconsistent - the transaction log is an integral part of the backup.

What's the scenario context here?

BTW, what size DB and transaction log are we talking about here?

Thanks

|||

1. You must change your recovery model to simple and the backups wont record the transaction logs, this is the easiest way, but You shouldnt restore a backup without transaction logs, its supposed for you to have the info upto date until the failover.

|||

You should be wary about just going to simple recovery model. This is not suitable for production systems where loss of recent changes (in the event of a problem) is not acceptable.

This is because after a data backup in the simple recovery model, the transaction log is truncated. This means that its only possible to restore back to the time the last backup was completed - its not possible to do a point-in-time restore up to just before the problem occured so all work between those two points is lost.

See the SQL Server 2005 BOL entry for 'Overview of Simple Recovery' for more details.

Thanks

Restore db and logs from device to a point in time

When restoring a database and log files from a device the point in time
option disappears. How do I restore from a device to a point in time? Is
there an easy way to restore multiple logs from a device? It seems like
doing them one at a time is the only option and it's quite a pain.I recommend that you familiar yourself with the RESTORE command so you can
do this from Query Analyzer.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"David Morrison" <me@.nospam.com> wrote in message
news:uof2WquuDHA.1596@.TK2MSFTNGP10.phx.gbl...
> When restoring a database and log files from a device the point in time
> option disappears. How do I restore from a device to a point in time? Is
> there an easy way to restore multiple logs from a device? It seems like
> doing them one at a time is the only option and it's quite a pain.
>

Restore database with no log

I'm trying to restore a database backed up a production machine onto
my development machine. I don't want to restore the transaction logs
(there are two) because they are far too large and are unnecessary for
what I'm doing. I would be happy to simply create one new log in my
development environment. Therefore, I backed up the database using
the No_Log option as in

backup database CPTS to disk = 'd:\backups\cpts_backup_billeh.bak'
with no_log

I took the backup file and I'm able to restore it on my development
machine with

RESTORE DATABASE [CPTS] FILE = N'CPTS_Data' FROM DISK = N'D:\archives
\Citrix\CPTS\SupportingMaterials\cpts_backup_bille h.bak' WITH MOVE
'CPTS_Data' TO 'd:\databases\CPTS.mdf', MOVE 'CPTS_Log' TO 'd:
\databases\CPTS_log.ldf', MOVE 'CPTS_1_Log' TO 'd:\databases
\CPTS_1_log.ldf'

Note that I cannot additionally specify FILE = N'CPTS_Log' or FILE =
N'CPTS_1_Log' because I get an error.

The restore created the CPTS_Data file only and returned the message

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Processed 415664 pages for database 'CPTS', file 'CPTS_Data' on file 1.

The database cannot be recovered because the log was not restored.<<

What additional step should I take to create a log file? I thought
that No_Log was supposed to give me a truncated log file but it didn't
seem to do so.

Is there another way around this problem? I tried taking the the
resulting mdf file and attaching it using sp_attach_single_file_db but
this didn't work either.

Thanks,

Bill E.
Hollywood, FLI should add that both production and development environments are SQL
Server 2005|||Bill E. (billmiami2@.netscape.net) writes:

Quote:

Originally Posted by

I'm trying to restore a database backed up a production machine onto
my development machine. I don't want to restore the transaction logs
(there are two) because they are far too large and are unnecessary for
what I'm doing. I would be happy to simply create one new log in my
development environment. Therefore, I backed up the database using
the No_Log option as in
>...

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>Processed 415664 pages for database 'CPTS', file 'CPTS_Data' on file 1.

The database cannot be recovered because the log was not restored.<<
>
What additional step should I take to create a log file? I thought
that No_Log was supposed to give me a truncated log file but it didn't
seem to do so.

Books Online says about NO_LOG:

In the context of a BACKUP DATABASE statement, specifies that a backup
will not contain any log. This equates to how file backups were created
before SQL Server 2005. A database backup created with NO_LOG equates
to a full set of file backups that contains no log records.

Under the full recovery model, NO_LOG is useful if you need to back up
data quickly, and you have a complete sequence of log backups of that
data.

The last paragraph implies that you are expected to apply the transaction
logs from elsewhere.

There is a reason why this does not work as you had expected: you
see, that log serves a purpose. A database backup is taken online, which
means that pages can be updated while it's working. Therefore the
backup must include log records, so that transactions that were committed
when the backup was running can be rolled forward. And more importantly,
transactions that had not yet been committed when the backup completed,
must be rolled back. Thus a database restored from a backup with no log
content is in a inconsistent state, and thus you are not permitted in.

I would suggest that you do a regular backup/restore, and then shrink
the log files once you have restored the database and set the recovery
mode to simple. A tip is to add WITH COPY_ONLY to the BACKUP command.
That prevents the backup from being recorded as a "real" backup.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You're not being nosey. I don't know why this little PC was formatted
that way, but it was. It's a Windows x64 PC. I don't think that I'm
going to go through the trouble of reformatting the drives. This is
the first time that the issue has come arisen.

Bill

Restore Database With Move

OK. I have a good one. I have the following setup:
ServerA
Data on: C:\Data
Log on: C:\Logs
ServerB
System Databases on: D:\SysData
Data on: E:\Data
Indexes on : D:\Indexes
Log on: F:\Logs
So, what I want to do is restore a database from ServerA to ServerB. Placing the
files and data in the correct locations. I want the data in a filegroup called
DatabaseName_Data. Problem is when I restore to ServerB, the data always goes to
PRIMARY filegroup during restore. The only thing I can find is to manually
change the location of the data for each table in Enterprise Manager (YUCK!)
Isn't there an easier way?
Thanks for the help
DarrellYou cannot change file or filegroup layout using backup/restore (which basically gives you an image
of your database). Either use EM to move or do it from QA using more or less clever/complicated TSQL
scripts. You change location of a table by (re)creating a clustered index. For an index, you rebuild
the index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:ehhWgpTvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> OK. I have a good one. I have the following setup:
> ServerA
> Data on: C:\Data
> Log on: C:\Logs
> ServerB
> System Databases on: D:\SysData
> Data on: E:\Data
> Indexes on : D:\Indexes
> Log on: F:\Logs
> So, what I want to do is restore a database from ServerA to ServerB. Placing the files and data in
> the correct locations. I want the data in a filegroup called DatabaseName_Data. Problem is when I
> restore to ServerB, the data always goes to PRIMARY filegroup during restore. The only thing I can
> find is to manually change the location of the data for each table in Enterprise Manager (YUCK!)
> Isn't there an easier way?
> Thanks for the help
> Darrell|||Refer to "How to restore files to a new location (Transact-SQL)" in BOL...
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:ehhWgpTvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> OK. I have a good one. I have the following setup:
> ServerA
> Data on: C:\Data
> Log on: C:\Logs
> ServerB
> System Databases on: D:\SysData
> Data on: E:\Data
> Indexes on : D:\Indexes
> Log on: F:\Logs
> So, what I want to do is restore a database from ServerA to ServerB.
> Placing the files and data in the correct locations. I want the data in a
> filegroup called DatabaseName_Data. Problem is when I restore to ServerB,
> the data always goes to PRIMARY filegroup during restore. The only thing I
> can find is to manually change the location of the data for each table in
> Enterprise Manager (YUCK!)
> Isn't there an easier way?
> Thanks for the help
> Darrell