Beigner Questions - 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 Administration (2000)

Beigner Questions

Author

Topic

jonjsilver2
Starting Member

14 Posts

Posted - 2010-09-24 : 14:22:02

OK, So I've analyzed our databases here and our recovery needs. The
databases are relatively small and most of the data orders are recoverable. I am going to take a fill backup each night and transaction logs a couple of times a day.

1. Is it better to use the BackiupMaintenancePlan or just a job with
some t-sql?

2. I see in BackiupMaintenancePlan, I can say ALL databases. Can I do that in t-sql too? or do I need one backup command per database?

3. Is there a difference between backing up the regular transaction log and the tail of the current one? If I sql BACKUP LOG doesn't that take the current transaction log with should still be active since the last BACKUP LOG was executed? In which case, why would I ever specify NO_TRUNCATE?

thanks
jon

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-24 : 14:31:16

1. Either way is fine, provided that they are monitored and tested. I never use maintenance plans. I wrote my own tools. A backup is only good if it can be restored. Make sure you perform test restores to prove that you can recover.

2. Yes you can. You can use my script for that and all of your database backup needs: http://weblogs.sqlteam.com/tarad/archive/2009/12/29/Backup-SQL-Server-2005-and-2008-DatabasesAgain.aspx

You do need one command per database. The maintenance plan will issue one command per database even if ALL is selected.

3. You should never specify NO_TRUNCATE! Also backup your tlog more often than a couple of times per day. Increase your recovery points. We backup our tlogs every 15 minutes. Why bother risking data loss when there is no problem with backing up the tlog frequently? 15 minutes of data loss is much better than 24 hours of data loss.

The tail of the backup is when you've encountered a problem and need to restore. Sometimes you can backup the tail and not lose any data. Sometimes you can't, so you restore as much as you can.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jonjsilver2
Starting Member

14 Posts

Posted - 2010-10-06 : 12:47:36

Thanks - this script looks great. However, I see that it references 2 stored procedures:
master.dbo.xp_backup_log and
master.dbo.sqlbackup

WHere do these come from? Is this supposed to be recursive? The name of this procedure is called: isp_Backup

thanks,
jon

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-06 : 12:56:19

No_truncate on a log backup is for when the mdf file is missing or damaged.

--
Gail Shaw
SQL Server MVP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-06 : 13:21:47

quote:
Originally posted by jonjsilver2

Thanks - this script looks great. However, I see that it references 2 stored procedures:
master.dbo.xp_backup_log and
master.dbo.sqlbackup

WHere do these come from? Is this supposed to be recursive? The name of this procedure is called: isp_Backup

thanks,
jon

One is for Quest's Litespeed product, and the other is for Red Gate's SQL Backup product. Ignore the warnings that you'll get when add the stored procedure to your system. SQL is throwing the warnings, but my code will specifically handle the situation when one or more options are not available.

They are not required. My code supports 4 different kinds of backups: native backup, native backup with compression, Litespeed backup, SQL Backup backup.

Yes it is recursive.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jonjsilver2
Starting Member

14 Posts

Posted - 2010-10-20 : 12:08:20

Really interesting problem using this stored procedure -

If I call this from sql server, it works great.

But if I execute the stored procedure from a vbscript,
It backs up only the system databases. It hangs on the user DB.

really strange to me. I added some logging to the stored procedure. Any ideas? This is repeatable.

LogFile ---
------------- called from vb script ---------
2010-10-20 10:27:48 S0 isp_Backup: Processing DB<master>
2010-10-20 10:27:49 S0 isp_Backup: Calling SQL<BACKUP DATABASE [master] TO DISK = 'C:\Backup\DB\master\master_20101020102749.BAK' WITH INIT>

2010-10-20 10:27:52 S0 isp_Backup: Processing DB<model>
2010-10-20 10:27:52 S0 isp_Backup: Calling SQL<BACKUP DATABASE [model] TO DISK = 'C:\Backup\DB\model\model_20101020102752.BAK' WITH INIT>

2010-10-20 10:27:55 S0 isp_Backup: Processing DB<msdb>
2010-10-20 10:27:55 S0 isp_Backup: Calling SQL<BACKUP DATABASE [msdb] TO DISK = 'C:\Backup\DB\msdb\msdb_20101020102755.BAK' WITH INIT>

2010-10-20 10:28:01 S0 isp_Backup: Processing DB<OrderExpress>
2010-10-20 10:28:01 S0 isp_Backup: Calling SQL<BACKUP DATABASE [OrderExpress] TO DISK = 'C:\Backup\DB\OrderExpress\OrderExpress_20101020102801.BAK' WITH INIT>
------ seems to hang right here --------

------- called from sql server -----------
2010-10-20 11:29:34 S0 isp_Backup: Processing DB<master>
2010-10-20 11:29:35 S0 isp_Backup: Calling SQL<BACKUP DATABASE [master] TO DISK = 'C:\Backup\DB\master\master_20101020112935.BAK' WITH INIT>

2010-10-20 11:29:38 S0 isp_Backup: Processing DB<model>
2010-10-20 11:29:38 S0 isp_Backup: Calling SQL<BACKUP DATABASE [model] TO DISK = 'C:\Backup\DB\model\model_20101020112938.BAK' WITH INIT>

2010-10-20 11:29:39 S0 isp_Backup: Processing DB<msdb>
2010-10-20 11:29:39 S0 isp_Backup: Calling SQL<BACKUP DATABASE [msdb] TO DISK = 'C:\Backup\DB\msdb\msdb_20101020112939.BAK' WITH INIT>

2010-10-20 11:29:48 S0 isp_Backup: Processing DB<OrderExpress>
2010-10-20 11:29:48 S0 isp_Backup: Calling SQL<BACKUP DATABASE [OrderExpress] TO DISK = 'C:\Backup\DB\OrderExpress\OrderExpress_20101020112948.BAK' WITH INIT>

2010-10-20 11:30:28 S0 isp_Backup: Processing DB<OrderExpressHistory>
2010-10-20 11:30:28 S0 isp_Backup: Calling SQL<BACKUP DATABASE [OrderExpressHistory] TO DISK = 'C:\Backup\DB\OrderExpressHistory\OrderExpressHistory_20101020113028.BAK' WITH INIT>

2010-10-20 11:31:25 S0 isp_Backup: Ending RC<0>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-20 : 12:20:01

If it works from SQL Server and not from VBScript, then the problem is with the VBScript code. Could you post your code? I'm certainly not a VBScript programmer, but I know enough of it to help out a tad.

Why do you need it to run via VBScript though? People typically schedule backups via the SQL Agent.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jonjsilver2
Starting Member

14 Posts

Posted - 2010-10-20 : 12:46:42

I put it in vbscript, because I wanted to combine this with an
FTP of the backup files off of the box.

Code just calls the store procedure via exec. Same call, I copied and pasted it.

Set cnn = CreateObject("ADODB.Connection")
cnn.Open OEConnectionString
If cnn.State = adStateOpen Then
ASNLog "Connected to Database " & DATADBASE
Else
ASNLog "Connection to <" & OEConnectionString & "> Failed"
End If

sSQL = "exec [master].[dbo].[isp_Backup] " _
& " @path = 'C:\Backup\DB\', @dbType = 'All', @bkpType = 'Full', " _
& " @retention = 5, @bkpSwType = 'NV', @archiveBit = 0, @copyOnly = 0"
ASNLog "Processing SQL <" & sSQL & ">"

cnn.Execute sSQL
ASNLog "Back from SQL cnn.Errors.Count <" & cnn.Errors.Count & ">"

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-20 : 14:39:24

permissions problem?

anyway, i'd still use a sql agent job.

- step 1, Tara's backup script (step type T-SQL Command)
- step 2, FTP script (step type Operating system command)

jonjsilver2
Starting Member

14 Posts

Posted - 2010-10-20 : 14:51:14

Don't think its a permission problem, but good point - a Two step
job would be better.

thanks :)

mrphantuan
Starting Member

5 Posts

Posted - 2010-11-18 : 04:45:04