Dan,
The reason why no new files were generated is because you are using the WITH
TRUNCATE ONLY option, which just truncates the log without creating a backup
file for it. It is recommended you do not use this unless you don't want to
do a point-in-time restore which means you can only restore from full
backups, in which case just switch your database recovery option to Simple.
Below is a series of scripts that I wrote for you that basically replicate
the functionality of a simple maintenance plan (which I recommend using over
this method, but then again I don't know your situation).
Things to mention about these scripts, the top two scripts in each section
are printouts of the commands that are actually being called in the bottom
two commented-out scripts so you can see what it is that these commands are
doing. I made it so all the commands below are set to append to a file and
not to overwrite it. If you wish to make the backup overwrite the file, do a
search for NOINIT and replace it with INIT and that should do it for you.
These scripts are also checking which type of recovery model the database is
using in the transaction backup script as you can't perform transaction log
backups on databases in SIMPLE mode. The Backup DB script also doesn't
backup the tempdb as this generates errors. I hope this helps, but again I
don't know why you wouldn't just create a maintenance plan for all of this.
NOTE: each one of these lines starts with an "exec" or a "--" so be sure to
remove any extraneous lines that the list inserts into this.
-- Different Files for every minute w/ Append
exec sp_MSforEachDb 'IF DB_NAME((SELECT dbid FROM sysdatabases WHERE [Name] =
''?'')) <> ''tempdb'' PRINT ''BACKUP DATABASE [?] TO DISK =
N''''d:\mssqldata\MSSQL\BACKUP\?\?_''+LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VA
RCHAR, GETDATE(), 120), '' '', ''''), ''-'', ''''), '':'',
''''),12)+''_Backup.bak'''' WITH NOFORMAT, NOINIT, NAME = N''''?-Full
Database Backup'''', SKIP, NOREWIND, NOUNLOAD'''
exec sp_MSforeachDB 'IF DATABASEPROPERTYEX(''?'', ''RECOVERY'') <> ''SIMPLE''
PRINT ''BACKUP LOG [?] TO DISK =
N''''d:\mssqldata\MSSQL\BACKUP\?\?_''+LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VA
RCHAR, GETDATE(), 120), '' '', ''''), ''-'', ''''), '':'',
''''),12)+''_TransLog.trn'''' WITH NOFORMAT, INIT, NAME = N''''?-Transaction
Log Backup'''', SKIP, NOREWIND, NOUNLOAD'' ELSE PRINT ''BACKUP LOG [?] WITH
TRUNCATE_ONLY'''
--exec sp_MSforEachDb 'IF DB_NAME((SELECT dbid FROM sysdatabases WHERE [Name]
= ''?'')) <> ''tempdb'' BACKUP DATABASE [?] TO DISK =
N''d:\mssqldata\MSSQL\BACKUP\?\?_''+LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARC
HAR, GETDATE(), 120), '' '', ''''), ''-'', ''''), '':'',
''''),12)+''_Backup.bak'' WITH NOFORMAT, NOINIT, NAME = N''?-Full Database
Backup'', SKIP, NOREWIND, NOUNLOAD'
--exec sp_MSforeachDB 'IF DATABASEPROPERTYEX(''?'', ''RECOVERY'') <>
''SIMPLE'' BACKUP LOG [?] TO DISK =
N''d:\mssqldata\MSSQL\BACKUP\?\?_''+LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARC
HAR, GETDATE(), 120), '' '', ''''), ''-'', ''''), '':'',
''''),12)+''_TransLog.trn'' WITH NOFORMAT, INIT, NAME = N''?-Transaction Log
Backup'', SKIP, NOREWIND, NOUNLOAD ELSE BACKUP LOG [?] WITH TRUNCATE_ONLY'
-- Different Files for every database w/ Append
exec sp_MSforEachDb 'IF DB_NAME((SELECT dbid FROM sysdatabases WHERE [Name] =
''?'')) <> ''tempdb'' PRINT ''BACKUP DATABASE [?] TO DISK =
N''''d:\mssqldata\MSSQL\BACKUP\?\?_Backup.bak'''' WITH NOFORMAT, NOINIT,
NAME = N''''?-Full Database Backup'''', SKIP, NOREWIND, NOUNLOAD'''
exec sp_MSforeachDB 'IF DATABASEPROPERTYEX(''?'', ''RECOVERY'') <> ''SIMPLE''
PRINT ''BACKUP LOG [?] TO DISK =
N''''d:\mssqldata\MSSQL\BACKUP\?\?_TransLog.trn'''' WITH NOFORMAT, INIT,
NAME = N''''?-Transaction Log Backup'''', SKIP, NOREWIND, NOUNLOAD'' ELSE
PRINT ''BACKUP LOG [?] WITH TRUNCATE_ONLY'''
--exec sp_MSforEachDb 'IF DB_NAME((SELECT dbid FROM sysdatabases WHERE [Name]
= ''?'')) <> ''tempdb'' BACKUP DATABASE [?] TO DISK =
N''d:\mssqldata\MSSQL\BACKUP\?\?_Backup.bak'' WITH NOFORMAT, NOINIT, NAME =
N''?-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD'
--exec sp_MSforeachDB 'IF DATABASEPROPERTYEX(''?'', ''RECOVERY'') <>
''SIMPLE'' BACKUP LOG [?] TO DISK =
N''d:\mssqldata\MSSQL\BACKUP\?\?_TransLog.trn'' WITH NOFORMAT, INIT, NAME =
N''?-Transaction Log Backup'', SKIP, NOREWIND, NOUNLOAD ELSE BACKUP LOG [?]
WITH TRUNCATE_ONLY'
-- Same File for every database w/ Append
exec sp_MSforEachDb 'IF DB_NAME((SELECT dbid FROM sysdatabases WHERE [Name] =
''?'')) <> ''tempdb'' PRINT ''BACKUP DATABASE [?] TO DISK =
N''''d:\mssqldata\MSSQL\BACKUP\Backup.bak'''' WITH NOFORMAT, NOINIT, NAME =
N''''?-Full Database Backup'''', SKIP, NOREWIND, NOUNLOAD'''
exec sp_MSforeachDB 'IF DATABASEPROPERTYEX(''?'', ''RECOVERY'') <> ''SIMPLE''
PRINT ''BACKUP LOG [?] TO DISK =
N''''d:\mssqldata\MSSQL\BACKUP\TransLog.trn'''' WITH NOFORMAT, INIT, NAME =
N''''?-Transaction Log Backup'''', SKIP, NOREWIND, NOUNLOAD'' ELSE PRINT
''BACKUP LOG [?] WITH TRUNCATE_ONLY'''
--exec sp_MSforEachDb 'IF DB_NAME((SELECT dbid FROM sysdatabases WHERE [Name]
= ''?'')) <> ''tempdb'' BACKUP DATABASE [?] TO DISK =
N''d:\mssqldata\MSSQL\BACKUP\Backup.bak'' WITH NOFORMAT, NOINIT, NAME =
N''?-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD'
--exec sp_MSforeachDB 'IF DATABASEPROPERTYEX(''?'', ''RECOVERY'') <>
''SIMPLE'' BACKUP LOG [?] TO DISK =
N''d:\mssqldata\MSSQL\BACKUP\TransLog.trn'' WITH NOFORMAT, INIT, NAME =
N''?-Transaction Log Backup'', SKIP, NOREWIND, NOUNLOAD ELSE BACKUP LOG [?]
WITH TRUNCATE_ONLY'
HTH,
John Eisbrener
SQL Database Administrator
Capitol Insurance Companies
-----Original Message-----
From: Dan Guzman
[mailto:mssqldba-ezmlmshield-x58570133.[Email address protected]
Sent: Thursday, October 25, 2007 10:42 AM
To: LazyDBA Discussion
Subject: Re: SP_MSforeachdb question
Well, the command completes successfully, but nothing happened. There
are no new or modified files in d:\mssqldata\MSSQL\BACKUP\?_Backup.bak
or d:\mssqldata\MSSQL\BACKUP\[?]_Backup.bak
I am using multiple quotes to qualify the string, maybe I have over quoted.
exec sp_MSforeachdb 'BACKUP LOG [?] TO DISK
=N''d:\mssqldata\MSSQL\BACKUP\[?]_Backup.bak'' WITH TRUNCATE_ONLY, NAME
=N''[?]-Transaction Log Backup'', NOINIT'
Dan
Eisbrener John wrote:
> Try putting square brackets around the ? in the Backup Log statement.
>
> exec sp_MSforeachdb 'BACKUP LOG [?] TO DISK...
>
> John Eisbrener
> SQL Database Administrator
> Capitol Insurance Companies
>
> -----Original Message-----
> From: Dan Guzman
> [mailto:mssqldba-ezmlmshield-x97594648.[Email address protected]
> Sent: Wednesday, October 24, 2007 5:10 PM
> To: LazyDBA Discussion
> Subject: Re: SP_MSforeachdb question
>
> I tried to run the below command through the sqlcmd utility and I get
> the following error:
>
> Msg 102, Level 15, State 1, Server ZOE, Line 1
> Incorrect syntax near '200305'
>
> One of my databases is names 200305, so I'm sure this has something to
> do with the question mark variable replacement in the sp_MSforeachdb
> command. How do I fix it?
>
> Dan
>
> Eisbrener John wrote:
> > Honestly, I don't know if it would append backup sets for different
> databases
> > to the same backup file or not. You could try it. If you want to
> run them
> > from a command line, throw this command into a batch file using isql
> or osql
> > (I forget which version is better for this).
> >
> > John Eisbrener
> > SQL Database Administrator
> > Capitol Insurance Companies
> >
> > -----Original Message-----
> > From: Dan Guzman
> > [mailto:mssqldba-ezmlmshield-x95501234.[Email address protected]
> > Sent: Wednesday, October 24, 2007 2:17 PM
> > To: LazyDBA Discussion
> > Subject: Re: SP_MSforeachdb question
> >
> > overwrite? I thought it would just append them, or is there a separate
> > append command? Anyway, thanks. So, with that in mind, how do I run
> > all that from the command line? I would like to run the following
> > command from the 'Post-Command' dialog of Backup Exec:
> >
> > exec sp_MSforeachdb 'BACKUP LOG ? TO DISK =
> > N'd:\mssqldata\MSSQL\BACKUP\?_Backup' WITH TRUNCATE_ONLY, NAME =
> > N'?-Transaction Log Backup''
> >
> > But this seems like it would be very complicated. Can DBCC do this?
> >
> > 'c:\program files\Microsoft SQL Server\MSSQL\90\tools\Bin\dbcc' 'exec
> > sp_MSforeachdb 'BACKUP LOG ? TO DISK =
> > N'd:\mssqldata\MSSQL\BACKUP\?_Backup' WITH TRUNCATE_ONLY, NAME =
> > N'?-Transaction Log Backup''
> >
> > Dan
> > Eisbrener John wrote:
> > > Don't forget to replace the other RLs as well, otherwise all of your
> > backups
> > > will overwrite that one file, and you'll end up with a backup of the
> > database
> > > with the highest id (or name if being executed on SQL 2000).
> > >
> > > exec sp_MSforeachdb 'BACKUP DATABASE ? TO DISK =
> > > N'd:\mssqldata\MSSQL\BACKUP\?_Backup' WITH NOFORMAT, NOINIT, NAME =
> > N'?_Full
> > > Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
> > >
> > > John Eisbrener
> > > SQL Database Administrator
> > > Capitol Insurance Companies
> > >
> > > -----Original Message-----
> > > From: Stephen Davey
> > > [mailto:mssqldba-ezmlmshield-x15583554.[Email address protected]
> > > Sent: Wednesday, October 24, 2007 1:41 PM
> > > To: LazyDBA Discussion
> > > Subject: Re: SP_MSforeachdb question
> > >
> > > Dan,
> > >
> > > Try
> > >
> > > exec sp_MSforeachdb 'BACKUP DATABASE ? TO DISK =
> > > N'd:\mssqldata\MSSQL\BACKUP\RL_Backup' WITH NOFORMAT, NOINIT,
> NAME
> > >
> > > = N'RL-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
> > >
> > > The question mark will be replaced by db name.
> > >
> > > S. Davey (10/24)
> > >
> > >
> > > MI DIT\Agency Services
> > > 7:30 A.M. - 4:00 P.M. ET
> > > 517.335.4237 (M- F CCC )
> > > 517.373.1349 (MDOT)
> > >
> > >
> > >
> > >>>> "Dan Guzman "
> > >>>>
> > > <mssqldba-ezmlmshield-x95045173.[Email address protected] 10/24/07
> > > 2:33 PM >>>
> > > I realize this sp won't do what I want it to, because it doesn't
> insert
> > > the name of the 'Eachdb' as it repeats. How do I do that?
> > > DB_Name()? Will that sort of thing work here?
> > >
> > > exec sp_MSforeachdb 'BACKUP DATABASE [Random_Lengths] TO DISK =
> > >
> > > N'd:\mssqldata\MSSQL\BACKUP\RL_Backup' WITH NOFORMAT, NOINIT,
> NAME
> > >
> > > = N'RL-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
> > >
> >
> >
>
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page