Auto-Grow and Shrink Via Code

Hey guys,
Does anybody know of a way to turn on the Auto-Grow feature for a
transaction log via T-SQL code? What we want to do is during a
scheduled job, turn the auto grow feature on then after the job is
finished, shrink the log, then cut auto grow back off?
Any thoughts? Thanks in advance!
Hunter
**************Please Post to Group so that all can benefit!Have you looked at the ALTER DATABASE topic in Books Online? Of particular
interest:
AUTO_SHRINK ON | OFF
If ON is specified, the database files are candidates for automatic periodic
shrinking.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Hunter" <bamared36054@.yahoo.com> wrote in message
news:2a0a8bc1.0312020836.3923452e@.posting.google.com...
> Hey guys,
> Does anybody know of a way to turn on the Auto-Grow feature for a
> transaction log via T-SQL code? What we want to do is during a
> scheduled job, turn the auto grow feature on then after the job is
> finished, shrink the log, then cut auto grow back off?
> Any thoughts? Thanks in advance!
> Hunter
> **************Please Post to Group so that all can benefit!|||Sorry, wrong section... 'grow' != 'shrink' Aaron! Look under filespec:
< filespec > ::=( NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = 'os_file_name' ]
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] )
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Auto Restore of Production to Developement Db on same Server

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

IS there an easier way.

rik

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

rik

|||

Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.

create table #tb(i int identity primary key, name nvarchar(80) null)

declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'

insert #tb(name)
exec xp_cmdshell @.sql

select * from #tb

|||

ok, this was what I was afraid of: having to always write sql code.

oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.

|||

If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver

Be sure to include a business case if you want it to have any merit.

|||

thanks

appreciate it.

rik

Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

IS there an easier way.

rik

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

rik

|||

Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.

create table #tb(i int identity primary key, name nvarchar(80) null)

declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'

insert #tb(name)
exec xp_cmdshell @.sql

select * from #tb

|||

ok, this was what I was afraid of: having to always write sql code.

oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.

|||

If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver

Be sure to include a business case if you want it to have any merit.

|||

thanks

appreciate it.

rik

Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

IS there an easier way.

rik

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

rik

|||

Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.

create table #tb(i int identity primary key, name nvarchar(80) null)

declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'

insert #tb(name)
exec xp_cmdshell @.sql

select * from #tb

|||

ok, this was what I was afraid of: having to always write sql code.

oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.

|||

If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver

Be sure to include a business case if you want it to have any merit.

|||

thanks

appreciate it.

rik

Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

IS there an easier way.

rik

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

rik

|||

Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.

create table #tb(i int identity primary key, name nvarchar(80) null)

declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'

insert #tb(name)
exec xp_cmdshell @.sql

select * from #tb

|||

ok, this was what I was afraid of: having to always write sql code.

oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.

|||

If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver

Be sure to include a business case if you want it to have any merit.

|||

thanks

appreciate it.

rik

Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

IS there an easier way.

rik

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

rik

|||

Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.

create table #tb(i int identity primary key, name nvarchar(80) null)

declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'

insert #tb(name)
exec xp_cmdshell @.sql

select * from #tb

|||

ok, this was what I was afraid of: having to always write sql code.

oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.

|||

If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver

Be sure to include a business case if you want it to have any merit.

|||

thanks

appreciate it.

rik