Real World: Backup Strategy And Implementation, How?

Jan 24, 2007

Real World: Backup Strategy and implementation, how?
A quote:

€œReal World:

Whether you back up to tape or disk drive, you should use the tape rotation technique. Create multiple sets, and then write to these sets on a rotating basis. With a disk drive, for example, you could create these back files on different network drives and use them as follows:


//servername/data1drive/backups/AWorks_Set1.bak. Used in week 1, 3, 5 and so on for full and differential backups.
//servername/data2drive/backups/AWorks_Set2.bak. Used in week 2, 4, 6 and so on for full and differential backups.
//servername/data3drive/backups/AWorks_Set3.bak. Used in the first week of the month for full and differential backups.
//servername/data4drive/backups/AWorks_Set4.bak. Used in the first week of the quarter for full and differential backups.

Do not forget that each time you start a new rotation on a tape set, you should overwrite the existing media. For example, you would append all backups in week 1. Then, when starting the next rotation in week 3, you would overwrite the existing media for the first backup and then append the remaining backups for the week.€?

I understand these concepts, however in €˜the real world€™ how do you go about implementing these jobs in SQL2K and how on earth do you schedule the tasks to overwrite, for example, week 1, when on week 3€™s rotation.

Could I have real world examples or scripts for the jobs that would carry out this task? It appears that whatever course you do, it does not fully cover the above, and I have only worked on my own and never with a DBA, so I have never seen this implemented in any environment.

I would like full details on this please, as I need to get my head around it.

Thanks

Neil

View 6 Replies


ADVERTISEMENT

MSDE Or SQL 2005 Deployment Resources - Real World Input

Feb 9, 2006

I have traditionally done web app and client server programming and am have been playing around with a new tool (Win forms) app that I eventually want to distribute to other developers and a couple of business people in our organization. I have done apps in the past that all connect to a central server for data access. The I'm working on now will have an individual DB per user and should be available locally for a desktop version of the software.

I am looking for more resources on things to consider when deploying an app with either MSDE or SQL 2005 Express. More specifically, items like long term maintenance of the db once it's installed with the user, etc. (DB bloat, transaction files, auto maintenance routines I may want to build in, etc)

I have seen all of the msdn docs on what you need to deploy (and how to do it), but I'm looking fro input from people that have deployed it and any significant pitfalls they have run into with that sort of deployment.

Any links or book references would be appreciated, thank you,

Cy Huckaba

View 1 Replies View Related

SSAS Data Mining: Using The Time Series Model In A Real World Scenario.

Mar 15, 2008



I probably posted my question in the wrong forum so please check here:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3009605&SiteID=17

thanks

View 6 Replies View Related

(Project Real Implementation) Error Code: 0x80004005 OLEDB Connection To SQL Server

Mar 25, 2008

Hi List
Im trying to set up an implementation of Project Real --it works like this-
Create two system environment variables called REAL_Root_Dir and
REAL_Configuration with the values given below. Click on
Start -> Control Panel -> System. Go to the Advanced Panel, click Environment Variables button, then New in the System variables box.

If the Project REAL files were installed at C:Microsoft Project REAL, then the variable values will be:

Variable Name: REAL_Root_Dir
Variable Value: C:Microsoft Project REALETL

Variable Name: REAL_Configuration
Variable Value: %REAL_Root_Dir%REAL_Config.dtsconfig



The package OLEDB connections work like this
First read enviroment variable to get location of config file
Next read Config File to get connection string for Config Database
<?xml version="1.0"?>
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="Package.Connections[SQL - Configuration].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=(local);Initial Catalog=DataWarehouseABC;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
Next read Config database to get connection strings for Source and Destination databases



Destination database is called "DataWarehouseABC"
Source database is called "SnapshotABC"


the Source database OLEDB connection works 100%
however the destination OLDB connection we get this error below
PS--Both source and destination databases are on the same development machine , however both databases are restored bak files from another production machine







Error 1 Error loading LoadGroup_Daily.dtsx: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user 'xxxxxx'.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot open database "DataWarehouseABC" requested by the login. The login failed.".


Any ideas on how one OLEDB Connection in this package can get this corruption


thanks in advance
Dave

View 5 Replies View Related

Backup Strategy

Jul 18, 2000

Hi all,

Pardon me for asking a question that I know has been asked before. I need to develop a backup strategy for our SQL Server and I am looking for any help that anyone can offer including recommending good books for reading.


Thanks in advance,
Faustina

View 1 Replies View Related

Backup Strategy

Oct 18, 2000

In SQL Server 6.5, Is it generally better to dump the
transaction log first, then the database or to dump
the database and then run a dump 'tranlog with truncate
only' option?

Or, is this more a matter of personal choice?

Toni

View 1 Replies View Related

Backup Strategy

Jul 23, 2005

I've recently inherited a position where I am responsible for the well-beingof some DBs.2 (much) more important than others.The current recovery model, from what I can tell, is to do a full db/logbackup overnight.This .bak file is then written to tape as well as saved on the disk for 2days.Both these dbs are used fairly extensively 8-5pm and losing data would notbe good.The db sizes are approx 5gb and 3gb.This doesn't seem like the ideal situation to me. Everything I read tellsme... full backup periodically, differential nightly and transaction hourly.Agreed?If so then I have 2 questions:1. Is the best way to do this via a maintenance plan or by scripting andscheduling?2. What, if any, overhead can be expected with regular transaction backupsduring work hours?A bit of a pointer to #1 would be appreciated also.Thanks.

View 4 Replies View Related

What Is The Best Backup Strategy

May 23, 2007

We are currently doing daily full backup of system & custom databases since database size is small. Is that good idea ? or better option would be weekly full & daily incrementatl ?

Do we need to do any special backup on system databases or transactional logs ?



Please advice

View 30 Replies View Related

Backup Strategy

Nov 1, 2007

Hi


I have concern about an sql server. The server has the operating system and sql server installed locally. The databases and transaction log files is stored on SAN. We used to have the database backup and transaction log backups stored locally on the server. We tape the database backup and transaction logs every 24h. If we lose the san and the server then we are stucked with no backup easily accessible. And on tape we loose of 24h data. We decided to put up stand alone server with no connection to the san and dump the backups file on this server. We also put a secondary server sql I case of emergency, to test backups and We are looking at the getting a mirroring or log shipping solution but we are not there yet, next year€™s budget. We still using some old server left from migrating to virtualization.


Then I read the €œPractical Troubleshooting The Database Engine book€? best practice not to avoid net work drive backups. Stuck aging. Back with the backup to local drive and robocopy them? Keep them on the network drive, start using? MIRROR TO in the BACKUP DATABASE? Today we are using the Backup Database Task in the SSIS.


Advice?



Regards
Johan

View 4 Replies View Related

Backup Strategy

May 21, 2007

I am running SQL Server 2005 x64 Enterprise under Window Server 2003 x64 Enterprise. After reviewing many posts and suggestions in this forum, I am developing a backup strategy that should include keeping my transaction log file in a manageble size.



Please examine the following proposed backup schedule and let me know if this is considered a sound plan. The scripts below will write to disk and each night and then be backed up to tape.



*** TASK 1 ***



Backup transaction log

/* This script backs up the DSS database transaction log to disk, overwriting any
previous backup
*/

BACKUP LOG [DSS]

TO DISK = N'g:mssqlackuplogdss_log.bak'

WITH

INIT

, NAME = N'DSS-Transaction Log Backup'

GO



*** TASK 2 ***



/* This script shrinks the DSS database transaction log file

*/

BACKUP LOG [DSS] with truncate_only

dbcc shrinkfile(DSS_log)



**** TASK 3 ****



/* This script backs up the DSS database to disk, overwriting any
previous backup
*/
BACKUP DATABASE [DSS]
TO DISK = N'g:mssqlackupdatabaseDSS.bak'
WITH DESCRIPTION = N'DSS Full Database Backup'
, INIT
, NAME = N'DSS - Full Database Backup'
GO
/* Backup validation to ensure the file is valid before storing it */
RESTORE VERIFYONLY
FROM DISK = N'g:mssqlackupdatabaseDSS.bak'
WITH FILE = 1
GO


*** TASK 4 ***

Update statistics on the DSS database





View 3 Replies View Related

SQL Backup Strategy

Jun 15, 2007

This may seem like a silly question, but has anyone ever heard of a DBA or an Engineer deciding to not back up databases inside EM, and only relying on the RAID or third party software for redundancy?



SBS 2003 R2

SQL 2000

Veritas 8.6 open file agent, SQL agent, Exchange agent



Thanks for any input,



Rich

View 3 Replies View Related

Backup Strategy

Mar 2, 2007

Hi,

In my current organisation they are using SQL Server.

They are using TSM (Tivoli Storage Manager) to back up the server on a nightly base.

However I feel that this is not the correct way. Suppose I need some data back, I call technical support and they would restore the server. If another user made some changes to another database that day, he would lose his changes.

Of course they could restore a file but I am not sure if this is correctly. What will happen to the transaction log for example ?

My idea is that they should backup the database using the normal sql backup commando, dump the data to a folder and backup that folder.

Any suggestions please ?

Constantijn Enders

View 5 Replies View Related

Backup&Restore Strategy

May 4, 1999

Hi All,
Can any one help me with this..
I've a critical application that can't be stopped for a second.
I'd like to have an implementation that uses 2 sql 6.5 servers one as standby and
which is ready and up to date to take place and run instead of the master
server when it's down and when the master is back to work it's updated with the
data entered to the standby.
This process must be automatically to maximum extent.
Thanks
Mohamed

View 1 Replies View Related

Scripts For Backup Strategy

Jun 14, 2007

My maintenance plans are starting to acting weird. I'm building a custom script to manage the database backups on my server, but curious if anybody has some sample work that will allow me to avoid re-inventing the wheel.

A couple of primary constraints:
I want to do a full backup daily (and only retain 1 day of full backups)
Transaction Log backups every 20 minutes
I'd like to loop through the databases on the server automatically to make this a little more flexible.

You have anything you'd like to share? Or, bits of knowledge worth sharing?

Please advise,

alex8675

View 3 Replies View Related

Backup/restore Strategy Help

Sep 27, 2006

Hello.

I have only ever been required to take a full back up of my main prod database every night.

Now the times they are-a changing , and it is now required to be able to restore the database up to the last hour.

I've never really done much with tran log / differential backups so I'm asking for some advice as to what should be the best strategy. We are not a 24/7 shop we work from 6:30 am to 6:30 pm every day, so I thought:


Full backup @ 7pm
Backup tran log every hour after that starting @ 7am (as there are no changes overnight)

How does that sound? also when the tran log is backed up, is it truncated? Or do I need to shrink it? Basically I need to know what to do so it doesn't get too big!

Thanks

View 1 Replies View Related

Help Me Understand/engineer A Backup Strategy.

Aug 16, 2006

I tried searching, but it appears the search is still broken.Some of you know my other posts.. pleading for help with a problem (that's really sort of fixed itself/not remanifested itself).Upon this whole ordeal I decided to re-evaluate my nonchelant backup strategy. Which currently is:Backup our DB daily w/ transaction logs. (overwrite the backup daily on the tape).Backup our DB weekly w/ transaction logs.I rotate the tape weekly, on Monday morning. So there is a weekly backup and the last backup from monday morning on each tape.Along with that I have some optimizations and shrinks I run. Reading through the SQL BOL. It seems my strategy is ok, however if we lost the db at the end of the day, we'd lose a full days worth of work.So. Here in lay the questions.The BOL states to possibly back up twice a week, with differential daily backups and 4 hour transaction logs (example).So, I sorta get that..I make a device, "COMPANY.BAK", which writes to my external 'tape' unit.I make a job to back up the complete DB on sunday. to "COMPANY.BAK". (Not sure if I tell it to truncate the transaction log?).I make another job to make nightly differential backups to "COMPANY.BAK" with the 'append to media' switch on.I make yet ANOTHER job to make a transaction log backup. Here's what I don't get. If I set this thing to run every 4 hours, since it's not a differential backup.. rather incremental.. doesn't it just overwrite itself each time? It doesn't append itself to the backup right? So how much data could be missing if the log is overwriting itself rather than appending to itself? Also, if I turn on truncate log on backup, aren't I missing possibly critical transactions from the log for a sucessful restore?I guess I'm a bit befuddled here.

View 14 Replies View Related

Backup Estore Strategy For Production DB.

Jun 9, 2008

Hi,

I would like to have guide for backup
estore strategy for production DB..if i have to apply for high availability of production DB..

consider scenario ..Full backup every week, differential backups every 6 hours, and logs every fifteen minutes.

suppose my DB is crashed at 8:14 AM and my last logs were backed up at 8 AM then by looking above startegy then i lost my DB from 8 am to 8:14 am???? if not then where it shud find???

also looking above scenario by taking backup for such high frequency is it full my disk?? guide me pls...

T.I.A

View 4 Replies View Related

Backup Strategy For A 1000GB Database

Jul 20, 2005

Hi,Could anyone tell me the backup strategy for a 1000GB database?Thank you!Peter Wang*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Backup Estore Strategy For Critical Issue

Jun 9, 2008

Hi
I have one query regarding backup
ecovery startegy
hi

I have Db with simple recovery model.. recently i updated my column without where clause now i need to recover all my original state of that column..i just have idea @ set DB recovery model to Full then take full back up and then take differantial back up and then take transactional log back up and restore it as full , diff and then most recent transactional log back up...but still i didnt get original state of that table column...

please guide me

T.I.A

View 13 Replies View Related

Co-existence Of Log-Shipping And A Independent Backup Strategy

Dec 19, 2007

We have a critical Production database on which we want to setup Log-Shipping. We have also purchased the Symantec NetBackup utility for taking Backup to Tape Drives.
We Know that there are some inherent problems with using Log-Shipping and a Backup Strategy together, and thus we were finding out various ways in which we can run both in tandem.

One of them was taking the Symantec Log backups with Copy-Only option.
The main problem with this is that the Symantec Backups becomes dependent on the Log-Shipping Backups and also most of their Log-Backups become useless. Also, we are not sure whether the utility allows Copy-Only backups

The second alternative was to disable the Backup job of Log-Shipping on the Primary Server and to use the Log-Backups done by the Symantec utility for performing the Restores on the Secondary.


Thus, if Log-Shipping is scheduled to run say every 4 hrs, and the Symantec Log-backup happens every 1 hour, then at an interval of every 4 hrs, the Restore Job on the secondary will pick up the 4 backups done by the Symantec Backup utility and Restore each one of them in sequential manner.
But, I guess it is not easy to have a manual Restore Policy in place.
I was really banking on this solution until I found that the Restore Job of the Log-Shipping setup is dependent on the Filename of the Transaction log file which the systems generates automatically, and it won€™t be easy to create a customized Restore Job on the secondary server which takes in all the Log Backups generated by the Symantec Backup utility and Restore the Secondary database.

Have any one of you ever face this issue? Would like to know what is the best way to keep both of them running together.

View 3 Replies View Related

Suspend All Transactions For A Split-Mirror Backup Strategy?

May 11, 2006

We currently use a split-mirror backup strategy for our Sybase database, which has a "quiesce database" command to suspend all transactions. By quiescing the database before splitting the mirror, we suspend all transactions to ensure we get a stable backup of the environment. It works very well for us and I'm trying to understand how we could implement this with our SQL Server 2005 DB.

(I'm aware of SQL Server mirroring and that there are other ways of possibly backing up the DB. In this post however, I'm only interested in how I would make the split-mirror strategy work if I wanted to pursue it. I'm trying to avoid paying for software that uses the VDI as it's quite costly.)

Can someone help me with how I would accomplish a split-mirror backup strategy in SQL Server 2005 (without using a vendor's software that uses the VDI)? I have to imagine there's something similar to the "quiesce database" command in SQL Server...

I appreciate the help!

View 3 Replies View Related

SQL 2012 :: Local Backup Strategy On Primary With Transaction Log Shipping Enabled To Secondary DR Site

Oct 2, 2014

I have a scenario where a customer is going to be using Log Shipping to the DR site; however, we need to maintain the normal backup strategy on the current system. (i.e. Nightly Full, Every 6 Hour Differential and Hourly Transaction Log backup)I know how to setup Transaction Log Shipping and Fail-over to DR and backup but now the local backup strategy is going to be an issue. I use the [URL] .... maintenance solution currently.

Is it even possible to do regular backups locally keeping data integrity for your backup strategy with Transaction Log Shipping enabled?

View 2 Replies View Related

Backup Strategy - SQL2005 Tools Versus 3rd Party Tools

Jun 1, 2007

I am running SQL Server 2005 x64 Enterprise under Windows 2003 x64 Enterprise. My current backup strategy uses T-SQL jobs run by SQL Agent (writes out *.bak files) and then I have an Integration Services job that copies the *.bak files to our NAS device. I have performed a restore without issue. The jobs are all automated every four hours via SQL Agent. Is this a sound strategy or are there additional benefits to using 3rd party tools? If so, what are the advantages and which tool provides them?

View 3 Replies View Related

Hello World: SQL Server + ASP.NET

Mar 15, 2006

Hi everybody,
This is propably very silly question...
I've used SQL Server for sometime now, but always through tools supplied by my hosts (my Little Admin, SQL Server Web Admin).
I always wanted to try running the real thing, so I've installed SQL Server 2K developer edition on my machine. I also have IIS and .NET framework running.
What would be the next step to see those 3 components in action? I have noticed Northwind database in the Enterprise Manager. How do I make the connection?
I have the SQL Server Service Manager running fine, but when I open Query Analyzer it comes up with the window "Connect to SQL Server" and this little dropdown, where you can choose the server is actually empty. What do I do?
Thank you all for your pointers.

View 1 Replies View Related

It's Really A Strange World Over In....

Jul 16, 2007

...the Access forum...so many bad practices

Like this one

http://www.dbforums.com/showthread.php?p=6284279&posted=1#post6284279

View 12 Replies View Related

DB Web Access From Around The World

Mar 29, 2004

Here's a nice little scenario:

Say you have users who would be accessing a SQL Server database over the web from around the world (mainly the US, Japan, and UK), and you wanted to make the access as fast as possible.

Say also you have a couple of million dollars for hardware and software.

What architectures would you build?

Thanks.

Henry

View 3 Replies View Related

World Week

Apr 13, 2008

Hi, I have a table ABC. It has 2 columns, A and B. At column A i have 3 rows, Week,Goal,Used. Let say for column B at row Week is WW15 now, after 7 days i want the row to be updated to WW16. How do i do that? Advice please.

View 14 Replies View Related

Hello World: SQL Server &#043; ASP.NET

Mar 15, 2006

Hi everybody,

This is propably very silly question...

I've used SQL Server for sometime now, but always through tools supplied by my hosts (my Little Admin, SQL Server Web Admin).

I always wanted to try running the real thing, so I've installed SQL Server 2K developer edition on my machine. I also have IIS and .NET framework running.

What would be the next step to see those 3 components in action? I have noticed Northwind database in the Enterprise Manager. How do I make the connection?

I have the SQL Server Service Manager running fine, but when I open Query Analyzer it comes up with the window "Connect to SQL Server" and this little dropdown, where you can choose the server is actually empty. What do I do?


Thank you all for your pointers.

View 2 Replies View Related

Hello Model World?

Dec 18, 2007

Hi!
I want to get hole through to Analysis Services 2005
from MS SQL Server 2005 with openquery,
so I created linked server (MSOLAP).
How to test that I can access an "database" on Analysis Services Server?

Something like select * from openquery(linked_olap, getdate())
or openquery(linked_olap, 'print hello world')
or openquery(linked_olap, select results from dataminingmodelCreatedWithVstudio)
?
Thanks
Bjørn

View 4 Replies View Related

Handling World Time With SQL And ASP.NET

Mar 24, 2004

Hi,

I would like to capture date time in my SQL during a certain operation. This piece of data will be displayed to an ASP.NET page.

When displaying the date time, I want it converted to the local time zone equivalent as set in the user's operating system.

What setup do I need on the SQL and the coding in the ASP.NET page?

Thanks.

View 2 Replies View Related

In The World Of J2EE, What Are JSF Component That

Aug 7, 2005

In the world of J2EE, What are JSF component that maps to
the .Net list components?

View 1 Replies View Related

Stored Procedures: How In The World Do I Use These Things?

Apr 11, 2008

I was told I can use them with SQLDataSources, but I have no clue how to do it.
I believe I have managed to set up the querry in the datasource correctly, but what do I need to do to actually use it in my VB code?
 
Any help is appreciated, and any tutorials you've found to be usefull on the subject are sure to help.
Thanks

View 3 Replies View Related

SSRS Report With Geographic World Map

Jan 3, 2014

I am creating a SSRS report with geographic world map. I am able to to download few shape files and use them.

However, the maps which i have downloaded misses few locations (for e.g. South Sudan which was formed few years back).

Where can i find the latest world map shape file?

View 3 Replies View Related







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