Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Backup Stored Proc Expand / Collapse
Author
Message
Posted Monday, August 18, 2008 2:51 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:05 PM
Points: 2,644, Visits: 824
Jonathan.Sims (8/12/2008)
Great SP Scott

But i do have one question, every so often my msdb databases is skipped with this backup, have you (or anyone) ran into this issue before?


I've replied to this privately, but for other forum users: no, I haven't come across this behaviour before.

Edit: But Jonathan & I are trying to find the cause of it.




Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Post #554612
Posted Monday, August 18, 2008 9:39 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:05 PM
Points: 2,644, Visits: 824
Here's a revised version of the script. Now handles long folder/file names, checks server version is 2000 or 2005.




Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare


  Post Attachments 
usp_DBBackup.txt (10 views, 10.46 KB)
Post #554724
Posted Wednesday, August 20, 2008 5:03 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:05 PM
Points: 2,644, Visits: 824
Another revision. SQL version checking (will only run on SQL 2000, 2005), better logging, additional options.

Set Database name to:

ALLSYS = backs up master, model, msdb
ALLUSER = backs up all (online) user databases (excludes master, model, msdb, tempdb)
ALLDATABASES = backs up all (online) databases (excludes tempdb)

or just specify an individual database name.




Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare


  Post Attachments 
usp_DBBackup2.txt (5 views, 14.02 KB)
Post #556164
Posted Tuesday, September 2, 2008 7:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 2:20 PM
Points: 8, Visits: 397
I tried to use this script and i found you that if the msdb..backupfile is large this scripts just hanges up.
it does the backup but never finishes deleting the old backuphistory


best regards Ingvi Jón
Post #562362
Posted Wednesday, September 3, 2008 5:44 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:05 PM
Points: 2,644, Visits: 824
This is a known issue with MSDB - there are some indexes missing that would improve performance. There is an article in the forums somewhere (search for "large msdb" and you should come across it) which boils down to this:

To improve performance of sp_delete_backuphistory (i.e., there are tens of thousands of rows in each of the backupset, restorefile & restorefilegroup tables), create the following indexes:

CREATE NONCLUSTERED INDEX ix_mediasetid ON backupset(media_set_id)
GO
CREATE NONCLUSTERED INDEX ix_restorehistoryid ON restorefile(restore_history_id)
GO
CREATE NONCLUSTERED INDEX ix_restorehistoryid2 ON restorefilegroup(restore_history_id)
GO

Creating the indexes above sped up the deletion process considerably - from ~60-100 rows per minute (speed improved as the tables got smaller) to 9600 rows per minute.

Run DBCC UPDATEUSAGE(0) WITH COUNT_ROWS to update table usage by counting every row.

Run sp_updatestats 'resample' when done.




Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Post #563492
Posted Monday, September 22, 2008 5:51 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:05 PM
Points: 2,644, Visits: 824
OK, a new version. This has tidied up a couple of things that were annoying me.

I have also added the ability to perform FILE or FILEGROUP backups. This only works if you specify a single database name. You can specify multiple files or filegroups. Supports FULL and DIFFERENTIAL backups for all.

I [believe I] have fully tested it, but there's always a chance I have missed something. If you have any problems, let me know.





Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare


  Post Attachments 
usp_DBBackup3.txt (21 views, 28.46 KB)
Post #573944
Posted Tuesday, March 3, 2009 9:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 10, 2009 12:34 PM
Points: 1, Visits: 15
Scott,

This is an awesome script. I use it on my SQL Server to backup our production DB full weekly, incremental nightly, and transaction log every 5 minutes.

However, do you have an analog to RESTORE the database? We have a reporting instance that we used to refresh nightly from production, but now that I don't do a full DB backup every night my existing SQL Server job can't run, so I'm forced to (for now) restore the incremental by hand every day.

I'm comfortable scripting another proc to be in sync (e.g. every Monday morning restore the full DB, every other day restore the incremental) but the part that I'm having trouble with is determining which incremental to pick up (since it's some sort of random number appended to the file name)...

In theory, I'd want to get the most recent full or incremental backup when running the restore, e.g.:
restore 'MYDB', 'FULL', 'P:\ath\to\backups', 'somedate' = null

if the date was entered, look for the most recent full on that date otherwise pick the most recent full in the dir...

Thoughts?
Post #667405
Posted Friday, May 1, 2009 7:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 4, 2009 1:22 AM
Points: 1, Visits: 3
I'm a total n00b to SQL, we received SQL2000 as database server backend with accounting software and frankly speaking, looking at the complexity of this stuff, it will not be my hobby :))

However, the need for a backup is there so i found this script. Is anyone running this on SQL 2000 SP4? I tried the script by starting Enterprise Manager, follow the path
Microsoft SQL Servers -> SQL Server Group -> (local) (Windows NT) -> Databases -> master -> Stored Procedures and then choosing new stored procedures. I copied the entire contents of the backup scripts version 3 a few posts up. I think i need to replace the NULL parameters with some of the things i needed to decide on. So i did that, when i click on OK i get a huge error message (so long it doesn't fit my screen) with all sorts of "Need to define" and then a variable name.

What am i doing wrong?
Post #708438
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse