Best Tool For Backup/Recovery
Apr 29, 2004Hi,
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.
ADVERTISEMENT
Is There A MS SQL Backup Tool That Will Zip Or Compress The Backup Files?
Feb 27, 2007Currently we use a SQL maintenance plan to do a full backup of all our databases daily (about 40 databases on our production server). As you can imagine, this eats up disk space quickly so currently we manually zip the backup files and/or move them to an archive drive. I considered writing an application to walk through the backup folder structure and zip any .bak file it finds, but I know there are some third party tools out there that will backup/restore a MS SQL database.
I was wondering if any of these also zip the backups once they are created. Any recommendations or suggestions are welcome.
SQL Server Backup And Third Part Backup Tool
Oct 2, 2001SQL 7.0 SP1.
We do SQL Server backup and also third part backup (with SQL agent active) of SQL Server databases.
Sometimes happen this situation:
01.00 SQL Server backup of DB1
01.00 third part backup of DB1
02.00 SQL Server T-LOG backup of DB1
03.00 SQL Server T-LOG backup of DB1
03.30 SQL Server differnatial DB backup of DB1
04.00 SQL Server T-LOG backup of DB1
or this situation:
01.00 SQL Server backup of DB1
02.00 SQL Server T-LOG backup of DB1
03.00 SQL Server T-LOG backup of DB1
03.10 third part backup of DB1
03.30 SQL Server differnatial DB backup of DB1
04.00 SQL Server T-LOG backup of DB1
My questions now are:
Are my SQL Server backups valid (DB,Differenatial and T-LOG)?
In case of a restore operation, can I use my SQL Server backups without problem?
In other words, can we leave the third part backup tool active or it should be taken away?
Can anybody clarify this for me?
Thank you in advance.
Franco
Recovery :: Differential Backup Much Larger Than Database / Full Backup
Nov 16, 2015I 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 RelatedTool In SQL For Backup Of Some Columns
Dec 27, 2004Hi ...
I really need a tool that doesn't make a backup of all content of my database, and I don't want a script with just a schema of it.
I want a backup of just some columns and the data that has in them.
I know that It has a tool, but I never heard about it, and It's too important, so much that now I'm needing.
Anyone knows something?
Thanks
Mssql Db Backup Tool
Jun 2, 2007Hi all!
I need to have a backup of a database that is stored on a remote server.
Is there any tool to do it?
Thank you in advance
Backup And Recovery
Dec 15, 20001. 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
DTS Backup And Recovery
Jul 18, 2003Is 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
Backup And Recovery
Apr 22, 2004Hi 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
Backup And Recovery
Apr 2, 2008How can we check whether the backup is in consistent state or not.
View 14 Replies View RelatedBackup And Recovery
May 17, 2008Hi,
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?
Recovery And Backup
Jul 23, 2005Could someone please give me an idea of the differences between SQLServer and Oracle, backup and recovery options.Thanks
View 1 Replies View RelatedBackup And Recovery
Oct 18, 2007i 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
Backup And Recovery...
Nov 14, 2007Hi 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)
Is There A FREE, Pure Web Based Backup Restore Tool ?
May 10, 2008HI
I am trying to find a pure web based backup restore tool that can be used on MS SQL 2000.
I have found a few that claim to be web based, but then it tells you to log into your MS SQL server and make some adjustments.
I know that microsoft Enterprise manager comes with a backup / restore tool. but 1) i dont want to have to install a complete copy of 2005 server, and 2) i dont hav ethe physical space to do it right now anyway. all i need to do is move 2 databases from one account to another.
thanks
Backup And Recovery Problem
Apr 19, 2004i'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?
Doubt In Backup And Recovery
Apr 1, 2008every 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
Backup && Recovery History
Jul 23, 2005Hi 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 RelatedRecovery :: How To Get Continuously Backup
Aug 25, 2015I 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 RelatedDatabase Recovery If No Backup Available
Dec 19, 2006Hi,
If we delete a Database accidentally (no backup exists), Is it possible to recover. How?
Thanks
ven--
Log Backup In Point-in-time Recovery
Aug 10, 2000Hi 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
Tape Backup Recovery Time
Jul 28, 2004How 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
__________________
Sharepoint DB Recovery In Log Backup Files
Oct 11, 2011I 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 RelatedLOG Backup And Remote Recovery - Msg 4330
Aug 22, 2007Hi,
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 !
Simple Recovery And Full Backup
Jun 10, 2006Hi 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
Backup/recovery Larger Databases...
Apr 10, 2008Greetings 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
Recovery :: Backup And Restore At Same Time
Nov 23, 2015SQL 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.
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.
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.
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.
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.
Recovery :: How To Backup Raw Data Only With Just Views And Tables
Sep 23, 2015My 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?
Db Backup Simple Vs. Full Recovery Mode
Feb 14, 2007When 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