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

x
?
Solved

Database Restore from .bak file

Posted on 2010-08-19
6
Medium Priority
?
815 Views
Last Modified: 2012-05-10
Hi,

I have a backup file from an old server which has sql server 2005 database on it on to new server in c folder.

I created a empty database like this

USE [master]
GO

/****** Object: Database [Wind] Script Date: 08/19/2010 11:46:02 ******/
CREATE DATABASE [Wind] ON PRIMARY
( NAME = N'wind', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Wind.mdf' , SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'wind', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Wind_log.LDF' , SIZE = 832KB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%)
GO

ALTER DATABASE [Wind] SET COMPATIBILITY_LEVEL = 90
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Wind].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [Wind] SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [Wind] SET ANSI_NULLS OFF
GO

ALTER DATABASE [Wind] SET ANSI_PADDING OFF
GO

ALTER DATABASE [Wind] SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [Wind] SET ARITHABORT OFF
GO

ALTER DATABASE [Wind] SET AUTO_CLOSE OFF
GO

ALTER DATABASE [Wind] SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [Wind] SET AUTO_SHRINK OFF
GO

ALTER DATABASE [Wind] SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [Wind] SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [Wind] SET CURSOR_DEFAULT GLOBAL
GO

ALTER DATABASE [Wind] SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [Wind] SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [Wind] SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [Wind] SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [Wind] SET ENABLE_BROKER
GO

ALTER DATABASE [Wind] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [Wind] SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [Wind] SET TRUSTWORTHY OFF
GO

ALTER DATABASE [Wind] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [Wind] SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [Wind] SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [Wind] SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [Wind] SET READ_WRITE
GO

ALTER DATABASE [Wind] SET RECOVERY FULL
GO

ALTER DATABASE [Wind] SET MULTI_USER
GO

ALTER DATABASE [Wind] SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [Wind] SET DB_CHAINING OFF
GO


I have a .bak file from a database backup from sql server 2005 in c:/wind.bak, i want to restore this back up file to a primary file.

How can i acheive this?

Or Is there a way to re-create the database from the backup?(it's a full
backup)

I'm working with MSSQL Server 2008 and Management Studio.

PsNote: Any changes in creation of database if necessary please advice me .

Thank you for any advice!
0
Comment
Question by:SaiRam77
[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
6 Comments
 
LVL 80

Accepted Solution

by:
arnold earned 1000 total points
ID: 33477541
All you do is run the restore process.
using SSMS
http://msdn.microsoft.com/en-us/library/ms177429.aspx

Or using SQL commands
 
http://msdn.microsoft.com/en-us/library/ms186858.aspx
0
 
LVL 12

Assisted Solution

by:mcv22
mcv22 earned 500 total points
ID: 33477563
Use the restore command in a new query window:

RESTORE DATABASE [Wind] FROM DISK = 'c:\wind.bak' WITH RECOVERY, REPLACE

If the paths to the database files wind and wind_log need to change, add the MOVE option:

RESTORE DATABASE [Wind] FROM DISK = 'c:\wind.bak' WITH MOVE 'wind' TO 'c:\somenewfolder\wind.mdb', MOVE 'wind_log' TO 'c:\somenewfolder\wind_log.ldb', RECOVERY, REPLACE

To get a list of database files contained in the backup file, use something like the following:

RESTORE FILELISTONLY FROM DISK = 'c:\wind.bak'
0
 

Author Comment

by:SaiRam77
ID: 33478160
When i use the following syntax

RESTORE DATABASE [Wind] FROM DISK = 'c:\wind.bak' WITH RECOVERY, REPLACE

It is throwing me the following error:

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'c:\wind.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 80

Assisted Solution

by:arnold
arnold earned 1000 total points
ID: 33478293
check the permissions on c:\wind.bak it needs to have rights for the sql service.
Copy the file to default install:
c:\program files\microsoft sql server\mssql\mssql.1\backup\

and  try it again.

Do you have the option to use SSMs (graphical interface)?
The other issue deals with where the files should be restored.  i.e. the backup is from a system where the data files were on drive E: which this system does not have.
RESTORE DATABASE [Wind], filegroup='c:\program files\mssql\data'  FROM DISK = 'c:\wind.bak' WITH RECOVERY, REPLACE

Ref: http://msdn.microsoft.com/en-us/library/ms186858.aspx
0
 
LVL 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 500 total points
ID: 33479286
It looks like from the message that the path c:\ exists, but the SQL Server service could not find any file there, you would get an access denied error if there were no permissions to open it.  If you go to the C:\ on the SQL Server itself and look for the wind.bak file it should exist.  If the file is on your local C:\ it will never be able to see it, it has to be on the local drive of the machine that SQL Server is running on.

To deal with any errors, you first have to find your backup file.
0
 

Author Closing Comment

by:SaiRam77
ID: 33598340
Thanks All
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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

610 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