Solved

Database Restore from .bak file

Posted on 2010-08-19
6
804 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
6 Comments
 
LVL 77

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 77

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CONVERT date time to a different time zone. 2 42
CROSS APPLY 4 43
Help with simplifying SQL 6 47
Near realtime alert if SQL Server services stop. 20 50
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

929 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now