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 SQL Files

Hi,

We are about to install MSSQL Server 2000, on a Windows XP Home
Machine. However, we have servers we could set routine backups of files
to be done to. What what be the best way of doing this?

Is there functionality in SQL Server 2000, where we can say dump all
data definitions, accounts, and data to files on this drive at regular
intervals?

What other suggestions do you have apart from obviously the usual RAID,
and Tape Drive stuff?

Thanks

DavidDavid (david.goodyear@.gmail.com) writes:
> We are about to install MSSQL Server 2000, on a Windows XP Home
> Machine. However, we have servers we could set routine backups of files
> to be done to. What what be the best way of doing this?
> Is there functionality in SQL Server 2000, where we can say dump all
> data definitions, accounts, and data to files on this drive at regular
> intervals?

You could set up a job that runs from SQL Server Agent that backups
the database to a disk somewhere using the BACKUP command.

--
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|||What "flavour" of SQL Server 2000 (e.g. Enterprise, Developer) will you
be installing on this machine? I only ask because certain flavours
won't let you install the server components on an XP machine...

Anyway, you can use the Database Maintenance Plan Wizard to set up a
regular full backup of a database. This will preserve all the data in
the database (including the schema), objects such as functions and
stored procedures and the user accounts you have defined in the
database.

In addition to backing up any user defined databases, you should also
consider doing a backup of the system databases (master, msdb, model)
via the Database Maintenance Plan Wizard. Doing this should allow you
to have a backup of the server logins, SQL Server Agent jobs,
maintenance plans etc.

Hope that helps a bit

Backup script

Hi,

I'm currently working on a backup script for my MSSQL databases. I have managed to make a working backup command but I have one problem.
I need every new backup file to have a uniqe filename, for example backup_UNIQE.bak. How can I make a variable that can do this?

Alexander

Here's the basis of one that can be used which uses the date as a unique identifier:

Code Snippet

declare @.filename nvarchar(2000)

set @.filename = 'c:\yourbackupdirectory\dbname_bk_' + convert(varchar,getdate(),112) + '.bak'

backup database northwind
to disk = @.filename

HTH!

|||

Hi,

Can you stick a date on the end of it? See below and then pass the filename into you script somehow.

declare @.datet varchar(10), @.filename varchar(20)

set @.datet = convert(varchar(10),getdate(),112)

set @.filename = 'backup'+@.datet+'.bak'

print @.filename

will give you

backup20070824.bak

You could always add the time to it :

set @.datet = replace(convert(varchar(8),getdate(),114),':', '')

Any help to you?

Cheers

Matt

|||Thank you both! This worked like a charm!

Matt Tolhurst:

Can you please explain this one: set @.datet = replace(convert(varchar(8),getdate(),114),':', '') ?

I'm not TSQL coder yet, and I would appricitate it very much!

- Alexander
|||

Alexander Davidsen wrote:

Can you please explain this one: set @.datet = replace(convert(varchar(8),getdate(),114),':', '') ?

Hi,

That bit is simply taking the time part of the getdate function and removing the colons, as you don't really want to put colons in the file name.

declare @.datet2 datetime, @.datet varchar(10), @.filename varchar(20)

set @.datet2 = getdate()

print @.datet2 --

Aug 24 2007 11:07AM

set @.datet = convert(varchar(10),@.datet2,112)

print @.datet

20070824

here is the time part:

set @.datet = convert(varchar(8),@.datet2,114)

print @.datet

11:07:44

set @.datet = replace(convert(varchar(8),@.datet2,114),':', '')

print @.datet

110744

Any clearer?

Matt

|||Thanks Matt

This made it alot clearer!

Have a nice weekend!

- Alexander

Backup of mssql server in LAN

hi everyone,
I am trying to backup and restore mssql server using VDI Interface.
Where i can list the no. of sqlserver's in LAN.
To backup and restore, i have to separately run my application in
individual machines where
mssql server was installed. I would like to configure backup and
restore mssql server available in connected LAN, and to control in one
machine using only one application in only one machine.
I am getting GetConfiguration method of VDI API time out while trying
to connect mssql server another machine.
vds->GetConfiguration(INFINITE, &config)
never times out but for more than half an hour couldn't get connected
with mssql server
running in another machine.
I would like to get a solution to connect all mssql server for backup
and restore from a single
machine in a LAN using VDI API without time out in GetConfiguration
method.
Any Suggestions on this will be greatly appreciated.
Thanx in advance
regards,
deenaHi
As no-one else has replied I thought I would put in my 2c!!
Most backup solutions seem to have an agent component, which would indicate
that it may not be possible to do this. You may have to log a call for this
as it is not a feature that many people would use.
John
"tsgd84@.gmail.com" wrote:

> hi everyone,
> I am trying to backup and restore mssql server using VDI Interface.
> Where i can list the no. of sqlserver's in LAN.
> To backup and restore, i have to separately run my application in
> individual machines where
> mssql server was installed. I would like to configure backup and
> restore mssql server available in connected LAN, and to control in one
> machine using only one application in only one machine.
> I am getting GetConfiguration method of VDI API time out while trying
> to connect mssql server another machine.
> vds->GetConfiguration(INFINITE, &config)
> never times out but for more than half an hour couldn't get connected
> with mssql server
> running in another machine.
> I would like to get a solution to connect all mssql server for backup
> and restore from a single
> machine in a LAN using VDI API without time out in GetConfiguration
> method.
> Any Suggestions on this will be greatly appreciated.
> Thanx in advance
> regards,
> deena
>

Backup of mssql server in LAN

hi everyone,
I am trying to backup and restore mssql server using VDI Interface.
Where i can list the no. of sqlserver's in LAN.
To backup and restore, i have to separately run my application in
individual machines where
mssql server was installed. I would like to configure backup and
restore mssql server available in connected LAN, and to control in one
machine using only one application in only one machine.
I am getting GetConfiguration method of VDI API time out while trying
to connect mssql server another machine.
vds->GetConfiguration(INFINITE, &config)
never times out but for more than half an hour couldn't get connected
with mssql server
running in another machine.
I would like to get a solution to connect all mssql server for backup
and restore from a single
machine in a LAN using VDI API without time out in GetConfiguration
method.
Any Suggestions on this will be greatly appreciated.
Thanx in advance
regards,
deenaHi
As no-one else has replied I thought I would put in my 2c!!
Most backup solutions seem to have an agent component, which would indicate
that it may not be possible to do this. You may have to log a call for this
as it is not a feature that many people would use.
John
"tsgd84@.gmail.com" wrote:
> hi everyone,
> I am trying to backup and restore mssql server using VDI Interface.
> Where i can list the no. of sqlserver's in LAN.
> To backup and restore, i have to separately run my application in
> individual machines where
> mssql server was installed. I would like to configure backup and
> restore mssql server available in connected LAN, and to control in one
> machine using only one application in only one machine.
> I am getting GetConfiguration method of VDI API time out while trying
> to connect mssql server another machine.
> vds->GetConfiguration(INFINITE, &config)
> never times out but for more than half an hour couldn't get connected
> with mssql server
> running in another machine.
> I would like to get a solution to connect all mssql server for backup
> and restore from a single
> machine in a LAN using VDI API without time out in GetConfiguration
> method.
> Any Suggestions on this will be greatly appreciated.
> Thanx in advance
> regards,
> deena
>

backup log

I'm trying to export my database to a server (WebMatrixHosting.net) ...
So I'm using the DTS - Import/Export Wizard of MSSQL Tools ...
It generates the script ok, but when the copy starts I receive an error: Failed to copy objects from SQL Server to SQL Server ...
Looking at the log file I found out the error: BACKUP LOG permission denied in database 'database_name' ...
So I set Backup log permission using the Enterprise Manager to my database. But the error remains.
What's wrong and how to fix it?
ThanksCont.

I set Backup log permission for my database locally, not the database_name that is returning an error. (stupid isn't?)
I need the command line to set permission for the backup log for database_name (using the query analyser), because I'm unable to use Enterprise Manager.
Thanks

Backup From ISP?

I am just starting to use MSSQL. I've used Access a while ago and for
the last 4 years or so have been using FirebirdSQL pretty heavily.
I have a MSSQL database hosted with our ASP.net application. I am
wondering how I can backup my database using Enterprise Manager. I can
connect and alter tables, etc. I've seen the backup tool, but am
having a bit of trouble figuring it out.
Dumb, I know.
Thanks,
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."
Hi Lee,
From EnterPrise Manager right click your database, All Tasks, Backup
Database
Alternativly look at BACKUP DATABASE command.
I'm surprised an ISP gives you EnterPrise Manager, mine certainly doesn't.
Who do you use ...?
HTH. Ryan
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:uTe6tYTEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>
> I am just starting to use MSSQL. I've used Access a while ago and for
> the last 4 years or so have been using FirebirdSQL pretty heavily.
> I have a MSSQL database hosted with our ASP.net application. I am
> wondering how I can backup my database using Enterprise Manager. I can
> connect and alter tables, etc. I've seen the backup tool, but am
> having a bit of trouble figuring it out.
> Dumb, I know.
> Thanks,
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."
|||Ryan enlightened me by writing:

> Hi Lee,
> From EnterPrise Manager right click your database, All Tasks, Backup
> Database
> Alternativly look at BACKUP DATABASE command.
> I'm surprised an ISP gives you EnterPrise Manager, mine certainly
> doesn't. Who do you use ...?
Hi Ryan, I'm using http://www.crystaltech.com/
Thanks for the info.
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."
|||www.webhost4life.com gives my EM privileges to my data.
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eJ$LgjTEGHA.1736@.TK2MSFTNGP14.phx.gbl...
> Hi Lee,
> From EnterPrise Manager right click your database, All Tasks, Backup
> Database
> Alternativly look at BACKUP DATABASE command.
> I'm surprised an ISP gives you EnterPrise Manager, mine certainly doesn't.
> Who do you use ...?
> --
> HTH. Ryan
>
> "Lee" <luv2program2000@.yahoo.com> wrote in message
> news:uTe6tYTEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>

Backup From ISP?

I am just starting to use MSSQL. I've used Access a while ago and for
the last 4 years or so have been using FirebirdSQL pretty heavily.
I have a MSSQL database hosted with our ASP.net application. I am
wondering how I can backup my database using Enterprise Manager. I can
connect and alter tables, etc. I've seen the backup tool, but am
having a bit of trouble figuring it out.
Dumb, I know.
Thanks,
--
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."Hi Lee,
From EnterPrise Manager right click your database, All Tasks, Backup
Database
Alternativly look at BACKUP DATABASE command.
I'm surprised an ISP gives you EnterPrise Manager, mine certainly doesn't.
Who do you use ...?
--
HTH. Ryan
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:uTe6tYTEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>
> I am just starting to use MSSQL. I've used Access a while ago and for
> the last 4 years or so have been using FirebirdSQL pretty heavily.
> I have a MSSQL database hosted with our ASP.net application. I am
> wondering how I can backup my database using Enterprise Manager. I can
> connect and alter tables, etc. I've seen the backup tool, but am
> having a bit of trouble figuring it out.
> Dumb, I know.
> Thanks,
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."|||Ryan enlightened me by writing:
> Hi Lee,
> From EnterPrise Manager right click your database, All Tasks, Backup
> Database
> Alternativly look at BACKUP DATABASE command.
> I'm surprised an ISP gives you EnterPrise Manager, mine certainly
> doesn't. Who do you use ...?
Hi Ryan, I'm using http://www.crystaltech.com/
Thanks for the info.
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."|||www.webhost4life.com gives my EM privileges to my data.
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eJ$LgjTEGHA.1736@.TK2MSFTNGP14.phx.gbl...
> Hi Lee,
> From EnterPrise Manager right click your database, All Tasks, Backup
> Database
> Alternativly look at BACKUP DATABASE command.
> I'm surprised an ISP gives you EnterPrise Manager, mine certainly doesn't.
> Who do you use ...?
> --
> HTH. Ryan
>
> "Lee" <luv2program2000@.yahoo.com> wrote in message
> news:uTe6tYTEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>>
>> I am just starting to use MSSQL. I've used Access a while ago and for
>> the last 4 years or so have been using FirebirdSQL pretty heavily.
>> I have a MSSQL database hosted with our ASP.net application. I am
>> wondering how I can backup my database using Enterprise Manager. I can
>> connect and alter tables, etc. I've seen the backup tool, but am
>> having a bit of trouble figuring it out.
>> Dumb, I know.
>> Thanks,
>> --
>> Warm Regards,
>> Lee
>> "Upon further investigation it appears that your software is missing
>> just one thing. It definitely needs more cow bell..."
>

Backup From ISP?

I am just starting to use MSSQL. I've used Access a while ago and for
the last 4 years or so have been using FirebirdSQL pretty heavily.
I have a MSSQL database hosted with our ASP.net application. I am
wondering how I can backup my database using Enterprise Manager. I can
connect and alter tables, etc. I've seen the backup tool, but am
having a bit of trouble figuring it out.
Dumb, I know.
Thanks,
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."Hi Lee,
From EnterPrise Manager right click your database, All Tasks, Backup
Database
Alternativly look at BACKUP DATABASE command.
I'm surprised an ISP gives you EnterPrise Manager, mine certainly doesn't.
Who do you use ...?
HTH. Ryan
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:uTe6tYTEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>
> I am just starting to use MSSQL. I've used Access a while ago and for
> the last 4 years or so have been using FirebirdSQL pretty heavily.
> I have a MSSQL database hosted with our ASP.net application. I am
> wondering how I can backup my database using Enterprise Manager. I can
> connect and alter tables, etc. I've seen the backup tool, but am
> having a bit of trouble figuring it out.
> Dumb, I know.
> Thanks,
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."|||Ryan enlightened me by writing:

> Hi Lee,
> From EnterPrise Manager right click your database, All Tasks, Backup
> Database
> Alternativly look at BACKUP DATABASE command.
> I'm surprised an ISP gives you EnterPrise Manager, mine certainly
> doesn't. Who do you use ...?
Hi Ryan, I'm using http://www.crystaltech.com/
Thanks for the info.
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."|||www.webhost4life.com gives my EM privileges to my data.
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eJ$LgjTEGHA.1736@.TK2MSFTNGP14.phx.gbl...
> Hi Lee,
> From EnterPrise Manager right click your database, All Tasks, Backup
> Database
> Alternativly look at BACKUP DATABASE command.
> I'm surprised an ISP gives you EnterPrise Manager, mine certainly doesn't.
> Who do you use ...?
> --
> HTH. Ryan
>
> "Lee" <luv2program2000@.yahoo.com> wrote in message
> news:uTe6tYTEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>