Solved

Database Restore from .bak file

Posted on 2010-08-19
6
802 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 76

Accepted Solution

by:
arnold earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 76

Assisted Solution

by:arnold
arnold earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks All
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

763 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

6 Experts available now in Live!

Get 1:1 Help Now