T-SQL

Re: Restore one filegroup (out of 2) at some point in the past

cardasim — Fri, 28 Aug 2009 07:17:32 GMT

Multumesc, Igor. Autorul articolului e cel care mi-a raspuns la intrebare aici: http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/21ebad42-d765-43de-9bd6-4d45a218b6c6

Re: Restore one filegroup (out of 2) at some point in the past

igor — Tue, 18 Aug 2009 08:48:41 GMT

Salut,

Un articol foarte informativ despre acest subiect il gasesti acici

Re: Restore one filegroup (out of 2) at some point in the past

cardasim — Fri, 14 Aug 2009 07:51:57 GMT

Am postat pe MS Connect. ID 482717, "Restore filegroup to a certain point back in time".

Am primit intre timp si doua raspunsuri de la MS, fac copy&paste aici:

1. De la echipa Sql Server
"That functionality is not possible in SQL Server.

In order to have a database that is consistent, all portions of it must be at the same transactional point in time.

The internal consistency of the database is maintained using Log Sequence Numbers, and there is no way to have multiple LSNs active in a single database.

It seems that both scenarios could be readily facilitated by creating a simple script to truncate the tables which contain customer specific data, and put the database into a state which is ready to deploy at another customer. Or better yet, develop a set of pre-configured databases which have never had any customer data in them.

In Scenario B, there is a real risk in taking a backup from one customer and putting the data on another customer’s machine. Even when rows are deleted, the data is not zeroed out on the pages, so there is a chance that the second customer could obtain information from the first.

SQL Storage Engine PM team : SQL Server Storage Engine Team blog"

2. De la support prin programul METRO

"Unfortunately in the 2 scenarios you described it’s not applicable by restoring filegroup backups. I’d like to explain a little as below:

Scenario A

SQL Server will not let you into a database where different data is from different points in time.

You must use file and filegroup backup and restore operations in conjunction with transaction log backups. After you restore the files, you must restore the transaction log backups that were created since the file backups were created to bring the database to a consistent state.

File/filegroup backups can be used to restore the database to an earlier point in time. To do this, you must restore a complete set of file backups, and then restore transaction log backups in sequence to reach a target point that is after the end of the most recent restored file backup.

In fact the main reason for performing selective restores of files or filegroups is to make it possible to spot-recover damage to a database that is too big to restore wholesale. For more details about restoring filegroup backups please reference http://msdn.microsoft.com/en-us/library/ms190710.aspx.

Scenario B

File and filegroup backups must be restored to the matching database.

File and filegroup backups can only be restored to the database to which they belong. You cannot create a new, blank database with the same structure and filenames and then attempt to restore the single file or filegroup backup; you must restore it into the existing database or perform a full database restore in another location.

Suggested solution

For scenario A you can restore the PRIMARY filegroup into a new temp database-to the desired earlier point in time, and then copy the relevant data from this temp database into the production database.

Or you could simply put the Configuration data in a separate database, then replace the objects in the main database with synonyms, or replicate the configuration data into the main database."

Re: Restore one filegroup (out of 2) at some point in the past

crestinul — Fri, 07 Aug 2009 11:41:17 GMT

Parere mea e ca te complici.Poti face din aplicatia urmarirea modifcarii obiecteleor.Linq si Entity Framework de exemplu au capabilitatea de object tracking daca folosesti aceste tehnologii ca dataacces layer.

Re: Restore one filegroup (out of 2) at some point in the past

Diana — Fri, 07 Aug 2009 09:33:04 GMT

Da-mi de veste, vreau sa votez "pentru"...:)

Re: Restore one filegroup (out of 2) at some point in the past

cardasim — Fri, 07 Aug 2009 09:26:31 GMT

@Crestinul Tocmai asta e: eu nu am nevoie de recovery ci de o modalitate de a "reutiliza" un filegroup plin cu date. Cine stie poate in versiunile viitoare ... raportez pe Microsoft Connect si vedem ce se intampla. Pentru moment case closed

Re: Restore one filegroup (out of 2) at some point in the past

cardasim — Fri, 07 Aug 2009 09:21:02 GMT

@Diana: bine zis, am acelasi sentiment periodic...

Re: Restore one filegroup (out of 2) at some point in the past

crestinul — Fri, 07 Aug 2009 06:02:49 GMT

Cred ca solutia cea mai simpla e sa faci restore point in time daca ai recovery model pe full.

Restaurezi intreaga baza de date dar macar iti realizei obiectivul

Re: Restore one filegroup (out of 2) at some point in the past

Diana — Wed, 05 Aug 2009 12:41:08 GMT

Multumesc, Florin.
Cat traim invatam si tot nu ajunge...:)

Re: Restore one filegroup (out of 2) at some point in the past

cardasim — Wed, 05 Aug 2009 11:06:19 GMT

Nu, din pacate snapshot-ul e doar la nivelul intregii baze de date, nu la nivel de filegroup.
De asta s-au mai plans si altii, de exemplu aici: http://www.eggheadcafe.com/forumarchives/SQLServerserver/Feb2006/post26066897.asp

Re: Restore one filegroup (out of 2) at some point in the past

sageata2002 — Tue, 04 Aug 2009 13:23:00 GMT

inteleg ca database snapshots nu ajuta in cazul tau, nu?

toate cele bune,
calin

Re: Restore one filegroup (out of 2) at some point in the past

cardasim — Mon, 03 Aug 2009 19:11:15 GMT

Diana, multumesc pentru sugestie, insa din pacate nu am reusit.

Intre timp am mai primit niste informatii, ce vreau eu nu se poate.
Detalii aici:
http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/21ebad42-d765-43de-9bd6-4d45a218b6c6

Concluzia: pentru ce vreau eu am doua solutii
1. cod care sa faca export - import
2. baze de date separate pentru CONFIG si ROUTINE (operational)
Sunt nevoit sa aleg solutia 1. Dar ar fi fost cool sa pot reolva problema cu filegroups. Poate in versiunile urmatoare

Re: Restore one filegroup (out of 2) at some point in the past

Diana — Mon, 03 Aug 2009 07:39:24 GMT

Incearca sa lucrezi cu filegroup-ul "Config" pus pe "read-only". Cand ai nevoie sa schimbi ceva in "Config", il pui din nou pe "readwrite". Vezi
http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=268
si
http://msdn.microsoft.com/en-us/library/ms188631(SQL.90).aspx

Re: Restore one filegroup (out of 2) at some point in the past

cardasim — Sun, 02 Aug 2009 18:39:04 GMT

Da, dar din pacate piecemeal nu cred ca ajuta la ce vreau eu, si anume restore la un filegroup la un moment ANTERIOR in timp fata de celelalte filegroup-uri.

Re: Restore one filegroup (out of 2) at some point in the past

crestinul — Sun, 02 Aug 2009 08:08:40 GMT

sql 2005 are posibilitatea de peace meal restore care face asa ceva restaureaza numai anumite fisiere.