Solved

Database Restore from .bak file

Posted on 2010-08-19
6
809 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 78

Accepted Solution

by:
arnold earned 250 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 125 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
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!

 
LVL 78

Assisted Solution

by:arnold
arnold earned 250 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 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 125 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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