Backup File Name

Dear all,

On scheduling the online backup for SQL server 2005 with SQL server management studio, how the backup filename can be automatically change for everyday. I mean the backup filename be automatically generated every day.

Thanks

Imran Hasware

You can take help of database maintenance plan in this case, where you can automatically set the filename to change.

Or else refer to the Tara's weblog http://weblogs.sqlteam.com/tarad/ where she got very useful script that can used without any issues.

|||

Dear Satya,

I tried Maintenance plan, but i am getting the following error:

Creating maintenance plan "SAP_Backup_Full" (Error)

Messages

Create maintenance plan failed.

ADDITIONAL INFORMATION:

Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The specified '@.subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476

When I checked the Link, No information is described there. Can you please suggest me.

Thank

Imran Hasware

backup error 17055 description 3041

I am getting this error when trying to bakup a database to a file. I am using the default filename and location. The error message is:
2004-02-29 01:00:00.79 backup BACKUP failed to complete the command BACKUP DATABASE [afdb000001_beSQL] TO DISK = N'd:\SQL Data\MSSQL\BACKUP\fordev.bak' WITH NOINIT , NOUNLOAD , NAME = N'afdb000001_beSQL backup', NOSKIP , STATS = 10, NOFORMAT
Any help would be appreciated.
Chris,
A couple of questions.
1) Do you have enough space on the D drive?
2) What is the D drive? Local SCSI, network drive, CD-ROM, SAN?
This error may occur sometimes when backing up over the network when there are network infrastructure issues.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Did you ever get this resolved?
I am wondering what you did?

backup error 17055 description 3041

I am getting this error when trying to bakup a database to a file. I am usin
g the default filename and location. The error message is:
2004-02-29 01:00:00.79 backup BACKUP failed to complete the command BACKU
P DATABASE [afdb000001_beSQL] TO DISK = N'd:\SQL Data\MSSQL\BACKUP\ford
ev.bak' WITH NOINIT , NOUNLOAD , NAME = N'afdb000001_beSQL backup', NOSK
IP , STATS = 10, NOFORMAT
Any help would be appreciated.Chris,
A couple of questions.
1) Do you have enough space on the D drive?
2) What is the D drive? Local SCSI, network drive, CD-ROM, SAN?
This error may occur sometimes when backing up over the network when there a
re network infrastructure issues.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Did you ever get this resolved?
I am wondering what you did?

Backup Database hangs

SQL Server 2000

Backup database command hangs. The command I am using is:

BACKUP DATABASE @.DBName TO DISK = @.filename WITH INIT

Size of the database is 140GB and we have 210 GB free disk space.
sp_who status shows runnable.

Any help would be appreciated.

Thanks.How do you execute the command (analyzer?) and what do you mean when you say "Backup database command hangs."? How did you find out it hangs?|||Command is execeuted thru SQL Server Agent as..

BACKUP DATABASE [Assentor] TO DISK = 'D:\DB_BAK\AssentorDB2.BAK' WITH INIT , NOUNLOAD , NAME = N'Assentor backup', SKIP , STATS = 10, DESCRIPTION = 'Daily Assentor DB Backup', FORMAT , MEDIANAME = N'Assentor DB Backup', MEDIADESCRIPTION = N'Assentor DB Backup'

Generally it takes 2 hrs. to complete the backup, but since last few days it just hangs and sits there for hours. I tried to kill the job, it shows killed/rollback and takes hours to release the spid.

Thanks.|||Any ideas? Last backup job which kicked of at 12:00 noon is still in runnable state. I can provide more info. if someone needs to help me out.

Thanks|||Find the spid of the backup process, and see if anything is blocking the backup. If nothing is blocking the backup, see if the CPU and PHYSICAL_IO numbers are going up. That would indicate that the process is running, if slowly. If he numbers are not going up, check the waittype, waitresource, and lastwaittype columns for any resource contention problems.|||Four options (that I know of):

1. Create multiple filegroups and start backing up on a filegroup level;
2. Move to a third-party backup solution (SQLLiteSpeed)
3. Move to SAN
4. Combination of the three above|||When the spid appears to hang, substitute the spid for the ?? then run:SELECT cpu, physical_io FROM master.dbo.sysprocesses WHERE ?? = spid
WAITFOR DELAY '1:00'
SELECT cpu, physical_io FROM master.dbo.sysprocesses WHERE ?? = spidIf the numbers change (at all), then the spid is still running.

If the numbers don't change, you need to check the disk to see if the backup is growing. Sometimes the spid doing the backup can hang for a long time if the database dump is undergoing a "grow" operation.

-PatP|||Is it 1 hour or 1 minute?|||That should be only one minute, but maybe I goofed. I don't have any easy way to test it at the moment.

If the counters don't change for a minute, then I'd be willing to consider the task "hung" for most purposes.

-PatP|||The whole problem is the backup file is not created at all in this whole process. OR let me put it this way, IF a backup file already exists, ABCbackup.bak with date and time stamp of 10/19/2004 7:30 AM, IT SHOULD CHANGE as soon as I start another backup on 10/20/2004 7:30 AM and the size should start growing. BUT it doesn't happend that way.

Now that I killed the backup job, it shows KILLED/ROLLBACK state for almost 14 hrs. and is blocking the new backup job which kicked of today morning at 7:30 AM.

Thanks.|||The whole problem is the backup file is not created at all in this whole process. OR let me put it this way, IF a backup file already exists, ABCbackup.bak with date and time stamp of 10/19/2004 7:30 AM, IT SHOULD CHANGE as soon as I start another backup on 10/20/2004 7:30 AM and the size should start growing. BUT it doesn't happend that way.

Now that I killed the backup job, it shows KILLED/ROLLBACK state for almost 14 hrs. and is blocking the new backup job which kicked of today morning at 7:30 AM.

80 0 runnable ASSENTOR\MSSQL_Srv_Agent NYC-SRA-02 0 Assentor KILLED/ROLLBACK
80 3 runnable ASSENTOR\MSSQL_Srv_Agent NYC-SRA-02 0 NULL KILLED/ROLLBACK

Thanks.|||I opened a case with Microsoft. Will keep you updated on this issue...

Backup - filename

Heillo
I want to get a timestamp in my filename when I take a backup
I have tried the following syntax
=== S T A R T ==
BACKUP DATABASE databas
TO DISK
SELECT 'C:\Backup\database\
+ (CONVERT(char(4), Year(GetDate()))
+ (CONVERT(char(2), Month(GetDate()))
+ (CONVERT(char(2), Day(GetDate()))
+ (CONVERT(char(2), DATEPART(hh, GetDate()))
+ (CONVERT(char(2), DATEPART(hh, GetDate()))
+ '.bak
=== S T A R T ==
When I try to run this I get Incorrect syntax near the keyword 'SELECT'
Anyone who have an idea how to automatic get date and time in the filename
Please reply to may E-mail address so that I don't need to read the news for reply ;-
regard
JohnTry this ir works from Query Anyliser!
declare @.sFile varchar(150);
set @.sFile = 'p:\desktop\test'
+ (CONVERT(char(4), Year(GetDate())))
+ (CONVERT(char(2), Month(GetDate())))
+ (CONVERT(char(2), Day(GetDate())))
+ (CONVERT(char(2), DATEPART(hh, GetDate())))
+ (CONVERT(char(2), DATEPART(hh, GetDate())))
+ '.bak'
BACKUP DATABASE test TO DISK = @.sFile
Cheers!
"John A. Horve" <John.Horve@.kristiansand.kommune.no> wrote in message
news:1EF8E895-AEA1-4DC2-B6EE-57535614C349@.microsoft.com...
> Heillo,
> I want to get a timestamp in my filename when I take a backup.
> I have tried the following syntax:
> === S T A R T ===> BACKUP DATABASE database
> TO DISK => SELECT 'C:\Backup\database\'
> + (CONVERT(char(4), Year(GetDate())))
> + (CONVERT(char(2), Month(GetDate())))
> + (CONVERT(char(2), Day(GetDate())))
> + (CONVERT(char(2), DATEPART(hh, GetDate())))
> + (CONVERT(char(2), DATEPART(hh, GetDate())))
> + '.bak'
> === S T A R T ===> When I try to run this I get Incorrect syntax near the keyword 'SELECT'.
> Anyone who have an idea how to automatic get date and time in the
filename?
> Please reply to may E-mail address so that I don't need to read the news
for reply ;-)
> regards
> John