August 19, 2010 at 10:51 am
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!
August 19, 2010 at 10:57 am
So are you trying to just restore a copy of the DB onto this server from the backup?
If so and you already created it with the data files in the appropriate places with the correct names, you could just:
RESTORE DATABASE WIND
FROM FILE='C:\wind.bak'
WITH REPLACE
August 19, 2010 at 11:05 am
Thanks SSC.
Can you give me the full query.
August 19, 2010 at 11:28 am
srathna77 (8/19/2010)
Thanks SSC.
Who?
Can you give me the full query.
That is the full query.
Do note that you don't need to create a database before doing a restore. A restore creates a database. Hence the CREATE step is completely unnecessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2010 at 11:52 am
This is throwing an error:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FILE'.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
August 19, 2010 at 11:59 am
Right click the "databases" folder in SSMS. Restore database, then select your file. If there are other files in the dialog, remove them.
Give it a new db name for now.
Does this work?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 19, 2010 at 12:11 pm
Whoops, my fault.
That was supposed to be FROM DISK not FROM FILE
Not nearly enough caffeine yet today..sorry about that.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply