January 28, 2010 at 11:57 am
1. ) We have a backup file ( Full back up ) from our prod database as of yesterday.
2.) I know that our PROD database tables belong to several file groups and uses
a SAN system for storage.
Here is my question:
3.) If I take the full backup file and try to restore it in a brand new server, do I have to
first create file groups or will the restore process automatically create the file groups
for me ? Keep in mind I am restoring it into a server that has just one hard drive ( No SAN system )
January 28, 2010 at 12:21 pm
If the new server is a different configuration, you will need to use the MOVE clause in the restore to move the files to their new location on the new server.
January 28, 2010 at 1:35 pm
Not sure what you mean.
The new server is brand new.
There is just 2 drives ( C and D )
D where SQl sever software sits.
So then, I am free to create 3 directories and put the 3 files in any location that I
like it to be. Is that right ?
January 28, 2010 at 1:49 pm
Where do the database files (mdf/ndf/ldf) exist on the source server and what are their logical names?
January 29, 2010 at 1:18 pm
The following is the script obtained from SQl sever 2005.
This will tell you where each file is.
question: The new sever only has 2 drives. I really don't care if all 3 files can exist in the
D drive. So what will be the syntax for the restore command
USE [master]
GO
/****** Object: Database [NGProd] Script Date: 01/29/2010 15:16:02 ******/
CREATE DATABASE [NGProd] ON PRIMARY
( NAME = N'NextGen_System_Data', FILENAME = N'K:\SQL Data Files DBI01\MSSQL.1\MSSQL\Data\NGProd_System_data.mdf' , SIZE = 287168KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [NEXTGEN_CORE]
( NAME = N'NextGen_Core_Data_1', FILENAME = N'K:\SQL Data Files DBI01\MSSQL.1\MSSQL\Data\NGProd_Core_Data_1.ndf' , SIZE = 48391424KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [NEXTGEN_INDEX_1]
( NAME = N'NextGen_Index_1', FILENAME = N'K:\SQL Data Files DBI01\MSSQL.1\MSSQL\Data\NGProd_Index_1.ndf' , SIZE = 46539520KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'NextGen_Log', FILENAME = N'L:\SQL Log Files DBI01\NGProd_Log.ldf' , SIZE = 5714112KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'NGProd', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [NGProd].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [NGProd] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [NGProd] SET ANSI_NULLS OFF
GO
ALTER DATABASE [NGProd] SET ANSI_PADDING OFF
GO
ALTER DATABASE [NGProd] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [NGProd] SET ARITHABORT OFF
GO
ALTER DATABASE [NGProd] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [NGProd] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [NGProd] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [NGProd] SET AUTO_UPDATE_STATISTICS OFF
GO
ALTER DATABASE [NGProd] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [NGProd] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [NGProd] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [NGProd] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [NGProd] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [NGProd] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [NGProd] SET ENABLE_BROKER
GO
ALTER DATABASE [NGProd] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [NGProd] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [NGProd] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [NGProd] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [NGProd] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [NGProd] SET READ_WRITE
GO
ALTER DATABASE [NGProd] SET RECOVERY FULL
GO
ALTER DATABASE [NGProd] SET MULTI_USER
GO
ALTER DATABASE [NGProd] SET PAGE_VERIFY TORN_PAGE_DETECTION
GO
ALTER DATABASE [NGProd] SET DB_CHAINING OFF
January 29, 2010 at 2:05 pm
Here is a restore command. You will have to make appropriate changes to match your actual environment.
restore database NGProd
from disk = N'D:\Backups\NGProd.bak' -- assumes the file is local
with file = 1,
move 'NextGen_System_Data' to 'D:\Databases\NGProd\NGProd_System_data.mdf',
move 'NextGen_Core_Data_1' to 'D:\Databases\NGProd\NGProd_Core_Data_1.ndf',
move 'NextGen_Index_1' to 'D:\Databases\NGProd\NGProd_Index_1.ndf',
move 'NextGen_Log' to 'D:\Databases\NGProd\NGProd_Log.ldf',
recovery, -- assumes no tlog or diff backups need to be applied
stats = 10;
January 29, 2010 at 2:23 pm
Thanks
That is close. I ran into an error.
Right now, the new server does nto have any database by the name "NGPROD" created.
Do I have to first create a database by the name "NGPROD"
Or Am I using a wrong file as the backup. I was given a file that ends with the extension "trn"
I used the following command and got the error message which is listed further down.
restore database NGProd
from disk = N'D:\NGProd_backup_201001280600.trn' -- assumes the file is local
with file = 1,
move 'NextGen_System_Data' to 'D:\Databases\NGProd\NGProd_System_data.mdf',
move 'NextGen_Core_Data_1' to 'D:\Databases\NGProd\NGProd_Core_Data_1.ndf',
move 'NextGen_Index_1' to 'D:\Databases\NGProd\NGProd_Index_1.ndf',
move 'NextGen_Log' to 'D:\Databases\NGProd\NGProd_Log.ldf',
recovery, -- assumes no tlog or diff backups need to be applied
stats = 10;
Msg 3118, Level 16, State 1, Line 1
The database "NGProd" does not exist. RESTORE can only create a database when restoring either a full backup or a file backup of the primary file.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
January 29, 2010 at 2:27 pm
The .trn file is a transaction log backup file, you need to restore the most current FULL backup first. If you also need to restore differential and/or transaction log backups, you need to change the first restore to use norecovery instead of recovery, then you need a restore database for the differential backup (same as the full backup) and the restore log commands for each transaction log file you are restoring.
January 29, 2010 at 2:32 pm
Please see attached image.
This will give you some idea of whether we have a full backup or not.
Please reply. Your help is appreciated
January 29, 2010 at 2:36 pm
True, I agree.
Our DB Admins are using the ".trn" extension for a full backup.
Please see the attached image that I sent earlier. You will get an idea whether we are
doing a full backup or not.
I think it is a full backup although the file extension is "trn"
I may be wrong.
January 29, 2010 at 2:41 pm
Doesn't tell me a thing.
run the following and post the results:
restore headeronly N'D:\NGProd_backup_201001280600.trn'
restore filelistonly N'D:\NGProd_backup_201001280600.trn'
January 29, 2010 at 2:57 pm
I believe there was a syntax error
restore headeronly N'D:\NGProd_backup_201001280600.trn'
restore filelistonly N'D:\NGProd_backup_201001280600.trn'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D:\NGProd_backup_201001280600.trn'.
--The file name is spelleed correctly
January 29, 2010 at 3:02 pm
Check BOL (Books Online).
January 29, 2010 at 3:08 pm
Sorry for the confusion.
You were right!
That was a transaction log.
We did find the actual backup file it is 78GB large and also there was a second file
that was 12GB large.
I guess I will have to trust the bigger file and conisder it as the full backup file (recent ).
January 29, 2010 at 3:15 pm
mw112009 (1/29/2010)
Sorry for the confusion.You were right!
That was a transaction log.
We did find the actual backup file it is 78GB large and also there was a second file
that was 12GB large.
I guess I will have to trust the bigger file and conisder it as the full backup file (recent ).
Use the two commands I provided earlier (after correcting syntax based on BOL) to determine what each file is.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply