Automatic Restore

Hi,
I have a database .BAK file on disk that I want to resotre everynight.
RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
If I want to automate this everyday, how can I create yyyymmdd
variable?
Thanks,
hjThis should get you started:
DECLARE @.sql nvarchar(300), @.d char(8)
SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d + '.bak'''
SELECT @.sql
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a database .BAK file on disk that I want to resotre everynight.
> RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> If I want to automate this everyday, how can I create yyyymmdd
> variable?
> Thanks,
> hj
>|||Hitesh Joshi wrote:
> Hi,
> I have a database .BAK file on disk that I want to resotre everynight.
> RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> If I want to automate this everyday, how can I create yyyymmdd
> variable?
> Thanks,
> hj
>
Instead of assuming a given filename/format, why not just check to see
what the most recent backup was, and restore that? The following should
work for you, add whatever switches you need to the restore command:
DECLARE @.DBName VARCHAR(50)
DECLARE @.LastBackupFileName VARCHAR(255)
DECLARE @.Command VARCHAR(1024)
SELECT @.DBName = 'YourDBName'
SELECT TOP 1 @.LastBackupFileName = backupmediafamily.physical_device_name
FROM msdb.dbo.backupset backupset
INNER JOIN msdb.dbo.backupmediafamily backupmediafamily
ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE backupset.type = 'D'
AND backupset.database_name = @.DBName
ORDER BY backupset.backup_start_date DESC
SELECT @.Command = 'RESTORE DATABASE ' + RTRIM(@.DBName) + '_New FROM
DISK=''' + @.LastBackupFileName + ''''
EXEC (@.Command)|||Hi,
Thank you for the suggestion... it works awesome with file name like
db_name_yyyymmdd.bak but my files have name something like
db_name_yyyymmddhhmm.bak.
and last hhmm is not always same. is there a way I can use wildchars?
SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' +
@.d + '*.bak'''
I tried this but did not work :(
Thanks in advance.
Tibor Karaszi wrote:
> This should get you started:
> DECLARE @.sql nvarchar(300), @.d char(8)
> SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
> SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d + '.bak'''
> SELECT @.sql
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
> news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...
> >
> > Hi,
> >
> > I have a database .BAK file on disk that I want to resotre everynight.
> >
> > RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> > If I want to automate this everyday, how can I create yyyymmdd
> > variable?
> >
> > Thanks,
> > hj
> >|||This solution looks very interesting but I am getting bak'ed up db
files from a third server and I do not have access to msdb on that
server...
Tracy McKibben wrote:
> Hitesh Joshi wrote:
> > Hi,
> >
> > I have a database .BAK file on disk that I want to resotre everynight.
> >
> > RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> > If I want to automate this everyday, how can I create yyyymmdd
> > variable?
> >
> > Thanks,
> > hj
> >
>
> Instead of assuming a given filename/format, why not just check to see
> what the most recent backup was, and restore that? The following should
> work for you, add whatever switches you need to the restore command:
> DECLARE @.DBName VARCHAR(50)
> DECLARE @.LastBackupFileName VARCHAR(255)
> DECLARE @.Command VARCHAR(1024)
> SELECT @.DBName = 'YourDBName'
> SELECT TOP 1 @.LastBackupFileName = backupmediafamily.physical_device_name
> FROM msdb.dbo.backupset backupset
> INNER JOIN msdb.dbo.backupmediafamily backupmediafamily
> ON backupset.media_set_id = backupmediafamily.media_set_id
> WHERE backupset.type = 'D'
> AND backupset.database_name = @.DBName
> ORDER BY backupset.backup_start_date DESC
> SELECT @.Command = 'RESTORE DATABASE ' + RTRIM(@.DBName) + '_New FROM
> DISK=''' + @.LastBackupFileName + ''''
> EXEC (@.Command)|||Hitesh Joshi wrote:
> This solution looks very interesting but I am getting bak'ed up db
> files from a third server and I do not have access to msdb on that
> server...
>
Create a linked server on your restore server that points to the "third"
server you mentioned. Then query the backupset and backupmediafamily
tables using their fully-qualified names:
SERVERNAME.msdb.dbo.backupset
SERVERNAME.msdb.dbo.backupmediafamily|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:upB3v4VmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hitesh Joshi wrote:
> > This solution looks very interesting but I am getting bak'ed up db
> > files from a third server and I do not have access to msdb on that
> > server...
> >
> Create a linked server on your restore server that points to the "third"
> server you mentioned. Then query the backupset and backupmediafamily
> tables using their fully-qualified names:
> SERVERNAME.msdb.dbo.backupset
> SERVERNAME.msdb.dbo.backupmediafamily
>
BTW, this is what we used to do.
Though, if possible what works better if the source server is doing
transaction log backups is to do a full backup/restore and then restore only
the logs every N hours. This is quicker and can reduce your disaster
recovery time.
(google for log shipping for more info. It's possible to roll your own.)|||You can't do wildcards in the restore command. I suggest you go the path suggested by Tracy. Also
consider Log Shipping as suggested by Greg.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1151350714.948572.273570@.r2g2000cwb.googlegroups.com...
> Hi,
> Thank you for the suggestion... it works awesome with file name like
> db_name_yyyymmdd.bak but my files have name something like
> db_name_yyyymmddhhmm.bak.
> and last hhmm is not always same. is there a way I can use wildchars?
> SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' +
> @.d + '*.bak'''
> I tried this but did not work :(
> Thanks in advance.
> Tibor Karaszi wrote:
>> This should get you started:
>> DECLARE @.sql nvarchar(300), @.d char(8)
>> SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
>> SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d + '.bak'''
>> SELECT @.sql
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
>> news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...
>> >
>> > Hi,
>> >
>> > I have a database .BAK file on disk that I want to resotre everynight.
>> >
>> > RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
>> > If I want to automate this everyday, how can I create yyyymmdd
>> > variable?
>> >
>> > Thanks,
>> > hj
>> >
>

Automatic Backups

Hi:
Somebody knows how I can program a stored procedure for automatic backup to
disk in SQL Server 2005 checking the transaction log?
Thanks in advance
Ale :)In SQL Server Management Studio, Go to Management, Maintenance Plans, right
click the node and select New. Choose the "Back Up Database Task" option from
the toolbox. Configure the backup wizard as needed.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Ale" wrote:
> Hi:
> Somebody knows how I can program a stored procedure for automatic backup to
> disk in SQL Server 2005 checking the transaction log?
> Thanks in advance
> Ale :)|||ok, I checked this but I have one doubt: how can I check my log? if I want
that my backup init when my log is at 80 %, how can I send an automatic form
the backup process to work? it exists a manner for programming this in SQL
Server 2005? (like a cron in UNIX)
Thanks again
Ale
"AndyP" wrote:
> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
> click the node and select New. Choose the "Back Up Database Task" option from
> the toolbox. Configure the backup wizard as needed.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Ale" wrote:
> > Hi:
> > Somebody knows how I can program a stored procedure for automatic backup to
> > disk in SQL Server 2005 checking the transaction log?
> > Thanks in advance
> > Ale :)|||You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
> ok, I checked this but I have one doubt: how can I check my log? if I want
> that my backup init when my log is at 80 %, how can I send an automatic form
> the backup process to work? it exists a manner for programming this in SQL
> Server 2005? (like a cron in UNIX)
> Thanks again
> Ale
> "AndyP" wrote:
>> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
>> click the node and select New. Choose the "Back Up Database Task" option from
>> the toolbox. Configure the backup wizard as needed.
>>
>> --
>> AndyP,
>> Sr. Database Administrator,
>> MCDBA 2003
>>
>> "Ale" wrote:
>> > Hi:
>> > Somebody knows how I can program a stored procedure for automatic backup to
>> > disk in SQL Server 2005 checking the transaction log?
>> > Thanks in advance
>> > Ale :)|||Ok, I'll try it!
This Agent triggers when the condition is completed?
Thanks!
Ale :)
"Tibor Karaszi" wrote:
> You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
> your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
> > ok, I checked this but I have one doubt: how can I check my log? if I want
> > that my backup init when my log is at 80 %, how can I send an automatic form
> > the backup process to work? it exists a manner for programming this in SQL
> > Server 2005? (like a cron in UNIX)
> > Thanks again
> > Ale
> >
> > "AndyP" wrote:
> >
> >> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
> >> click the node and select New. Choose the "Back Up Database Task" option from
> >> the toolbox. Configure the backup wizard as needed.
> >>
> >>
> >> --
> >> AndyP,
> >> Sr. Database Administrator,
> >> MCDBA 2003
> >>
> >>
> >> "Ale" wrote:
> >>
> >> > Hi:
> >> > Somebody knows how I can program a stored procedure for automatic backup to
> >> > disk in SQL Server 2005 checking the transaction log?
> >> > Thanks in advance
> >> > Ale :)
>
>|||> This Agent triggers when the condition is completed?
When the condition is true, yes. For instance, you can for the counter "Percent log full" specify a
value "raises above" and 80. When that perf mon counter is > 80, the alert is fired.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...
> Ok, I'll try it!
> This Agent triggers when the condition is completed?
> Thanks!
> Ale :)
> "Tibor Karaszi" wrote:
>> You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
>> your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ale" <Ale@.discussions.microsoft.com> wrote in message
>> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
>> > ok, I checked this but I have one doubt: how can I check my log? if I want
>> > that my backup init when my log is at 80 %, how can I send an automatic form
>> > the backup process to work? it exists a manner for programming this in SQL
>> > Server 2005? (like a cron in UNIX)
>> > Thanks again
>> > Ale
>> >
>> > "AndyP" wrote:
>> >
>> >> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
>> >> click the node and select New. Choose the "Back Up Database Task" option from
>> >> the toolbox. Configure the backup wizard as needed.
>> >>
>> >>
>> >> --
>> >> AndyP,
>> >> Sr. Database Administrator,
>> >> MCDBA 2003
>> >>
>> >>
>> >> "Ale" wrote:
>> >>
>> >> > Hi:
>> >> > Somebody knows how I can program a stored procedure for automatic backup to
>> >> > disk in SQL Server 2005 checking the transaction log?
>> >> > Thanks in advance
>> >> > Ale :)
>>|||It´s working!
Thanks!!!
Ale
"Tibor Karaszi" wrote:
> > This Agent triggers when the condition is completed?
> When the condition is true, yes. For instance, you can for the counter "Percent log full" specify a
> value "raises above" and 80. When that perf mon counter is > 80, the alert is fired.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...
> > Ok, I'll try it!
> > This Agent triggers when the condition is completed?
> > Thanks!
> > Ale :)
> >
> > "Tibor Karaszi" wrote:
> >
> >> You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
> >> your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> >> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
> >> > ok, I checked this but I have one doubt: how can I check my log? if I want
> >> > that my backup init when my log is at 80 %, how can I send an automatic form
> >> > the backup process to work? it exists a manner for programming this in SQL
> >> > Server 2005? (like a cron in UNIX)
> >> > Thanks again
> >> > Ale
> >> >
> >> > "AndyP" wrote:
> >> >
> >> >> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
> >> >> click the node and select New. Choose the "Back Up Database Task" option from
> >> >> the toolbox. Configure the backup wizard as needed.
> >> >>
> >> >>
> >> >> --
> >> >> AndyP,
> >> >> Sr. Database Administrator,
> >> >> MCDBA 2003
> >> >>
> >> >>
> >> >> "Ale" wrote:
> >> >>
> >> >> > Hi:
> >> >> > Somebody knows how I can program a stored procedure for automatic backup to
> >> >> > disk in SQL Server 2005 checking the transaction log?
> >> >> > Thanks in advance
> >> >> > Ale :)
> >>
> >>
> >>
> .
>

autogrow history

Hi,
Is there a way to see when autogrow has been triggered for a particular database/tran log? Over the weekend, we ran out of disk space on a server that has multiple databases, and I'd like to find out which database's tran log was at fault.
Thanks,
SusanThis kind of error should be logged on SQL Server Logs.

Originally posted by dbadba
Hi,

Is there a way to see when autogrow has been triggered for a particular database/tran log? Over the weekend, we ran out of disk space on a server that has multiple databases, and I'd like to find out which database's tran log was at fault.

Thanks,
Susan|||Thanks for the reply!

There is an "out of space" error in the log, but I'm looking for a log (or alert, etc.) that would track each time autogrow was triggered, even when it didn't result in an error. Do you know where I can find that information?

Thanks,
Laura|||Laura,

As far as I know, there is no way to create an alert based on autogrow event, unless you fix a log size limit and then add an alert using "error 9002, severity 19 - The log file for database xxxx is full". I think there is even a template in SQL 2000 Alerts for that.
Another approach would be to define a trace based on transaction log autogrow event. You can use SQL Profiler for tracing; or it can generates the SQL script for you, and then you can include it in a stored procedure that can be scheduled to start at a specific time.

Hope it helps !

Originally posted by dbadba
Thanks for the reply!

There is an "out of space" error in the log, but I'm looking for a log (or alert, etc.) that would track each time autogrow was triggered, even when it didn't result in an error. Do you know where I can find that information?

Thanks,
Laura|||Thanks! I will check into both of the options you suggested.|||if you want to prevent this kind of error from happening you can also create an alert that is triggered when a log is more than a certain percentage full.|||Thank you very much for the suggestion! I appreciate it.|||UNtested but this could be run regularly to email you when the log is over 50% full

create procedure mylog @.dbname varchar(50)
as
declare @.logspace int
, @.wo_num varchar(30) , @.deleted_user varchar(255),@.emails varchar(255) , @.messages varchar(100),
@.subjecttext varchar(100)
truncate table tempdb..logspace
insert tempdb..logspace (dbname,logsize,percentused,status)
execute('dbcc perflog')
set @.logspace = (select percentused from tempdb..logspace where dbname = @.dbname)
print @.logspace
if @.logspace > 50
set @.messages = 'Log Space is over 50%'
set @.subjecttext = @.dbname + 'log is over 50% full'
set @.emails = 'joesmoe@.kokomo.com'
exec master..xp_sendmail @.recipients = @.emails , @.message = @.messages,@.subject = @.subjecttext
else
return

HTH