Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL backup

Posted on 2012-04-06
2
Medium Priority
?
436 Views
Last Modified: 2012-06-27
Hi,

I did a backup of an SQL database using this code:

/* Perform a Full Backup of dbMT*/
Use master
GO
BACKUP DATABASE dbMT
TO DISK =N'C:\SQL_backup\dbMT.BAK'
WITH COMPRESSION
GO

/* Perform a Tail Log Backup of dbMT*/
BACKUP LOG dbMT
TO DISK =N'C:\SQL_backup\dbMT.TRN'
WITH COMPRESSION, NORECOVERY
GO

After that the database was stuck in stuck in “Restoring” state. I had to run this code to release it:

RESTORE DATABASE dbMT
   FROM DISK = N'C:\SQL_backup\dbMT.BAK'
   WITH REPLACE,RECOVERY

How can I do the backup without this happening?

Best regards
RTSol
0
Comment
Question by:RTSol
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 37815417
Leave off the NORECOVERY when you do the tail log backup
0
 

Author Closing Comment

by:RTSol
ID: 37815492
Thanks - that made the trick.

Best regards
RTSol
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question