backup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

backup

Author

Topic

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-09-12 : 17:46:36

Hi, can some one tell me how to go about doing a consice and
proper backup.

I have a database I created, but I also see other databases
that come with SQL server.

thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-12 : 19:10:30

Have a look at the backup database statement in bol
It's easiest to do a backup database nybb to disk = ...

You need to backup all databases except tempdb and maybe model. Don't worry about northwind if you have it.

You need to do a full backup - I would suggest any user databases every night and system databases at least weekly but you may as well do them daily as well.

If your recovery model is not simple you will have to do transaction log backups as well.

This is an SP that will handle it all for you
http://www.nigelrivett.net/BackupAllDatabases.html

Remember there is little point in taking backups unless you perform regular test restores on them.
For important databases I test restore and run dbcc checkdb on every full backup.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-09-12 : 20:36:15

Hi, thanks for the code. Is it possible to get a brief set of instructinos on how to implement it via enterprise manager?

I've never really used stored procedures. Is it just a matter of cutting and pasting this text?

thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-13 : 00:47:09

search for backup and restore in BOL, you have both t-sql and EM steps there.

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-09-13 : 22:29:30

Well, I put this code in as a store procedure. I am just a bit confused as to how I call it from my code.

For instance I uses something like

Dim sConnStr As String
Dim sMyQuery As String
Dim oConn As New ADODB.Connection
Dim oRs As New ADODB.Recordset

sConnStr = "UID=" & "sa" & ";PWD=" & "123" & ";driver=SQL Server;server=" & NameOfPC & ";database=TEST"

oConn.ConnectionString = sConnStr
oConn.Open

sMyQuery = "Bla bla bla"

oConn.Execute (sMyQuery)

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-09-13 : 22:45:01

Hi, okay, I think I got it to work using:

Set oRs = oConn.Execute("exec s_BackupAllDatabases C\test', 'Full'")

But, I have another question. In the code I am looking at, the
below is commented out. Should I add this table to my database?

/*
drop table DatabaseBackup

Create table DatabaseBackup
{
Name varchar(128) primary key nonclustered ,
BackupFlagFull varchar(1) not null check (BackupFlagFull in ('Y','N')) ,
BackupFlagLog varchar(1) not null check (BackupFlagLog in ('Y','N')) ,
RetentionPeriodFull datetime not null ,
RetentionPeriodLog datetime not null
)
*/

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-14 : 22:38:44

Yes.
It's so that you can change how long the backups are retained and remove databses from the backup.
When it's first run it will populate this table with all the databases and default values.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-15 : 07:38:18

Just for an optional script, or to learn more about the backups, you might want to take a look at Tara's scripts:

http://weblogs.sqlteam.com/tarad

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources