Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I backup my database and I Mistakenly saved it without a name.

what happened is that instead of replace the last file it add to it the last backup.

now I have a .bak file that has 74 backups enter image description here

I want to restore the db in position 74 with query ,since restore from the wizrad always return "db in use"

I tried

alter database <dbname>
set single_user with rollback immediate

with no luck,so my only option is throw query window

any idea how to restore the one db from the file in position 74?

Thanks for any help

Baaroz

share|improve this question

migrated from stackoverflow.com Dec 2 '13 at 18:54

This question came from our site for professional and enthusiast programmers.

    
This site is for programming questions. We are not SQL server support. Try the DBA site. –  Marc B Dec 2 '13 at 17:09

2 Answers 2

up vote 0 down vote accepted

It doesn't matter how many backups are there. You would have the same error message if there was only one backup in the backup set.

I would either restore the backup 74 under different database name (put its name in "To database" textbox), or would rename current database to some other name, e.g. spetztest_OLD and restore the backup under original name. Then you can make sure you don't need the old one and drop it.

share|improve this answer

try the following:

USE [MASTER]
GO
ALTER DATABASE [spetztest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [spetztest] FROM  DISK = N'c:\sdk.bak' WITH FILE = 74,  REPLACE,  STATS = 1
GO

Make sure that your session that you are using to do the restore is not using the database you want to restore.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.