Restoring .bak files using query Analyzer [sql server]

February 16, 2008 at 1:42 pm 5 comments

I was wondering how to restore BAK files using query Analyzer as most of the hosting services will provide query Analyzer.

I got a script which works wonder and it’s damn simple.

1. create .bak file using Enterprise Manager

2. Store that in some location

3. Open Query analyzer and execute below command.

Use Master
Alter Database db_Name
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE db_Name
FROM DISK = ‘D:\dbName.bak’

That’s it here we go………..

One more solution is installing third party software http://www.sqlscripter.com/

About these ads

Entry filed under: SQL. Tags: .

Toll free Numbers Mozilla Addons and IE Addons for Webdevelopers

5 Comments Add your own

  • 1. Raj kumar  |  November 14, 2008 at 7:21 am

    Thanks for the query.
    works perfact

    Reply
  • 2. Mario  |  February 3, 2009 at 3:24 pm

    Database YourDB has full backup YourBaackUpFile.bak. It can be restored using following two steps.

    Step 1: Retrive the Logical file name of the database from backup.

    RESTORE FILELISTONLY
    FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
    GO

    Step 2: Use the values in the LogicalName Column in following Step.

    —-Make Database to single user Mode
    ALTER DATABASE YourDB
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE
    —-Restore Database
    RESTORE DATABASE YourDB
    FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
    WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
    MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’
    /*If there is no error in statement before database will be in multiuser
    mode.
    If error occurs please execute following command it will convert
    database in multi user.*/
    ALTER DATABASE YourDB SET MULTI_USER
    GO

    Reply
  • 3. steve  |  May 28, 2009 at 11:15 pm

    Can one restore just certain tables and data?

    Reply
  • 4. more resources  |  April 24, 2013 at 6:08 am

    Right here is the perfect webpage for anybody who hopes to understand
    this topic. You know a whole lot its almost tough to argue with you (not that I
    actually would want to…HaHa). You certainly put a brand new spin on a topic which has been written about for
    a long time. Wonderful stuff, just wonderful!

    Reply
  • 5. aura kingdom gold  |  January 30, 2014 at 4:08 pm

    Having read this I thought it was rather
    informative. I appreciate you taking the time and effort to put this article together.
    I once again find myself spending a significant amount of time both reading and
    leaving comments. But so what, it was still worth
    it!

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Flickr Photos

Dead Standing

大棧橋展望横浜港寬景

Small

More Photos

Blog Stats

  • 626,276 hits

Follow

Get every new post delivered to your Inbox.

Join 35 other followers

%d bloggers like this: