Database Restore from .bak file

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!
SaiRam77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcv22Commented:
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
SaiRam77Author Commented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

arnoldCommented:
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
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
SaiRam77Author Commented:
Thanks All
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.