How to restore a database with Full Text Search
-
HI,
The SQL Server instance is 2008 R2. I have a database with a Full Recovery Model and Full Text Search. I need to make a copy of the database on the same server. I have used SSMS to make a backup of the database. I checked the box "Copy-only backup".
I want to create a different database on the same server with all the data including the full text search from the original database. I should have everything I need in the .bak file. I was not able to find any examples I could use to write the code for the restore. It is the text search files that are causing me confusion.
I have created an empty database. The original database is Data_02. The new database I created is Data. The datafiles live in E:\Data and the backup files live in E:\Backup.
I have a couple of questions.
- Should I create this empty database, Data, prior to beginning the restore? I read something somewhere that said I should not but that article did not go on to specify how to create the database during the restore. So it left me confused.
- If I create the new database, Data, before the restore should I create it with the Full Text Search enabled? Or does it not make a difference because the backup will override the initial settings?
- I would like to do the restore from the query window in SSMS rather than use the wizard. I have yet to find an article that I am comfortable trying. Here is what I have so far but need to modify to include the Full Text Search.
USE [master]
RESTORE DATABASE [Data]
FROM DISK = N'E:\Backup\Data_02 Aug 8 2022.bak' WITH FILE = 1,
MOVE N'Data_02' TO N'E:\DATA\Data.mdf',
MOVE N'Data_02_log' TO N'E:\DATA\Data_log.ldf',
NOUNLOAD,
REPLACE,
STATS = 5;
GOThe Big Picture. The database being copied is a production database. The copy of the production database will be modified. The copy of the production database will then be restored overtop of the original production database. Of course, this will ultimately occur over the End of the Year Holiday. I wanted to share the overall process because if you spot anything I should do differently, I would appreciate a heads up. I do not have access to a Development SQL server at this client.
Thanks,
pat
-
Make sure you have added the service account in the security policy to "perform volume maintenance" ! (= activate Instant File Initialisation ) ( needs a restart of the instance !! )
No need to create anything up front.
ref: "Restoring a Full-Text Index"
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
In Sql 2008 R2 I could not find Security Policies. I did find Securables. I checked the GRANT check box for Create Any Database. I had to log in as SA to make that change to the user I typically use. Was this correct?
Using the link you provided, I followed the 4th choice, Restore (Transact-SQL). There I found an example link, E. Copying a Database using Backup and Restore. I was not able to understand the example provided.
I do not understand how to restore the .ndf file in the code I wrote above. See below for the source of my confusion.
I went back to trying to use SSMS to do the restore. Below are the screen shots of what I think I should set. I did not press OK.
I created a new database in SSMS, TestRestore. Then for the restore I right clicked the database and selected, Tasks>Restore>Database.
- On the General tab, Source = Device and the Backup set I created on Monday. Restore to was the new database , TestRestore
- The files tab is the source of my confusion. As you can see from the image, the ndf file has the same Original File Name AND Restore As name. Why is that? Wouldn't these setting overwrite the existing ndf that is currently being used by the Source Database? Wouldn't any changes made in the ndf between the Backup and the Restore be lost if it is over writing the Source's ndf?
- The Options tab is shown below with the options I selected. Are my choices correct?
Any help you can offer would be greatly appreciated.
Thanks,
pat
- On the General tab, Source = Device and the Backup set I created on Monday. Restore to was the new database , TestRestore
-
Should the code below work? Is this the correct method for renaming the ndf?
USE [master]
Declare @newNDF as varchar(100) = 'E:\DATA\ftrow_FTC_titles{' + cast(NewID() as varchar(100)) +'}.ndf'
RESTORE DATABASE [Data]
FROM DISK = N'E:\Backup\Data_02 Aug 8 2022.bak' WITH FILE = 1,
MOVE N'Data_02' TO N'E:\DATA\Data.mdf',
MOVE N'Data_02_log' TO N'E:\DATA\Data_log.ldf',
Move N'ftrow_FTC_titles{4E13037C-2F3F-49A3-B194-228F86A4F958}.ndf' to @NewNdf,
NOUNLOAD,
REPLACE,
STATS = 5;
GO -
you need to extract the logical filenames from the backup file!
you need to hard code the full restore statement (or generate the statement in to a variable to be executed with exec or sp_executesql).
Restore does not take parameter variables.
use restore filelistonly and work from there on
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Johan,
I was afraid you were going to say that. I did explore that option as suggested by your first post. But I cannot make heads or tails out of it. So please allow me some time to search for examples that might guide me. I have 3 pressing project which I have to complete before I can devote a lot of time to this. It may be Monday before I reply.
Thank you for staying with this thread and my lack of experience in this area.
pat
-
Johan,
I appreciate your patience.
I used
RESTORE FILELISTONLY FROM DISK = 'E:\Backup\Data_02 Aug 8 2022.bak'
To get the physical file names.
In a separate query window I used
select newID()
To get a new GUID, "E2E86282-489D-4B56-A680-3DFDB90FFA51". Combining all this together I get:
USE [master]
RESTORE DATABASE [Data]
FROM DISK = N'E:\Backup\Data_02 Aug 8 2022.bak' WITH FILE = 1,
MOVE N'Data_02' TO N'E:\DATA\Data.mdf',
MOVE N'Data_02_log' TO N'E:\DATA\Data_log.ldf',
Move N'ftrow_FTC_titles{4E13037C-2F3F-49A3-B194-228F86A4F958}.ndf' to N'E:\DATA\ftrow_FTC_titles{E2E86282-489D-4B56-A680-3DFDB90FFA51}.ndf',
NOUNLOAD,
REPLACE,
STATS = 5;
GOWhat do you think?
-
You said you have done a Copy Only backup. This may not be what you actually need.
Unless you really know what you are doing and the restrictions related to a Copy Only backup, you should do a normal full backup.
The main difference is a normal full backup will restore your database to be consistent at the last completed transaction. It does this by including in the backup a portion of the transaction log to allow rollforward and rollback processing to complete. Going by the rest of your description this looks like the scenario you want.
The comment about 'instant file initialisation' relates to a Windows security setting. Google/ddg/bing these words and one of the results will tell you more about what it means and how to implement it.
The comment about using internal file names is also best resolved by doing some research. Think about how you refer to your friend's house. You call it Bob's place. If Bob moves, you still call the new house Bob's place. You very seldom refer to the street address. The SQL internal name is like Bob's place and the file name like the street address.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Ed, Thank you for your comments.
Johan, I completed the restore just now. Thank you for your help.
Below is a summary of the steps I took for future reference.
- Used to Restore Files Only to get Logical and Physical names.
RESTORE FILELISTONLY FROM DISK = '\\SqlServerName\E$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Data_02Aug_15_2022.bak'
- Obtained a new GUID for .ndf and substituted it into the restore statement.
Select newid()
- Made sure the new Database, "DATA" did not exist on the server.
- Executed the Restore from SSMS.
USE [master]
RESTORE DATABASE [Data]
FROM DISK = N'\\SqlServerName\E$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Data_02Aug_15_2022.bak' WITH FILE = 1,
MOVE N'Data_02' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Data.mdf',
MOVE N'Data_02_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Data_log.ldf',
Move N'ftrow_FTC_titles' to N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ftrow_FTC_titles{C5DFD204-0159-4EBD-945B-A31EB8672118}.ndf',
NOUNLOAD,
REPLACE,
STATS = 5;
GO
- Used to Restore Files Only to get Logical and Physical names.
-
Thank you for the feedback.
( I was out for a week of vacation. )
Just wondering why you add the uniqueidentifier to the physical filename ??
Move N'ftrow_FTC_titles' to N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ftrow_FTC_titles{C5DFD204-0159-4EBD-945B-A31EB8672118}.ndf',
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Johan,
I hope you had an enjoyable vacation.
When I do not know what I am doing, I look for examples and try to follow them. My reasoning was that since SQL Server used a "uniqueidentifier" when it created the original file name, I should do the same. Based on your question, I am going to conclude that using a "uniqueiedentifier" was not necessary. I will keep that in mind if I ever have to do this again. Thanks for following up.
-
Short but very enjoyable indeed 🙂
A bit of Fulltext history can be found at ref: "Full-text index architecture"
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply