Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL backup

Posted on 2012-04-06
2
Medium Priority
?
437 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
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

581 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