February 24, 2009 at 12:35 pm
This may end up being more philosophical than practical - I know that I can write (aka copy something that already works from someone else in the best DBA tradition) a script to force the views to copy over (including DDL change triggers); I was caught unawares, so I'm looking for references as much as answers.
When I take a backup of a database that has stored procedures and views, using a 'BACKUP DATABASE ...' command, and restore the backup to a brand new, didn't exist two seconds ago database, all of the views and stored procedures come with the data (yes? Or is my amnesia setting in?).
I then set up a nightly job to take the backup of the production database, transport it to the offsite DR location, and restore the nightly backup database into a 'live' state (in single user mode, of course). When I check, I can verify that the day's transactions made it to the DR database intact. I believe changes in table definitions work, or I'd receive an error message when attempting to restore the data. BUT, a view was recently added to the production database. When I go to look at the DR site, it's not there. All the other Views are, but not the new kid.
Is this a 'flaw' in SS2K5? Is this by design? Is there a better work-around (e.g., some setting I've missed/misunderstood in BOL) than a custom script to capture VIEW changes? It's a relatively small database (<300 MB backup, uncompressed) housed in a single .mdf file on a single disk. Please advise.
February 24, 2009 at 1:04 pm
imo that view must have been created / altered after your backup started or ended.
Some of us create a full backup and make additional log backup(s) afterward to be restored onto a DR instance.
That would result into a restore of the full backup (norecovery) and a couple of log restores including the latest one using the with recovery option.
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
February 24, 2009 at 1:17 pm
Actually, it's a bit messier than I described. There's the production database, the DR database, and a 'man in the middle' - a reporting instance used to take some of the load off of production.
The Production database is backed up 10 pm daily. The backup is copied at 10:30 to the DR site and restored at 11 pm. User activity tends to be finished most days by 5:30, always by 7 pm. The view was inserted into the database at least two days ago. Yet it still does not appear on either of the other two instances.
The problem is not new. The Production database has 15 views maintained by a 3rd party. The DR database has 14 views, including one that the 3rd party manually inserted after determining that the backups didn't carry it over. The reporting instance has 13 views, because the 3rd party didn't manually install their 14th view.
February 24, 2009 at 1:51 pm
What you are stating is not possible - if the database is restored. If there are views that don't exist in the other instances, they must exist in a 'different' database than the one being restored every night.
Or, the process is not a backup/restore operation.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 24, 2009 at 2:16 pm
Well, let's see... I take a backup. I zip the file before copying it across from one server box to another server box. Then I unzip the file (using 7-zip utility in both cases). Then I execute the following code:
sqlcmd -S DR-Database -E -o \standbyfile'"
When I then run a restore command with recovery, the stored procedure is missing!?!
I'll have to do more research to provide the details on how the Reports server is restored.
-------
OK... it looks like the Reports server is being maintained with a 'snapshot' which would explain why only data is being ported over?
February 24, 2009 at 2:27 pm
steve smith (2/24/2009)
Well, let's see... I take a backup. I zip the file before copying it across from one server box to another server box. Then I unzip the file (using 7-zip utility in both cases). Then I execute the following code:sqlcmd -S DR-Database -E -o \standbyfile'"
When I then run a restore command with recovery, the stored procedure is missing!?!
I'll have to do more research to provide the details on how the Reports server is restored.
-------
OK... it looks like the Reports server is being maintained with a 'snapshot' which would explain why only data is being ported over?
All depends upon how the snapshot is being created and when it was created. If the snapshot was created prior to creating the new views they won't exist in the snapshot.
If the snapshot is a database snapshot - then the snapshot is a copy of the database from a point in time (when the database snapshot was created).
If the snapshot is SAN based - then again, it is a copy of the database at a point in time which will contain all objects in the database.
If by snapshot you mean an ETL process that extracts data from production and loads it into the reporting server, then no - the objects will not exist.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 24, 2009 at 3:05 pm
Jeff,
Well, that will explain why the reports server doesn't have the new views (doesn't need them, anyway). I'll have to wait and see whether tonight's backup brings over the newest view or not. There may have been a timing issue or I may have misunderstood when the user was adding the view. I know it was there today. I didn't really look for it earlier, so I can't speak to when it arrived. We'll see.
February 24, 2009 at 11:45 pm
If there is no ETL in between your prod to DR instance, check if your backup actually completed successfully.
Also check your backup set contains only one backup instance, otherwise you may be restoring an old version of the db.
Is you backup file growing heavy ?
Less plausible, but still... does DBCC opentran show entries ?
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
February 25, 2009 at 8:22 am
All,
Thank you for the responses and suggestions. When I checked the restore this morning, the database was properly synchronized and all the views appeared as expected. (The directory where the backup is initially stored is wiped before the full backup is taken - all database and log backup files are removed as part of the processing to create the latest backup.)
The suggested additional diagnostics are tools I will have to add to my tool bag; I have no doubt that a similar issue will appear in the future. After all, Murphy was an optimist!
To summarize what I've learned here: Snapshot processes will not pick up new views. Full database backups, at least for SS 2005, will backup the full database instance (which means master, etc. must be backed up separately) including tables, indexes, constraints, views and stored procedures. (Triggers? I believe so).
February 25, 2009 at 8:53 am
steve smith (2/25/2009)
Snapshot processes will not pick up new views.
Depends what you mean by snapshot.
Full database backups, at least for SS 2005, will backup the full database instance (which means master, etc. must be backed up separately) including tables, indexes, constraints, views and stored procedures. (Triggers? I believe so).
Database backups backup everything inside a database.
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
February 25, 2009 at 8:57 am
steve smith (2/25/2009)
All,Thank you for the responses and suggestions. When I checked the restore this morning, the database was properly synchronized and all the views appeared as expected. (The directory where the backup is initially stored is wiped before the full backup is taken - all database and log backup files are removed as part of the processing to create the latest backup.)
The suggested additional diagnostics are tools I will have to add to my tool bag; I have no doubt that a similar issue will appear in the future. After all, Murphy was an optimist!
To summarize what I've learned here: Snapshot processes will not pick up new views. Full database backups, at least for SS 2005, will backup the full database instance (which means master, etc. must be backed up separately) including tables, indexes, constraints, views and stored procedures. (Triggers? I believe so).
I'm sorry, but would you please NOT use size="1" in your posts? My eyes are getting old and that small font is difficult to read.
Thanks.
February 25, 2009 at 9:20 am
I'm sorry, but would you please NOT use size="1" in your posts? My eyes are getting old and that small font is difficult to read.
Thanks.
+1
February 25, 2009 at 9:28 am
Sorry all. This is the first time that I've experimented with using font sizing. Does '2' work better? [pls reply only if you need larger]
Gail -
yes, I agree that a database backup should back up everything inside the database. But when I checked from the backup, from last Friday, after the view had been added, it did not appear in the restore as expected, which is what prompted the discussion. I can't explain why / what caused the delay, only that it occurred. But the problem is gone, and since it's production I really can't "play" with creating views, so I would need to figure out whether I can create a replication in a test environment.
The true cautionary tale arising out of this thread is that database snapshots, when scheduled regularly, may or may not capture all changes in a database, depending on what besides data changes. And perhaps leaving the statement that vague does the greatest public service, because anyone reading it will have to make sure they understand what they are doing?
February 25, 2009 at 9:41 am
steve smith (2/25/2009)
Sorry all. This is the first time that I've experimented with using font sizing. Does '2' work better? [pls reply only if you need larger]
How about just leaving the size and font the forum default?
yes, I agree that a database backup should back up everything inside the database.
Not should, will. If the view isn't in the copy then either is wasn't in the database at the time of backup or it was dropped after the restore. Or the process creating the copy isn't a SQL backup/restore.
A backup just copies all pages from a database file (including those that store the system objects) puts them into the backup file. The restore process does the reverse. There's no place that a single object can be missed, unless the restore is giving errors.
The true cautionary tale arising out of this thread is that database snapshots, when scheduled regularly, may or may not capture all changes in a database, depending on what besides data changes.
A database snapshot is a point-in-time copy of a database. Any changes made to the DB after the snapshot is created will not be reflected in it. It also must be on the same instance as the source DB and cannot be moved.
Or are you talking about some other 'snapshot'
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
February 25, 2009 at 9:59 am
GilaMonster (2/25/2009)
The true cautionary tale arising out of this thread is that database snapshots, when scheduled regularly, may or may not capture all changes in a database, depending on what besides data changes.
A database snapshot is a point-in-time copy of a database. Any changes made to the DB after the snapshot is created will not be reflected in it. It also must be on the same instance as the source DB and cannot be moved.
Or are you talking about some other 'snapshot'
My bad. It's a replication from the 'production' server to a 'reports' server, published twice a day. Which I believe means that the term 'snapshot replication' could be applied? I'm terrible when it comes to using correct technical vocabulary. I also think that this means that data is being replicated but not necessarily all the content captured in a backup. I would need to reread all the stuff I studied last about 8 months ago.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply