February 27, 2008 at 11:13 am
I have been asked to fine a way to make this work. We have a production database that is rather large. Over a TB. We are going to create a file group called archive. We will put all archive tables and data in this file group. Every night, we have a SAN Copy of the database made for another host. In EMC terms this is called a BCV. This copy is only of the Primary file group and logs and will not contain the archive file group. I am researching the command syntax now of "create database... for attach" and do not find anywhere where I am able to "force" the database to come up with the missing file group. I've read where something like this is possible but have not found any details. I've seen more of this in the backup/restore arena.
I've gone as far as taking the Adventure works database, adding the file group, creating a table with some data, and then doing a detach, rename file, attach and the attach fails with a 5120 file not found.
Any suggestions or direction to research would be appreciated!
February 27, 2008 at 1:55 pm
you can indeed do partial restores etc. It's been a while since I did this - you might want to search what paul Randal has blogged about it. When i was working with partial restores the one important thing was that you cannot have user data on the primary partition, you must put all user data on secondary filegroups .
That said I'm not sure you can do what you're trying to do.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 27, 2008 at 3:21 pm
Hi Richard,
Have you considered archiving to a seperate archive database as possible workaround? Any reason why plan is to archive within same databae?
Thanks,
Phillip Cox
February 27, 2008 at 3:35 pm
Yes why can't you move the archive to another database. That should do the simple solution for you.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 27, 2008 at 4:26 pm
Thankyou for asking. First, the application software does not archive to a separate database. The application does archive to a separate set of tables which I would put on the "archive" file group. The archive file group would also be on lower cost Tier II storage. The goal here is not to add any additional Tier I storage as that cost is very high.
Thanks for the reply's.
February 28, 2008 at 8:44 am
In this case you can go for a replication add only those objects to be moved to the DR server as articles in the replication and leaving others so that the other database has only surrent data and not the archived one.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 28, 2008 at 8:50 am
A solution was found but not the one I was looking for. The application is able to point to a different server for the Archive data. This means we will create a new server and database on the new storage space. Since the data is archive data I do not have to worry about the data not being on the BCV. Your suggestions helped convince my boss of the solution we needed to pursue. Thanks everyone for the quick response.
February 28, 2008 at 8:51 am
If the app supports separate tables, create them, move them to the new database, drop the original tables.
Now create a view of the tables in the other database (cross database view) with the same names as the original tables. Archives now in a new database.
February 29, 2008 at 3:18 am
A different server often just means a different SQL instance. You could put a named instance on your original server, and use the named instance to hold the archive DB.
You certainly need to find a solution that avoids the need to try to use a database that has a ndf file missing. The solutions that allow you to have a missing file are designed for data salvage, not for production use.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply