Backup Strategy for MSSQL

Hi everyone. As the company's "DBA" (long story) I wanted to get some opinions on my backup plan. Currently, I have the following in place:

Full weekly backup of master.
msdb is treated as a user database, so msdb and two user databases receive Full weekly backups, Daily differentials and hourly transaction log backups.

The maintenence plan will remove logs older than 2 weeks.

Our networking group backs up the MSSQL to tape regularly.

I'm fairly new to DBA work, but if this scenario sounds like a sound plan, I'd appreciate any feedback. Thanks in advance!We back our stuff up nightly, however you have to decide how much info you are willing to lose should your system crash. If weekly backups have been working and you feel confident in your hardware then stick to that schedule.

HTH,
Aric|||

Quote:

Originally Posted by DbAFtW

Hi everyone. As the company's "DBA" (long story) I wanted to get some opinions on my backup plan. Currently, I have the following in place:

Full weekly backup of master.
msdb is treated as a user database, so msdb and two user databases receive Full weekly backups, Daily differentials and hourly transaction log backups.

The maintenence plan will remove logs older than 2 weeks.

Our networking group backs up the MSSQL to tape regularly.

I'm fairly new to DBA work, but if this scenario sounds like a sound plan, I'd appreciate any feedback. Thanks in advance!

Hi there,

Backup / recovery plan should be designed based on the importance of your data. As a consultant i am managing 17 database servers, some servers are configured to run backup routine every hour, whereas, some servers are configured to run backup routine every day. Good luck & Take care.|||Thanks folks for the replies.
Well, the vendors that put the web server and the MSSQL DB in place were using a Simple Recovery Model and I felt the nature of the data dictated transaction log backups. The system accepts college applications and I felt any data loss was something I wanted to avoid due to the importance of that data! The intention is that we can experience minimal loss utilizing the strategy to restore from the weekly full, nightly differential and every half hour transaction log backups.|||</bumpitybump>sql

Backup solution - question about transaction log

I've just inherited (i.e., our sys admin / DBA left the company) a fairly small SQL Server that's running 7 production databases. Most are quite small, but there are two which are about 40gb each. Traffic is quite low - ~30-40 users at one time doing your basic SELECT / UPDATE / INSERT stuff.

Anyway, I was going through some of the backups jobs and noticed that the transaction logs for each database were absolutely huge (in some cases bigger than the DB itself) which led me to think the log wasn't getting truncated.

The T-SQL being run in each case was

Currently, the transaction log for 6 DBs is backed up 3 times a day (and the 7th, "mission critical" DB is backed up every 15 minutes) with the following T-SQL:

BACKUP LOG <database> to <device> WITH NOINIT, NOFORMAT, NOSKIP, NOUNLOAD

5 of the 7 databases get a full backup twice a day, with the 2 larger ones getting a differential, with e.g.,

BACKUP DATABASE <database> TO <device> WITH NOINIT , NOUNLOAD , NAME = N'db', NOSKIP , STATS = 10, DESCRIPTION = N'db', NOFORMAT , MEDIANAME = N'db'DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='db'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='db')
RESTORE VERIFYONLY FROM <device> WITH FILE = @.i

This is then backed up to tape each night.

Looking through the documentation, those WITH commands are largely the default settings so I'm not sure why they're specified explicitly.

If I issue a

BACKUP LOG <database> to <device> WITH INIT, SKIP

then the log does get truncated. However, could someone explain the implications of that for me? As I understand it, INIT will overwrite any existing sets in the device, but considering that it will always backup anything that hasn't been committed then should it be a problem?

Alternatively could someone perhaps explain why the log wasn't getting truncated? It is my understanding that this should happen every time a full backup is completed... which is twice a day. Or does the Transaction log need to be manually shrunk every now and then?

Also, I understand the DECLARE... part in the last part of that DB backup SQL, but is it at all necessary?

Finally, does this backup strategy seem viable? Any thoughts and comments are appreciated!
Matt

By default in the backup database command if you do not specify anything it will append ,eg...

backup database xxxx to disk='F:\test\xxxx.bak' and check the bak file size

now again reissue the same command,

backup database xxxx to disk='F:\test\xxxx.bak' and once again check the bak file size.......it will be double the size

next time issue the same command using WITH INIT option,

backup database xxxx to disk='F:\test\xxxx.bak' WITH INIT now see the bak file size it will be the same size as in 1st case.........if you do not specify anything SQL server will use the default With NOINIT option..........so you need to specify them explicitly if you needed to overwrite........

the log gets truncated if you specify the command,

backup log dbname with truncate_only.........this command will be depreciated in future versions........once you issue this comand, all the committed transactions will be rolled forward and written to data file and uncommitted trans will be rolled back.......

since you have a mission critical db i suggest you go for Log shipping or Database mirroring in sql 2005.........instead of relying on your db backups.........if you have sufficient disk space go for full backup and if you feel the db is important and if you want to have db consistency you can have tran log backups and differential backups else not required........

|||Am I right about when the transaction log should get truncated - i.e., on a full database backup?|||

You are right, that the transaction log should be truncated after a full backup. However, note that the contents of the log will be truncated and the physical file will maintain its size. Therefore, you may see a large transaction log file although it won't be full. In order to reduce the size of the file, you'll need to run some form of SHRINKFILE operation.

HTH!

Backup plan reccomendations for user with ZERO experience.

Hello all, I was just awarded the job of maintaing the database server
for our company. I have basically ZERO experience using MS SQL Server
2000. Can anyone point me in the direction of a good resource for
creating backups of our database? I would love something that comes
with a gui that really simplifies the process; seeing as how i have
never even opened the MS SQL program.

Our database is fairly small we have 7 users with access to the
database. That is it.

any advice or good resources would be greatly appreciated.Look into Enterprise Manager, Maintenance Plan Wizard. It will help
you with various tasks including backups.
In my backup jobs, I first perform a log backup then a full backup.
This will keep the size of the transaction log in check as it gets
truncated during this process. (Assuming the database is in FULL and
not SIMPLE. Check the properties of the database via EM).

I found that when I only did a full backup, my transaction log kept
growing.
MC2
Rob

On Aug 14, 10:49 pm, scott s <scott.str...@.gmail.comwrote:

Quote:

Originally Posted by

Hello all, I was just awarded the job of maintaing the database server
for our company. I have basically ZERO experience using MS SQL Server
2000. Can anyone point me in the direction of a good resource for
creating backups of our database? I would love something that comes
with a gui that really simplifies the process; seeing as how i have
never even opened the MS SQL program.
>
Our database is fairly small we have 7 users with access to the
database. That is it.
>
any advice or good resources would be greatly appreciated.

|||scott s (scott.streit@.gmail.com) writes:

Quote:

Originally Posted by

Hello all, I was just awarded the job of maintaing the database server
for our company. I have basically ZERO experience using MS SQL Server
2000. Can anyone point me in the direction of a good resource for
creating backups of our database? I would love something that comes
with a gui that really simplifies the process; seeing as how i have
never even opened the MS SQL program.
>
Our database is fairly small we have 7 users with access to the
database. That is it.
>
any advice or good resources would be greatly appreciated.

First you need to determine which recovery model you want to use. There
are three to choose from FULL, BULK_LOGGED and SIMPLE. BULK_LOGGED is really
a variation of FULL, and we can leave it out for now.

In FULL recovery mode you are able to recover from a failure to any point
in time. In SIMPLE recovery, you can only recover by restoring the last
backup. So the question you need to answer: if you take a full backup
at midight, and at 15:00 something bad happens (disk crash, an UPDATE
without a WHERE clause or similar), what is your business requirement:
being able to recover the state at 14:59? Or are you fully content with
restoring the backup from midnight?

If you are content with restoring from the most recent backup, make
sure the database is simple recovery:

ALTER DATABASE db SET RECOVERY SIMPLE

now all you have to bother about is setting up full backup that is
scheduled each night, and that the backup file is copied somewhere. You
should make sure that you have backups a couple of days back in time
available, in case some accident is not discovered in time, or if a
backup is unreadable for some reason.

If you need up-to-the-point recovery, you also need to take log backups,
and you need full recovery. It's important to understand that taking log
backups is independet from database backups. If you never back up the log,
it will grow and grow. Since you are unexperienced, the best may be to
set up database backups through a maintenance plan in Enterprise
Manager.

Once you have your backups, you should also test restoring them, so
that you know the procedure if disaster strikes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Backup of Remote SQL 2005 Server

Hello Friends:

My problem relates to backing up my MS SQL 2005 database which is sitting on a shared server at a hosting company.

OVERVIEW:
- Hosting company is using MS SQL 2005
- I am using the SQL Server Management Studio that comes with SQL Server 2005 Standard (NOT Express), which is installed on MY PC.
- So, I am connecting to the SQL server over the internet

WHAT I WANT TO ACHIEVE:
- I would like to backup the data sitting on the Hosting company's MS SQL Server. I only have one database on this SQL Server. There are of course 100s of other databases on the same server which belong to other customers of the hosting company.

- I want to bring the backup to MY PC, from the SQL Server.

- As far as I can tell the following options within SQL Server Management Studio may be of help to me. I do not know which one I should use or which one is best or what is the proper method. 1) Select Backup option from the Tasks menu (but it only shows me drives/devices on the Hosting SQL Server, not my PC, so I cant backup to my PC) 2) Export Data (it does not work, showing errors not a trusted connection I have no clue what a trusted connection is. 3) Copy Database (which is supposed to copy the remote database on the Hosting company SQL server, to my local SQL Server running on my PC). I go through the wizard, on the last screen it just hangs i.e. shows- not responding)

MY QUESTION TO THE COMMUNITY:
How do I backup the database sitting on the hosting company SQL server? I of course need to bring the backup to my PC.What you'll need to check is whether you have access to a folder from SSMS which also can be accessed by other means, eg FTP. If so, you can run a backup to that folder, and then download the backup file. SSMS itself does NOT give access to your local disk drives.

But, why do you want to backup the database yourself, does not the hosting company take backups for you?|||Management studio does not support the UNC path, generally it will show you the local disk to the server. To perform network back up you need to write the TSQL command.
The username by which you login into CMS, requires permission to perform back up and restore. Basically the Admin of hosting company will assign permissions to each and every user. Speak to hosting company Administrator for help.|||Thank you all for your quick reply. My ISP does perform a backup. Then why do I want to do a backup to my local PC?

Well, I am planning to move to a new ISP. And so I need to move my database also. I first need to backup the SQL database (or extract the data) from my existing provider. Then I need to somehow upload the database to the new provider.

I am sure this is a very common issue. People must on occasions move from one hosting provider to another, and if they have a database they will not want to lose all the data.

Since I posted my first message, I signed-up with 3 ISPs. It did not work with 2 of them, and tech support could not tell me what to do. Then I signed up with Network Solutions, and I can now at least Export the data.

Copy Database still does not work. But I think I know why. Since there are 100s of other databases on the same server (and I do not have access to them) the Copy Database wizard probably tries to access them while displaying the list (which it does when it asks you for the Source Database).

I think a user with Sysadmin rights would be able to run Copy Database, but getting those rights of course would not be possible in a shared hosting environment.

Over the last several days, I have called the Tech Support of most major hosting companies in US, UK, CA, and some countries in Europe. I was able to speak to 'real tech humans' in each company. But surprisingly NOT EVEN ONE could answer my questions, or even shed some light on the issue. They were completely clueless!!

It seems either no one changes hosting companies or no one has any important data to backup or everyone who knows about these issues uses only dedicated servers (which cost lot more money). Either way, the tech support in these hosting companies (most of whom have at least 200 employees and $4million+ turnover) had little idea about what I was talking about. Network Solutions seems to be the best and has techs available 24X7, and atleast they all speak clearly and try to help.

By the way I have been in IT for 20 years, I did make sure I was asking the questions clearly.|||why not create a share from the host to your machine, open a query session and use t-sql to backup the database to your machine?

Why does it have to be GUi based?|||This might be an easy answer to a complicated question/issue-- but I've used a free tool for backup that has proved to be actually high-performance stuff... can be downloaded free at: www.idera.com/freetools, and its called SQLsafe Freeware Edition.

Hope that can do the trick, why not, its the right price... free. ha!

Enjoy, and let me know how it works for you.

Backup MS SQL Server using ASP.NET

Hi!

For the moment I’m hooked up with this hosting company that refuses to give me any backup files of my Microsoft SQL Server database/tables. I’ve even asked if the can do it just once but they still won’t. I don’t know what they are doing because they won’t even let me use Enterprise Manager so, from what I know, can’t use the scripts that I’ve found that use stored procedures.

Which is the easiest way to do a full backup just using the built in functionality of ASP.NET 1.3 and with limited permission on the SQL server? The goal is to move everything to a new company.

A friend recommended this way:

Create a class that holds both the table structure and a dataset for the data.
Each instance of these class is then stored in a collection that we serialize and store on the web server.
When this is done we reverse the process at the new hosting company.

Would this work / be the easiest way?

ThanksYou can take help of SQLDMO in this case, refer http://www.aspfree.com/c/a/VB.NET/Simple-BackupRestore-Utility-With-SQLDMO-Using-VBNET/ and http://codingforums.com/archive/index.php?t-25387.html for information.

HTH|||Thanks!
I've got the same advice on another forum, http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66036 (remove if it's not ok to link), who also suggested using DMO.|||Hey...I think that was me. :)

How you doing Satya?|||Hi Derrick
Thanks, I'm fine.. hope you had a good holiday as I don't see on the forums since a month (I think).
Its good to catch other forums too ... different from routine :)


How you doing Satya?

Backup Location

Hi,
Our web site's SQL Server is hosted by another company in internet. I want
to backup our database but the backupsets will be created on the host. Is it
possible to create a device the way that backupsets be created on my local
SQL Server?
Any help would be greatly appreciated.
Leila
No, SQL server will not "push" the data to the client machine from where you execute the backup.
Perhaps you can FTP the file after performing the backup?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:%23lnZYDg3EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Our web site's SQL Server is hosted by another company in internet. I want
> to backup our database but the backupsets will be created on the host. Is it
> possible to create a device the way that backupsets be created on my local
> SQL Server?
> Any help would be greatly appreciated.
> Leila
>

Backup Location

Hi,
Our web site's SQL Server is hosted by another company in internet. I want
to backup our database but the backupsets will be created on the host. Is it
possible to create a device the way that backupsets be created on my local
SQL Server?
Any help would be greatly appreciated.
LeilaNo, SQL server will not "push" the data to the client machine from where you execute the backup.
Perhaps you can FTP the file after performing the backup?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:%23lnZYDg3EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Our web site's SQL Server is hosted by another company in internet. I want
> to backup our database but the backupsets will be created on the host. Is it
> possible to create a device the way that backupsets be created on my local
> SQL Server?
> Any help would be greatly appreciated.
> Leila
>