Recovery :: How To Backup Raw Data Only With Just Views And Tables

Sep 23, 2015

My client has on a physical server (A), the following:

1. MS SQL Server 2008R2
2. MS SQL Server (Reporting Server)

The data base is backed up fully daily to another physical server (B).

What the client would like to do is:

1. Backup just the Raw SQL Data with Tables and Views to Server B every 10 mins

Point to be noted is, there isn't any dedicated storage (NAS or SAN) just local disk storage on the server.

The reason for this is they want to run reports against this data every 10-15 mins...

How and what do I need to do to facilitate this?

View 15 Replies


ADVERTISEMENT

Comparing Data In Tables/views.

May 13, 2008

I am looking for an efficient mechanism to compare data between 2 tables/views.

Rationale:
I use a proprietary tool to data transfer between 2 databases. The tool itself uses Microsoft SSIS (integration service) to transfer data. I want to make sure that the data is transfered properly. Both the source and target database are not live database. The source and target database are in seperate servers.

View 2 Replies View Related

How To Migrate Data From Sybase Views Into Tables

Oct 8, 2015

Is it possible to get SQL Server Migration Assistant to read data from a Sybase view and store it in a SQL table.  My problem is that I have a legacy sybase application that is being decomissioned and I have been asked to migrate the data into SQL Server.  The problem I have is that:

The account I have been provided to connect to the Sybase database does not have select privileges on all the tablesI don't have the sa password for the Sybase database to alter the security. The vendor support contract has expired and it does not appear I have any way to get the sa password

The account I've been given has access to query data in several views even though it does not have access to the underlying tables that the view pulls data from.  Since this is a legacy application whose data is now static, I wonder if it's possible to read the data from the Sybase views and dump it into SQL tables.

View 2 Replies View Related

Tables And Underlying Views W/ Access Data Projects

Dec 3, 2001

I don't know if there is a fundamental problem with what I am trying to do, or am I just having problems setting it up correctly:

I have a SQL server multi-user database. I want my users to connect to this databsase via Access 2000 Data Project. No problem there. The database consists of one main table and several views (based on the office branch that the user works from). For example there is a Chicago view, an Atlanta view, etc. that all extract different records from the same underlying table. I need my users to have FULL ACCESS (select, update, delete) to their respective VIEWS, but they cannot have access to the underlying table. I've tried several configurations and I'm beginning to think that this may not be possible... is that the case?

If it is not possible to grant access to views but not the underlying table, then what are my other options? The objective is to have a multi-user table that each user "owns a piece of" without being able to see the tables or records belonging to their peers. Do I need to setup a table for every office, and somehow link those tables into one main table? How would I avoid duplicate records being entered into the separate tables? Any help would be GREATLY appreciated, as this problem has had me stumped for weeks.

View 2 Replies View Related

Help With Partitioned Views Or Updating Data From Multiple Tables

Mar 16, 2008

Hi All,

My database's design is set out here. In summary, I'm trying to model a Stock Exchange for a Technical Analysis application written using Visual C++. In order to create the hierachy I'm using a Nested Set Model. I'm now trying to write code to add and delete equities (or, more generically, nodes) to the database using a form presented to the user in my application. I have example SQL code to create the necessary add and delete procedures that calculate the changes to the values in the lft and rgt columns, but these examples focus around a single table, where as my design aggregates rows from multiple tables using UNION ALL:




Code Snippet
CREATE VIEW vw_NSM_DBHierarchy -- Nested Set Model Database Hierarchy
AS
SELECT clmStockExchange, clmLeft, clmRight FROM tblStockExchange_
UNION ALL
SELECT clmMarkets, clmLeft, clmRight FROM tblMarkets_
UNION ALL
SELECT clmSectors, clmLeft, clmRight FROM tblSectors_
UNION ALL
SELECT clmEPIC, clmLeft, clmRight FROM tblEquities_




Essentially, I'm trying to create an updateable view but I receive the error "UNION ALL View is not updatable because a partitioning column was not found". I suspect that my design in wrong or lacks and this problem is highlighting the design flaws so any suggestions would be greatly appreciated.

View 9 Replies View Related

SQL Server 2014 :: Replicating Tables Referenced By Indexed Views With Data Binding

Apr 1, 2015

when i do a snapshot i have it set up to truncate before inserting. As a result I'm getting an error saying that it cant truncate a table reference in an indexed view. What settings should i use to allow for a snapshot in this instance? Should i manually drop the databinding then snap then recreate the databinding? there has to be a better way

View 1 Replies View Related

Recovery :: Differential Backup Much Larger Than Database / Full Backup

Nov 16, 2015

I have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...

View 13 Replies View Related

Backup And Recovery

Dec 15, 2000

1. I want some help regarding sql server 7.0.I had taken database backup (complete).And tried to restore in another machine.
It restored successfully. But it does not create any logins/users. But it has some objects owned by user A
If i create login as A and assigning default database, it is giving error and saying the user/role is already existing. Before restoring
also i created login and after restoring I tried to assign the default database, but failed.
I chaged the objects owner and tried again, still it is saying the user/role is existing.
Can you suggest the solution.

2. I had backup of a database of SQL server 6.5. If i tried to restore on SQL Server 6.5/7.0, it is giving error, and saying it is not valid file. Any solution

With regards
K.N.Rao

View 1 Replies View Related

DTS Backup And Recovery

Jul 18, 2003

Is it possible we can take the DTS package backup as part of SQL Server Database backup?.

Is it possible to store the script only in the server folder.?
Thanks,
Ravi

View 3 Replies View Related

Backup And Recovery

Apr 22, 2004

Hi Guys,
Well. I'm new to SQL server and appreciate your help on a problem....
Is the database mirroring possible in SQL server 2000. as in Oracle.

ie. I've the data file and a old log file (dated 3-04-2004), and ofource the backup file of the same date.

Now if my hard disk is creashed yesterday, will I be able to restore the database with the help of the data file which is updated till yesterday.

I can fo this in oracle. I create another database and then swap it's control files and data file that of the old one( the one which I need to restore). then i change the password file and boom i get everything upto date.

Can i do some thing like this in SQL Server?


thanking in anticipation

santosh kamble

View 1 Replies View Related

Backup And Recovery

Apr 2, 2008

How can we check whether the backup is in consistent state or not.

View 14 Replies View Related

Backup And Recovery

May 17, 2008

Hi,
I am new to sql server2005.
How to take a backup,restore&recovery operation in
sql server 2005.
Question No 1:
If i drop my "customer" table how can i get it back?
In oracle we will get back the table with the help of
incomplete recovery.

Question No 2:
How to connect as a user?
How to give the grant privilege to the particular user?

View 2 Replies View Related

Recovery And Backup

Jul 23, 2005

Could someone please give me an idea of the differences between SQLServer and Oracle, backup and recovery options.Thanks

View 1 Replies View Related

Backup And Recovery

Oct 18, 2007

i am upgrading 2000 to 2005 databases
i upgrade using backup and recovery method to do this.

If 2000 database contains many databases i have to upgrade each database at one time using the bakup file
Is there a way to upgrade all my 2000 databases in one shot using backup and recovery.

please let me know
thanks

View 15 Replies View Related

Backup And Recovery...

Nov 14, 2007

Hi all

Are there any steps, we have to follow while restoring Database.

Ex :I have Full backup (week end backup),every day I have Diff backup and every hour I have Trnbackup
If system has crahed how to restore Databse(I don't have any chanse to do any thing with system)








View 14 Replies View Related

Backup And Recovery Problem

Apr 19, 2004

i've got a project that using MSDE and JAVA
but how can i backup and recovery the database by using Java code??
or any alternative method?

View 1 Replies View Related

Best Tool For Backup/Recovery

Apr 29, 2004

Hi,
I know SQLLiteSpeed is one best tool for MSSQL server Backup Recovery.
And also I wanted know that the top 5 best tools for MSSQL server Backup/Recovery.

View 9 Replies View Related

Doubt In Backup And Recovery

Apr 1, 2008

every sunday 12 am fullback up.
for every 6 hours differential backup.
every 15 min t log back up.

If my server crashes at wednesday 3.30 pm

my restore order is fullbackup on sunday, last differential backup (wednesday 12pm) and the t logs from last full backup or t-log backups from latest differebntail backup

I have doubt after restoring the latest differentail backup we have to restore all t log backups from fullbackuop or all tlog backup from latest diff backup.
Which order we have to follow

View 7 Replies View Related

Backup && Recovery History

Jul 23, 2005

Hi gurus,May be my question is funny to all of you guys but I don't know why wehave to keep the backup and recovery history of databases in msdb. Itry to read BOL & other documents but no clue.Thanks a bunch

View 3 Replies View Related

Recovery :: How To Get Continuously Backup

Aug 25, 2015

I have small db and load is very less. I am set full backup at  9:00 pm once a day,and set  transaction log backup to every 15 min.then i am taking the transaction log backup in 9:15,9:30,9:45....... My question is  I lost my data between 9:15 to 9:30 . in those time i will do some transactions.Then how to recover my data even with out lasing single  transactions.

View 4 Replies View Related

Database Recovery If No Backup Available

Dec 19, 2006

Hi,

 

 If we delete a Database accidentally (no backup exists), Is it possible to recover. How?

 

Thanks

ven--

View 4 Replies View Related

Log Backup In Point-in-time Recovery

Aug 10, 2000

Hi Everybody:

We plan to do point-in-time recovery for certain databases. We plan to do Complete Database Backup every night and transaction log backup every two hours from 8:00 AM to 5:00 PM. I have following questions regarding the log backup.

1. There are two type of backup 'Append to media' or 'Overwrite'. If I choose 'Append' for log backup, is that mean I only need to restore database against last log backup file because all previous log backups have been accumulated there?

2. Can I automatically truncate log after the backup is done? How I can do it?

Thank you very much.

Joan

View 1 Replies View Related

Tape Backup Recovery Time

Jul 28, 2004

How much time does a 20 g tape backup take to recovery
I am on 6.5 sql and have compacq 15/30 gb DLT

How best to configure a standby sqk 2000 server

salim
__________________

View 8 Replies View Related

Sharepoint DB Recovery In Log Backup Files

Oct 11, 2011

I have an old sharepoint db (SQL Server 2000 Standard edition). What I need to try to recover is the data in log backup files from the docs table. I need to see if there is a way to get any data in the log backups from columns dirname, leafname and content. The column content is an image type which old documents (word, excel...). I don't have a full log chain since the last full restore so any data I can get out of the logs is better than nothing. I really just need to get the image files stored in the column content and place them in a shared directory for the users and let them see what they can use.

View 7 Replies View Related

LOG Backup And Remote Recovery - Msg 4330

Aug 22, 2007

Hi,

We have remote offices running SQL 2000, which are on a small bandwidth pipeline, so we implemented an incremental backup schema where they back up their LOGs overnight and send it to our main office. After an initial full backup, they just keep sending **incremental** LOG backups.

In the main office we restore the full backup of each remote office and from then on do a restore of each night's LOG file (with standby option), so that we keep in sync with them. The issue we have now is that we cannot get regular full backups from the remote (and sometimes independent) offices on a regular basis - maybe only once a *quarter*.

If we use differential backups, the size of each one grows each day, until we get a full backup. Incremental backups thru LOGs seemed to be the only way to go. *HOWEVER*, we now have multiple LOG files sitting on our server in the main office and since the databases that we restored to is in warm-standby and read-only mode, we cannot make a backup of them in-house, without breaking the LOG restore chain.

That also means that we would not be able to easily re-create the environment locally, should something go wrong - we would have to apply all of the LOG restores for (potentially) the whole quarter!

I even tried to create two restored databases - one where I keep it in standby state and ready to receive more incremental LOG backups and another where I would take it out of standby mode (and into recovery mode). At that point I am then able to make a full backup locally of this second database. *BUT*, when I then try to restore the next day's LOG backup to this second database, it fails with an error
- Msg 4330 - Level 16, State 4, Line 1
- The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.

How do I do incremental backups and restores, while maintaining the ability to make a new full backup of the restored database, before the restore sequence is done?

Help !

View 1 Replies View Related

Simple Recovery And Full Backup

Jun 10, 2006

Hi MVPS/MS Experts:

Pardon me and my ignorance for asking this question. I just want to understand the backup architecture more clearly. According to BOL (both in SQL 2k and SQL 2k5) in simple recovery mode trasaction log backup is not possible since the log is truncated on checkpoint which is true. Also we know that FULL backup backups both the db and transaction log as well.

My question is what happens when a database is in simple recovery mode and a full backup is done. since the tran log cannot be backed up does only the db backup is done when a full backup is done?. What exactly happens behind the scenarios?. Is it that only the active log gets backed up when a full backup is done in simple recovery mode?. I am trying to understand how a full backup in simple recovery mode behaves without contradicting the full backup architecture and that the veracity of the statement (both db and tran log backup in full backup mode) holds true for a simple recovery scenario.

MVPs/ MS Experts if you could Please explain it in detail, I would really appreciate it.

Thanks

Ankith

View 6 Replies View Related

Backup/recovery Larger Databases...

Apr 10, 2008

Greetings all and thanks for reading this post.

Here is my situation... I have 2 fairly large databases. Full backups are 83gb & 63gb. I am in the process of moving these database to a new data center. I've taken full backups of these databases and shipped them to the new center. I have been taking transaction log backups (larger db every 24 hrs smaller db every 15 min ... from log shipping).

I want to restore these databases in the new data center. I've gone ahead and restored the dbs in the new location.

Question final cutover.. can I just apply the transaction logs to the databases on final cut-over or do I have to restore the database backup first then apply the transaction logs?

Is there an other way to do this that I'm missing?

Thanks.
Kurt

View 3 Replies View Related

Recovery :: Backup And Restore At Same Time

Nov 23, 2015

SQL Server 2008 R2

I don't see a "general discussion" thread and this is the closest i think.

I just have a general question: if my backup window is from 8am to 10am, and i do a restore within that backup window, what will happen? assuming we're talking only of a single database, ACME.

I'm comparing because with Oracle RMAN, it pukes when i do a restore while the backup is going on. it would complain of unable to find some archive logs.

View 2 Replies View Related

Backup Recovery Philosophy... Pull Up A Chair

Oct 20, 2007

(Be forewarned... this is long, but I think you'll find the information interesting)

When it comes to SQL Server backup/restore/recovery, my approach is based on a two-part philosophy. I'd like to share this with you and get you opinion and feedback...

First, I approach recovery from the instance level, not the individual databases. While a SQL Server instance is made up of system- and user-databases, I make no distinctions between them in terms of recovery - the instance is the whole "database". I'll later explain why.

Second, I approach it so that it will ultimately allow me to do a "displace" recovery... not just "in-place" restore. In other words, be able to restore the database on the current host ("in-place"), such in a case of a media or other failures where the host is intact - which is usually the common, easy case. But above that, be able to recover the database onto another host, such in a case of a catastrophic failure of the original host.

At any rate, the two part approach equates to what I consider provisions for doing "displace SQL Server instance recovery" - the ability to restore the whole SQL Server Instance, including the original system-databases, onto another host.

I come from an Oracle background... in the world where there's only one "database instance"... no such thing as "individual" databases. And really, the term "database" in Microsoft equates to what Oracle calls a "tablespace". Oracle's "system tablespace" equates to Microsoft's "system databases"... and so forth. It's a common case of one vendor calling it, "TOE-MAY-TOE", and the other calling it, "TOE-MAH-TOE". And in Oracle... I'm not the habit of making distinction between these tablespaces - system or non-system - when it comes to backing up... not many are (I would think). Our backup appoach views backing up the WHOLE database instance... therefore if needed... we can restore the WHOLE database instance. I can't find any other logical way to approach database backup.

But as I've found... in the world of SQL Server, there's seems to be a disconnect between how user-databases should be backed up (and restored) from the the way system-databases should be backed up (and restored). Most people make distinction between these database when it comes to backup and recovery.

Let me it explain it this way...

In my shop, my backup strategy provides the capability to restore both system- and user-databases. Some people might be surprised, but all this can actually be done using only the built-in SQL Server backup and restore functions. What I've also found is SQL DBAs find it almost "un-natural" to restore system-databases. I've heard and read some say is this... "just restore the user-databases on the new server and "re-create" the objects (that were in the previous system database) into the new system databases on the new host". Some also suggests using scripts to make "re-creating" these objects easier.

I think that people approach it this way because restoring system database onto another server (a different host name, a different file directory structure, etc) is not as simple as restoring user-databases.

But it is doable... and doable using only the built-in SQL Server functions.
My previous post on this matter can be found here (http://sqlforums.windowsitpro....22923&highlight_key=y)

I long time ago, I found a way to do this. And since then, my backup strategy has remained fairly the same... backup the whole sql server instance... and have the ability to restore the whole instance (if/when needed). And in the end, we not only have a great backup strategy, but a strategy for disaster recovery.

I agree that the approach to [simply] restore user-database, and then "re-creating" system-database objects is viable, but it's not "absolute". And here's why...

You'll always have to make provisions around when system-database objects are created/changed... for the life of the instance... such as logins, for example.
I've seen some application create extended procedures (in the master database) and create jobs and plans (in msdb).
You'll have to consistently ensure you update your "re-create scripts" to ensure you capture these objects and changes... through the life of the instance.
You'll have to be that much more intimate with the applicatons in order to understand all the components it may be adding outside it's user-database.

There a huge potential risk that you'll miss something.

Bottom line... restoring the whole SQL Server instance avoids any of the above caveats, guaranteeing a "physical restore" [of the original instance] versus one where parts of it had to be "logically" recreated and/or potentially be missing.

I admit, I also use scripts to complete a full SQL Server instance recovery. For me, it is a set of batch and sql scripts, but I only use it to expedite the process. And more importantly... the scripts are created once for an instance and doesn't have to be updated...
regardless of what occurs within the instance...
regardless if new logins or extended procedures are created/updated (in the master)...
regardless if new/updatess to jobs/maintenance plans/etc occur (in msdb)...
and I don't even really know what goes on in model... but again... it doesn't matter...
all of it gets restored.

The process... scripts or no scripts... is the same for all SQL Server instance I support... regardless of the idiosyncrasies of the application running on them. I have to make NO special provisions for any of them.

It's a solution where at the end of the recovery... I can tell the customer that whatever they originally lost has been restored (from the last backup)... without any doubt. Again it's a "physical restore", not one where parts of it had to be "logically" recreated.

Lastly... If you understand my philosophy... then you could probably understand why I don't consider Log Shipping and Replication to be a thorough disaster recovery solution. The reason being is that these solutions only provision for the recovery of user-databases, right? You cannot use log shipping or replication for system databases, right? And as with the other... you'll have to provision for re-creating system-database objects. Again, I admit log shipping and replication are viable solution, but again, with the idea of re-creating system database objects... it's not absolute... not for system recovery.

I'm looking for good arguments and feedbacks to really put this approach to the test. Thanks.

View 6 Replies View Related

Backup/Recovery Philosophy... Pull Up A Chair

Oct 19, 2007

(Be forewarned... this is long, but I think you'll find the information interesting)

When it comes to SQL Server backup/restore/recovery, my approach is based on a two-part philosophy. I'd like to share this with you and get you opinion and feedback...

First, I approach recovery from the instance level, not the individual databases. While a SQL Server instance is made up of system- and user-databases, I make no distinctions between them in terms of recovery - the instance is the whole "database". I'll later explain why.

Second, I approach it so that it will ultimately allow me to do a "displace" recovery... not just "in-place" restore. In other words, be able to restore the database on the current host ("in-place"), such in a case of a media or other failures where the host is intact - which is usually the common, easy case. But above that, be able to recover the database onto another host, such in a case of a catastrophic failure of the original host.

At any rate, the two part approach equates to what I consider provisions for doing "displace SQL Server instance recovery" - the ability to restore the whole SQL Server Instance, including the original system-databases, onto another host.

I come from an Oracle background... in the world where there's only one "database instance"... no such thing as "individual" databases. And really, the term "database" in Microsoft equates to what Oracle calls a "tablespace". Oracle's "system tablespace" equates to Microsoft's "system databases"... and so forth. It's a common case of one vendor calling it, "TOE-MAY-TOE", and the other calling it, "TOE-MAH-TOE". And in Oracle... I'm not the habit of making distinction between these tablespaces - system or non-system - when it comes to backing up... not many are (I would think). Our backup appoach views backing up the WHOLE database instance... therefore if needed... we can restore the WHOLE database instance. I can't find any other logical way to approach database backup.

But as I've found... in the world of SQL Server, there's seems to be a disconnect between how user-databases should be backed up (and restored) from the the way system-databases should be backed up (and restored). Most people make distinction between these database when it comes to backup and recovery.

Let me it explain it this way...

In my shop, my backup strategy provides the capability to restore both system- and user-databases. Some people might be surprised, but all this can actually be done using only the built-in SQL Server backup and restore functions. What I've also found is SQL DBAs find it almost "un-natural" to restore system-databases. I've heard and read some say is this... "just restore the user-databases on the new server and "re-create" the objects (that were in the previous system database) into the new system databases on the new host". Some also suggests using scripts to make "re-creating" these objects easier.

I think that people approach it this way because restoring system database onto another server (a different host name, a different file directory structure, etc) is not as simple as restoring user-databases.

But it is doable... and doable using only the built-in SQL Server functions.
My previous post on this matter can be found here (http://sqlforums.windowsitpro....22923&highlight_key=y)

I long time ago, I found a way to do this. And since then, my backup strategy has remained fairly the same... backup the whole sql server instance... and have the ability to restore the whole instance (if/when needed). And in the end, we not only have a great backup strategy, but a strategy for disaster recovery.

I agree that the approach to [simply] restore user-database, and then "re-creating" system-database objects is viable, but it's not "absolute". And here's why...

You'll always have to make provisions around when system-database objects are created/changed... for the life of the instance... such as logins, for example.
I've seen some application create extended procedures (in the master database) and create jobs and plans (in msdb).
You'll have to consistently ensure you update your "re-create scripts" to ensure you capture these objects and changes... through the life of the instance.
You'll have to be that much more intimate with the applicatons in order to understand all the components it may be adding outside it's user-database.

There a huge potential risk that you'll miss something.

Bottom line... restoring the whole SQL Server instance avoids any of the above caveats, guaranteeing a "physical restore" [of the original instance] versus one where parts of it had to be "logically" recreated and/or potentially be missing.

I admit, I also use scripts to complete a full SQL Server instance recovery. For me, it is a set of batch and sql scripts, but I only use it to expedite the process. And more importantly... the scripts are created once for an instance and doesn't have to be updated...
regardless of what occurs within the instance...
regardless if new logins or extended procedures are created/updated (in the master)...
regardless if new/updatess to jobs/maintenance plans/etc occur (in msdb)...
and I don't even really know what goes on in model... but again... it doesn't matter...
all of it gets restored.

The process... scripts or no scripts... is the same for all SQL Server instance I support... regardless of the idiosyncrasies of the application running on them. I have to make NO special provisions for any of them.

It's a solution where at the end of the recovery... I can tell the customer that whatever they originally lost has been restored (from the last backup)... without any doubt. Again it's a "physical restore", not one where parts of it had to be "logically" recreated.

Lastly... If you understand my philosophy... then you could probably understand why I don't consider Log Shipping and Replication to be a thorough disaster recovery solution. The reason being is that these solutions only provision for the recovery of user-databases, right? You cannot use log shipping or replication for system databases, right? And as with the other... you'll have to provision for re-creating system-database objects. Again, I admit log shipping and replication are viable solution, but again, with the idea of re-creating system database objects... it's not absolute... not for system recovery.

Tell what you think.

View 20 Replies View Related

The Statement BACKUP LOG Is Not Allowed While The Recovery Model

May 7, 2008



Hi All,

SQL newbie here. I just looked at my Job Activity monitor and found that my Transaction log backups are failing. I looked at the error and it read as follows:

Executing the query "BACKUP LOG [OperationsManager] TO DISK = N'D:\SQL\MSSQL.1\MSSQL\Backup\OperationsManager\OperationsManager_backup_200805061000.trn' WITH RETAINDAYS = 1, NOFORMAT, NOINIT, NAME = N'OperationsManager_backup_20080506100001', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


I read some of the posts regarding this error, but I am not sure how to do the steps. How do I change the recovery model? I am new to this so I have no clue how to do this.

Any help would be greatly appreciated.

Thank you.

View 8 Replies View Related

Backup Recovery Philosophy... Pull Up A Chair

Oct 20, 2007

(Be forewarned... this is long, but I think you'll find the information interesting)

When it comes to SQL Server backup/restore/recovery, my approach is based on a two-part philosophy. I'd like to share this with you and get you opinion and feedback...

First, I approach recovery from the instance level, not the individual databases. While a SQL Server instance is made up of system- and user-databases, I make no distinctions between them in terms of recovery - the instance is the whole "database". I'll later explain why.

Second, I approach it so that it will ultimately allow me to do a "displace" recovery... not just "in-place" restore. In other words, be able to restore the database on the current host ("in-place"), such in a case of a media or other failures where the host is intact - which is usually the common, easy case. But above that, be able to recover the database onto another host, such in a case of a catastrophic failure of the original host.

At any rate, the two part approach equates to what I consider provisions for doing "displace SQL Server instance recovery" - the ability to restore the whole SQL Server Instance, including the original system-databases, onto another host.

I come from an Oracle background... in the world where there's only one "database instance"... no such thing as "individual" databases. And really, the term "database" in Microsoft equates to what Oracle calls a "tablespace". Oracle's "system tablespace" equates to Microsoft's "system databases"... and so forth. It's a common case of one vendor calling it, "TOE-MAY-TOE", and the other calling it, "TOE-MAH-TOE". And in Oracle... I'm not the habit of making distinction between these tablespaces - system or non-system - when it comes to backing up... not many are (I would think). Our backup appoach views backing up the WHOLE database instance... therefore if needed... we can restore the WHOLE database instance. I can't find any other logical way to approach database backup.

But as I've found... in the world of SQL Server, there's seems to be a disconnect between how user-databases should be backed up (and restored) from the the way system-databases should be backed up (and restored). Most people make distinction between these database when it comes to backup and recovery.

Let me it explain it this way...

In my shop, my backup strategy provides the capability to restore both system- and user-databases. Some people might be surprised, but all this can actually be done using only the built-in SQL Server backup and restore functions. What I've also found is SQL DBAs find it almost "un-natural" to restore system-databases. I've heard and read some say is this... "just restore the user-databases on the new server and "re-create" the objects (that were in the previous system database) into the new system databases on the new host". Some also suggests using scripts to make "re-creating" these objects easier.

I think that people approach it this way because restoring system database onto another server (a different host name, a different file directory structure, etc) is not as simple as restoring user-databases.

But it is doable... and doable using only the built-in SQL Server functions.
My previous post on this matter can be found here (http://sqlforums.windowsitpro....22923&highlight_key=y)

I long time ago, I found a way to do this. And since then, my backup strategy has remained fairly the same... backup the whole sql server instance... and have the ability to restore the whole instance (if/when needed). And in the end, we not only have a great backup strategy, but a strategy for disaster recovery.

I agree that the approach to [simply] restore user-database, and then "re-creating" system-database objects is viable, but it's not "absolute". And here's why...

You'll always have to make provisions around when system-database objects are created/changed... for the life of the instance... such as logins, for example.
I've seen some application create extended procedures (in the master database) and create jobs and plans (in msdb).
You'll have to consistently ensure you update your "re-create scripts" to ensure you capture these objects and changes... through the life of the instance.
You'll have to be that much more intimate with the applicatons in order to understand all the components it may be adding outside it's user-database.

There a huge potential risk that you'll miss something.

Bottom line... restoring the whole SQL Server instance avoids any of the above caveats, guaranteeing a "physical restore" [of the original instance] versus one where parts of it had to be "logically" recreated and/or potentially be missing.

I admit, I also use scripts to complete a full SQL Server instance recovery. For me, it is a set of batch and sql scripts, but I only use it to expedite the process. And more importantly... the scripts are created once for an instance and doesn't have to be updated...
regardless of what occurs within the instance...
regardless if new logins or extended procedures are created/updated (in the master)...
regardless if new/updatess to jobs/maintenance plans/etc occur (in msdb)...
and I don't even really know what goes on in model... but again... it doesn't matter...
all of it gets restored.

The process... scripts or no scripts... is the same for all SQL Server instance I support... regardless of the idiosyncrasies of the application running on them. I have to make NO special provisions for any of them.

It's a solution where at the end of the recovery... I can tell the customer that whatever they originally lost has been restored (from the last backup)... without any doubt. Again it's a "physical restore", not one where parts of it had to be "logically" recreated.

Lastly... If you understand my philosophy... then you could probably understand why I don't consider Log Shipping and Replication to be a thorough disaster recovery solution. The reason being is that these solutions only provision for the recovery of user-databases, right? You cannot use log shipping or replication for system databases, right? And as with the other... you'll have to provision for re-creating system-database objects. Again, I admit log shipping and replication are viable solution, but again, with the idea of re-creating system database objects... it's not absolute... not for system recovery.

I'm looking to put my approach to the test... so I'm really looking for some good arguments and feedback. Thanks.

View 8 Replies View Related

Db Backup Simple Vs. Full Recovery Mode

Feb 14, 2007

When we do a full database backup manually, we are seeing the trn file reflect the current date/time, but we are not seeing the mdf reflect the new date/time. And we are not seeing the transaction log file decrease in size. the recovery mode is set to full, do we need to change to simple to see both the mdf being backup'ed?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved