SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup Stored Proc


Backup Stored Proc

Author
Message
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4818 Visits: 940
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

thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4818 Visits: 940
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

Attachments
usp_DBBackup.txt (25 views, 10.00 KB)
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4818 Visits: 940
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

Attachments
usp_DBBackup2.txt (27 views, 14.00 KB)
Ingvi Jón
Ingvi Jón
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1248
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
w00t

best regards Ingvi Jón
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4818 Visits: 940
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

thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4818 Visits: 940
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

Attachments
usp_DBBackup3.txt (40 views, 28.00 KB)
sqlservercentral123-1030929
sqlservercentral123-1030929
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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?
Linuxfreak
Linuxfreak
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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 Smile)

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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search