Sql Backing up Tables: task

Showing posts with label task. Show all posts

Sunday, March 25, 2012

Backup error

I have this error:
SQL Server Scheduled Task: 43, 'BackupIBMDat - tempdb' --
Status: Failed
-- Task Invoked on: 1/21/04 1:30:00 AM -- Message: Can't
open dump device '\\.\TAPE0', device error or device off
line. Please consult the SQL Server error log for more
details. (Message 3201)
please help meso does SQL Server error log have anything regarding this?
see if you can run a backup to that \\TAPE0 device. Is the spelling right
for the device name?
"Whill" <anonymous@.discussions.microsoft.com> wrote in message
news:1c8d01c3e050$a5d94130$a001280a@.phx.gbl...
> I have this error:
> SQL Server Scheduled Task: 43, 'BackupIBMDat - tempdb' --
> Status: Failed
> -- Task Invoked on: 1/21/04 1:30:00 AM -- Message: Can't
> open dump device '\\.\TAPE0', device error or device off
> line. Please consult the SQL Server error log for more
> details. (Message 3201)
> please help me

Thursday, March 22, 2012

Backup error

quote:

> I have this error:
> SQL Server Scheduled Task: 43, 'BackupIBMDat - tempdb' --
> Status: Failed
> -- Task Invoked on: 1/21/04 1:30:00 AM -- Message: Can't
> open dump device '\\.\TAPE0', device error or device off
> line. Please consult the SQL Server error log for more
> details. (Message 3201)
> please help me

Tuesday, March 20, 2012

Back-up DB but keep the files for 3 days?

Hi
I have created a job to backup all our database. It works fine and creates .bak files in default folder. I have scheduled the task to run every 4 hours.

My question is how can i modify or program this backup plan to keep the backup files for only last 3 days and delete older backup files?

Mits

That's simple. I assume you are using SQL Server 2000.
If so, in Enterprise Manager, Expand Management node, select Database Maintenance plans.
Right click the maintenance plan and select properties.
Go to the complete backup tab.
Change the "Remove files older than" option to read 3 days and click ok.

Hope that Helps.

|||

A few questions for you to think about if you haven't already:

Are the backups stored on a different physical drive than the databases? If not then they absolutely should be.|||

Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.

This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.

I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.

|||

Matt Fraser wrote:

Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.

This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.

I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.

Matt,

I discovered this issue as well with SQL 2005, and I am perplexed as to why we have a way to back up databases but no way to manage the retention of old backups which can cause our disks to fill up. As a workaround I found this script on the web and use it to delete backup files older than "x" days, and it has worked flawlessly from the first time I started using it.

There are a few different ways to implement this workaround, but I have found the easiest way to is to copy the code below into a Notepad and save it as a .vbs file in the root of your backup drive, you might name the file something like DeleteOldBackups.vbs. This way you would simply create a Scheduled Task that points to this file and set the task to run either before or after all of your backups complete.

Make sure you set the directory paths and number of days to meet your needs.

Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld

'Delete database backup files older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\databackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files

'If database backup files are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next

'Delete database log file backups older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\logfilebackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files

'If database log file backups are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next

'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing

This has worked great for me, hopefully it will be useful to you and others as well.

|||Thank you everyone for you responses and i like the idea of script and i honestly agree with Matt Fraser.

I am using SQL Server 2005 Enterprise Edition.

I have had it working on SQL Server 2000 using a batch file using following command and this batch file was scheduled to run on sepecific time of the day. This will backup DB and Log files.

SQLMAINT -D DBName -BkUpDB E:\SQLBKUP -BkUpMedia DISK -DelBkUps 3days -VrfyBackup

The backup is taken on a shared Drive on the DB Server which will be backed up in the overall daily backup plan on tape. So everyday on tape, we will have the backup of last 3 days and we dont have to use hard disk space.

Regarding disaster recovery - we literally cant have any downtime from monday to saturday, but, couple of hours on Sunday could be arranged. Backup time is about 10-15 mins.

Yes I regularly restore the backups to check the validity.

I am quiet sure that this batch file will work for SQL Server 2005, but i not particularly keen on using batch file(and if i have to, i will use it). I want to do it through Maintenance Plan.

Mits

|||

I also use script to achieve this at my customers (WMI/VB Script). It's much more flexible and you can code it to do what you want. I've done a few custom versions, if, for example, they want to keep N number of fulls, and only a certain number of days worth of transaction logs, etc.

It works very well, but as you hint at Mits, it is outside the control of SQL Server.

|||

I did some digging and I found out that there is in fact a way to delete old backup files within the SQL management studio. I was told this functionality was going to be added with SP1 and apparently it was, just not where I expected to find it. I haven't had a chance to test it very much so I'm going to continue running my VB Script, but it is there.

Basically there are two ways to create backup jobs for your databases from within the SQL management studio Maintenence Plan area, manually or by using the wizard. If you choose to create your backup plans manually you will see the functionality to delete old backups right away, but here is how I found it because I always like to use the wizard instead.

In the SQL management studio under maintenence plans, right click maintenence plans and select the wizard. Follow the wizard all the way through to create your backup plan, you will not see any option to delete backup files older than "x" days, at the end save your backup plan.

Once your backup plan is visible under maintenence plans, right click it, and click on modify, this will open your backup plan in what appears to be a development environment window and you will see several boxes connected to each other with either green or blue arrows. Look down in the lower left hand corner and you will see a toolbox with about eight different components in it.

Select the component called Maintenence Cleanup History and drag it onto the page with your other boxes. Right click this new box and click edit, when it opens up you will see options to delete backup files older than x days. You can configure it, and then you will need to connect it to the other boxes and save the plan and then run it to test it. I tried it a couple of times and couldn't get it to work so I will have to keep testing it.

The ability to delete old backup files does exit, just not where I thought I would find it.

|||thank you very much andy
I try adding cleanup history step and see if i can get it working.

Mits
|||

Well done, Andy. It seems that SP1 did in fact add this functionality, but the Wizard hasn't been updated yet to include it. So, the key is to add the step for "Maintenance Cleanup Task," recognizing that it's different from "History Cleanup Task" (which is included in the Wizard).

I've added it to my maintenance plan, and in about a week's time I should know if it's working properly.

|||

This appears to work, but I won't be able to tell until tonight since Microsoft has removed the ability to delete files older than x hours. Only days, weeks, months, and years. I always used hours because of variations in backup duration. If a backup had the time of 8:01 one night and 8:00 the next, I would end up with both on the drive if I picked the 1 day option. I always used 23 hours to avoid the drive filling up. It appears that option is gone. Grrrrr! It is annoying to have a feature that you use and rely on only to have it removed.

Microsoft, please add back the hours option in a service pack.

|||

I think the frustration expressed in this thread is entirely justified and I'll ensure that the tools team here sees this.

As added weight, please go to connect.microsoft.com and give this feedback - this will open issues directly in our bug database and you will get feedback from the relevant teams.

Thanks

|||

Thanks for the suggestion! I didn't know about that site. I see a closed item where the response was that "hours" would come back as a duration in SP2. I voted for that and the open item about the same issue.

I also authored an item to make the cleanup task available through the wizard as it was in SQL2000.

Thanks for taking time to listen to our concerns and point me to the right forum for airing them. Your responsiveness does your employer proud.

|||how can I set the schedule of backup as follows using Maintenance Job.

I want to run the backup plan ever 285 mins or 4.75 hours starting at 8.15 in the morning and finishing at 9.15 at night.

I have tried but it wont let me put anything more then 60 mins or 4.75 hours.

Mits
|||

In sql2005 the retention period comes as common sql server setting.when check properties of sql server in database setting you have option to set retention period

Back-up DB but keep the files for 3 days?

Hi
I have created a job to backup all our database. It works fine and creates .bak files in default folder. I have scheduled the task to run every 4 hours.

My question is how can i modify or program this backup plan to keep the backup files for only last 3 days and delete older backup files?

Mits

Hope that Helps.

|||

A few questions for you to think about if you haven't already:

Are the backups stored on a different physical drive than the databases? If not then they absolutely should be.|||

I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.

|||

Matt Fraser wrote:

Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.

This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.

I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.

Matt,

Make sure you set the directory paths and number of days to meet your needs.

Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld

'If database backup files are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next

'If database log file backups are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next

'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing

This has worked great for me, hopefully it will be useful to you and others as well.

|||Thank you everyone for you responses and i like the idea of script and i honestly agree with Matt Fraser.

I am using SQL Server 2005 Enterprise Edition.

I have had it working on SQL Server 2000 using a batch file using following command and this batch file was scheduled to run on sepecific time of the day. This will backup DB and Log files.

SQLMAINT -D DBName -BkUpDB E:\SQLBKUP -BkUpMedia DISK -DelBkUps 3days -VrfyBackup

Regarding disaster recovery - we literally cant have any downtime from monday to saturday, but, couple of hours on Sunday could be arranged. Backup time is about 10-15 mins.

Yes I regularly restore the backups to check the validity.

I am quiet sure that this batch file will work for SQL Server 2005, but i not particularly keen on using batch file(and if i have to, i will use it). I want to do it through Maintenance Plan.

Mits

|||

It works very well, but as you hint at Mits, it is outside the control of SQL Server.

|||

The ability to delete old backup files does exit, just not where I thought I would find it.

|||thank you very much andy
I try adding cleanup history step and see if i can get it working.

Mits
|||

I've added it to my maintenance plan, and in about a week's time I should know if it's working properly.

|||

Microsoft, please add back the hours option in a service pack.

|||

I think the frustration expressed in this thread is entirely justified and I'll ensure that the tools team here sees this.

As added weight, please go to connect.microsoft.com and give this feedback - this will open issues directly in our bug database and you will get feedback from the relevant teams.

Thanks

|||

I also authored an item to make the cleanup task available through the wizard as it was in SQL2000.

Thanks for taking time to listen to our concerns and point me to the right forum for airing them. Your responsiveness does your employer proud.

|||how can I set the schedule of backup as follows using Maintenance Job.

I want to run the backup plan ever 285 mins or 4.75 hours starting at 8.15 in the morning and finishing at 9.15 at night.

I have tried but it wont let me put anything more then 60 mins or 4.75 hours.

Mits
|||

In sql2005 the retention period comes as common sql server setting.when check properties of sql server in database setting you have option to set retention period

sql

Back-up DB but keep the files for 3 days?

Hi
I

have created a job to backup all our database. It works fine and

creates .bak files in default folder. I have scheduled the task to run

every 4 hours.

My question is how can i modify or program this

backup plan to keep the backup files for only last 3 days and delete

older backup files?

Mits

Hope that Helps.

|||

A few questions for you to think about if you haven't already:

Are the backups stored on a different physical drive than the databases? If not then they absolutely should be.|||

I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.

|||

Matt Fraser wrote:

Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.

Matt,

Make sure you set the directory paths and number of days to meet your needs.

Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld

'If database backup files are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next

'If database log file backups are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next

'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing

This has worked great for me, hopefully it will be useful to you and others as well.

|||Thank you everyone for you responses and i like the idea of script and i honestly agree with Matt Fraser.

I am using SQL Server 2005 Enterprise Edition.

I have had it working on SQL Server 2000 using a batch file using following command and this batch file was scheduled to run on sepecific time of the day. This will backup DB and Log files.

SQLMAINT -D DBName -BkUpDB E:\SQLBKUP -BkUpMedia DISK -DelBkUps

3days -VrfyBackup

Regarding disaster recovery - we literally cant have any downtime from monday to saturday, but, couple of hours on Sunday could be arranged. Backup time is about 10-15 mins.

Yes I regularly restore the backups to check the validity.

I am quiet sure that this batch file will work for SQL Server 2005, but i not particularly keen on using batch file(and if i have to, i will use it). I want to do it through Maintenance Plan.

Mits|||

It works very well, but as you hint at Mits, it is outside the control of SQL Server.

|||

The ability to delete old backup files does exit, just not where I thought I would find it.

|||thank you very much andy
I try adding cleanup history step and see if i can get it working.

Mits|||

I've added it to my maintenance plan, and in about a week's time I should know if it's working properly.

|||

Microsoft, please add back the hours option in a service pack.

|||

I think the frustration expressed in this thread is entirely justified and I'll ensure that the tools team here sees this.

As added weight, please go to connect.microsoft.com and give this feedback - this will open issues directly in our bug database and you will get feedback from the relevant teams.

Thanks

|||

I also authored an item to make the cleanup task available through the wizard as it was in SQL2000.

Thanks for taking time to listen to our concerns and point me to the right forum for airing them. Your responsiveness does your employer proud.

|||how can I set the schedule of backup as follows using Maintenance Job.

I want to run the backup plan ever 285 mins or 4.75 hours starting at 8.15 in the morning and finishing at 9.15 at night.

I have tried but it wont let me put anything more then 60 mins or 4.75 hours.

Mits|||

In sql2005 the retention period comes as common sql server setting.when check properties of sql server in database setting you have option to set retention period

Back-up DB but keep the files for 3 days?

Hi
I

have created a job to backup all our database. It works fine and

creates .bak files in default folder. I have scheduled the task to run

every 4 hours.

My question is how can i modify or program this

backup plan to keep the backup files for only last 3 days and delete

older backup files?

Mits

Hope that Helps.

|||

A few questions for you to think about if you haven't already:

Are the backups stored on a different physical drive than the databases? If not then they absolutely should be.|||

I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.

|||

Matt Fraser wrote:

Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.

Matt,

Make sure you set the directory paths and number of days to meet your needs.

Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld

'If database backup files are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next

'If database log file backups are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next

'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing

This has worked great for me, hopefully it will be useful to you and others as well.

|||Thank you everyone for you responses and i like the idea of script and i honestly agree with Matt Fraser.

I am using SQL Server 2005 Enterprise Edition.

I have had it working on SQL Server 2000 using a batch file using following command and this batch file was scheduled to run on sepecific time of the day. This will backup DB and Log files.

SQLMAINT -D DBName -BkUpDB E:\SQLBKUP -BkUpMedia DISK -DelBkUps

3days -VrfyBackup

Regarding disaster recovery - we literally cant have any downtime from monday to saturday, but, couple of hours on Sunday could be arranged. Backup time is about 10-15 mins.

Yes I regularly restore the backups to check the validity.

I am quiet sure that this batch file will work for SQL Server 2005, but i not particularly keen on using batch file(and if i have to, i will use it). I want to do it through Maintenance Plan.

Mits|||

It works very well, but as you hint at Mits, it is outside the control of SQL Server.

|||

The ability to delete old backup files does exit, just not where I thought I would find it.

|||thank you very much andy
I try adding cleanup history step and see if i can get it working.

Mits|||

I've added it to my maintenance plan, and in about a week's time I should know if it's working properly.

|||

Microsoft, please add back the hours option in a service pack.

|||

I think the frustration expressed in this thread is entirely justified and I'll ensure that the tools team here sees this.

As added weight, please go to connect.microsoft.com and give this feedback - this will open issues directly in our bug database and you will get feedback from the relevant teams.

Thanks

|||

I also authored an item to make the cleanup task available through the wizard as it was in SQL2000.

Thanks for taking time to listen to our concerns and point me to the right forum for airing them. Your responsiveness does your employer proud.

|||how can I set the schedule of backup as follows using Maintenance Job.

I want to run the backup plan ever 285 mins or 4.75 hours starting at 8.15 in the morning and finishing at 9.15 at night.

I have tried but it wont let me put anything more then 60 mins or 4.75 hours.

Mits|||

In sql2005 the retention period comes as common sql server setting.when check properties of sql server in database setting you have option to set retention period

Monday, March 19, 2012

Backup Database using Files

I am but a lowly DBA unworthy of this task...

I have a large (200+ GB) database with many (100+) files. Please don't ask me why I did it this way; I inherited this database -- really, it wasn't my idea.

My predecessor also seemd to think that backups were unnecessary; there have been no backups of this database -- ever.

While we cast about for a good long term solution, I am trying various short-term options. One I want to explore is to back the database up in chunks -- ie, by backing up individual files. I created a test database with five files (there is only one filegroup on the production server). Here is the DDL:

-- =============================================
-- Create database on mulitple file groups
-- =============================================
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'MultiFile')
DROP DATABASE MultiFile
GO

CREATE DATABASE MultiFile
ON PRIMARY
( NAME = MultiFile,
FILENAME = N'e:\MSSQL\Data\MultiFile.mdf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = MultiFile2,
FILENAME = N'e:\MSSQL\Data\MultiFile2.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = MultiFile3,
FILENAME = N'e:\MSSQL\Data\MultiFile3.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = MultiFile4,
FILENAME = N'e:\MSSQL\Data\MultiFile4.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = MultiFile5,
FILENAME = N'e:\MSSQL\Data\MultiFile5.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)

LOG ON
( NAME = MultiFile_Log,
FILENAME = N'e:\MSSQL\Data\MultiFile_Log.ldf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
GO

I have tried the following backup script:

BACKUP DATABASE MultiFile
FILE = 'MultiFile',
FILE = 'MultiFile2'
TO Backup01
WITH
INIT

BACKUP DATABASE MultiFile
FILE = 'MultiFile3',
FILE = 'MultiFile4'
TO Backup02
WITH
INIT

BACKUP DATABASE MultiFile
FILE = 'MultiFile5'
TO Backup03
WITH
INIT

And here is the restore script:

RESTORE DATABASE MultiFile2
FILE = 'MultiFile',
FILE = 'MultiFile2',
FILE = 'MultiFile3',
FILE = 'MultiFile4',
FILE = 'MultiFile5'
FROM Backup01, Backup02, Backup03
WITH MOVE 'MultiFile' TO 'E:\MSSQL\Data\aMultfile.mdf',
MOVE 'MultiFile2' TO 'E:\MSSQL\Data\aMultifile2.mdf',
MOVE 'MultiFile2' TO 'E:\MSSQL\Data\aMultifile3.mdf',
MOVE 'MultiFile2' TO 'E:\MSSQL\Data\aMultifile4.mdf',
MOVE 'MultiFile2' TO 'E:\MSSQL\Data\aMultifile5.mdf',
MOVE 'MultiFile_log' TO 'E:\MSSQL\aMultFile_Log.ldf'

However, running the Restore script generates the following error:

Server: Msg 3259, Level 16, State 1, Line 1
The volume on device 'Backup02' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I'm not sure what to make of this. What do I need to alter in either the backup script or the restore script to make this work?

I am trying this because my objectives are to:
1. Limit the amount of work that the server is performing during any one given backup session. The idea that I have is to backup the database in chunks using a rolling 3-5 day window.
2. The database must be up and operational 7x24x365 (except for one 4 hour window each month)
3. This is not the long-term solution; but I need something to tide us over until we can purchase additional storage capacity.

I appreciate any thoughts and or guidance you can provide.

Regards,

hmscottYou may need something like
RESTORE DATABASE MultiFile2
'MultiFile',
'MultiFile2',
'MultiFile3',
'MultiFile4',
'MultiFile5'
FROM Backup01, Backup02, Backup03
WITH MOVE 'MultiFile' TO 'E:\MSSQL\Data\aMultfile.mdf',
MOVE 'MultiFile2' ...

Some cautions, though. You will need to keep all of your transaction logs, if you back up the files on separate nights. I have not tried to do a file by file restore, so I am not sure how easy it would be.|||You might consider a backup directly to tape... You can do a backup while the server is running with negligable impact, and it would allow you to relatively quickly and easily get a backup (or two) made and SENT OFF SITE before you have a cornary! You'll eat a couple of tapes, but that doesn't even rank as a HK at this point in time!

I don't know of any good way to backup part of a filegroup. It just isn't a good plan in my experience.

You could also BCP the tables out to flat files, and back those up. The down side to this approach is that there isn't any synchronization, so you'll never get a full backup made that you can really truly trust.

-PatP

Backup Database Task

Hi,

I am trying in the Back Up Database Task to set the Value of SelectedDatabases via Expression.
Is there any way?

Thanks,

Willfried

SQL Server 2005 SP 1 + Hotfix

You cannot set the SelectedDatabases property with an Expression. Try using an Execute SQL Task and an expression to set the SqlStatementSource

Backup Database Task

Hi,

I am trying in the Back Up Database Task to set the Value of SelectedDatabases via Expression.
Is there any way?

Thanks,

Willfried

SQL Server 2005 SP 1 + Hotfix

You cannot set the SelectedDatabases property with an Expression. Try using an Execute SQL Task and an expression to set the SqlStatementSource

Thursday, March 8, 2012

Backup claims to have run, but there's no file

I have been trying to figure out why my transaction log backup task doesn't
seem to do anything. I changed the user account to SA, and now I see in the
logs that the task did run (no more account errors).
When I View Job History on the task, I see that it ran last night at 5AM as
I expected, and there's no errors. But there's no file!
Any ideas?
Maury
That's not much to go on but is there any chance the db is in Simple
Recovery mode?
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:4E2A71B7-814A-4DC8-98B3-0974F1778827@.microsoft.com...
>I have been trying to figure out why my transaction log backup task doesn't
> seem to do anything. I changed the user account to SA, and now I see in
> the
> logs that the task did run (no more account errors).
> When I View Job History on the task, I see that it ran last night at 5AM
> as
> I expected, and there's no errors. But there's no file!
> Any ideas?
> Maury
|||"Andrew J. Kelly" wrote:
> That's not much to go on but is there any chance the db is in Simple
> Recovery mode?
I can't tell you, I'm running '97 and Enterprise Manager doesn't say. Was
this even an option in earlier versions?
It would certainly make sense that this could cause the problem, I never
thought of it likely because I never saw the option while poking about!
After a little googling I note that the SQL Server Agent appearently resets
the server to Simple (assuming my server has this). This might be the problem
right there.
Maury
|||That's SQL Server 7.0 of course, not '97
|||In 7.0 it would not be called Simple mode. It would have Truncate Log on
ChkPoint I believe.
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:5BF9ADF5-4465-43C6-B090-36BDF0322629@.microsoft.com...
> That's SQL Server 7.0 of course, not '97
|||"Andrew J. Kelly" wrote:
> In 7.0 it would not be called Simple mode. It would have Truncate Log on
> ChkPoint I believe.
That option I do see. So I will turn this off and keep my fingers crossed.
BTW, what is a checkpoint, and when does it occur?
Maury
|||Look up CHECKPOINT in BooksOnLine for details.
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14EEF517-A29E-40FA-983C-476E721FCD46@.microsoft.com...
> "Andrew J. Kelly" wrote:
> That option I do see. So I will turn this off and keep my fingers crossed.
> BTW, what is a checkpoint, and when does it occur?
> Maury
|||No luck so far...
I checked all of the log and error files (in /LOG), and don't see any
mention of a backup even running. I do, however, see an empty file for the
16th, the last time it ran.
I went into Enterprise Manager and after some poking about managed to find
the internal logs, the ones stored in the table. This did have an entry (so
why didn't the log file?!) for the 16th, which stated that "Backup can not be
performed on this database. This sub task is ignored", and said it
_succeeded_.
Either way, still no backup file!
This shouldn't be this confusing.
Maury
|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:28194502-6F72-4448-935B-E8DAE25CBCEA@.microsoft.com...
> No luck so far...
> I checked all of the log and error files (in /LOG), and don't see any
> mention of a backup even running. I do, however, see an empty file for the
> 16th, the last time it ran.
> I went into Enterprise Manager and after some poking about managed to find
> the internal logs, the ones stored in the table. This did have an entry
(so
> why didn't the log file?!) for the 16th, which stated that "Backup can not
be
> performed on this database. This sub task is ignored", and said it
> _succeeded_.
> Either way, still no backup file!
I missed it, is this a full or transaction log backup?
If it's a transaction log backup either your database may be in SIMPLE
recovery mode or a bulk insert has been done since the last full backup.

> This shouldn't be this confusing.
> Maury
|||"Greg D. Moore (Strider)" wrote:
> I missed it, is this a full or transaction log backup?
Transaction log.

> If it's a transaction log backup either your database may be in SIMPLE
> recovery mode or a bulk insert has been done since the last full backup.
There is no "simple" in 7.0, but I have turned off the confusingly-named
options that seem to imply transaction logs should now work.
The only bulk work we do is SELECT INTO on #temp tables. If THAT is the
cause of the problem, I can't imagine why -- bulks into temp tables should be
ignored for the purpose of transaction logging.
And if that is the cause, what is the solution? Diff backups prior to trans
backups?
Maury

Backup claims to have run, but there's no file

I have been trying to figure out why my transaction log backup task doesn't
seem to do anything. I changed the user account to SA, and now I see in the
logs that the task did run (no more account errors).
When I View Job History on the task, I see that it ran last night at 5AM as
I expected, and there's no errors. But there's no file!
Any ideas?
MauryThat's not much to go on but is there any chance the db is in Simple
Recovery mode?
--
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:4E2A71B7-814A-4DC8-98B3-0974F1778827@.microsoft.com...
>I have been trying to figure out why my transaction log backup task doesn't
> seem to do anything. I changed the user account to SA, and now I see in
> the
> logs that the task did run (no more account errors).
> When I View Job History on the task, I see that it ran last night at 5AM
> as
> I expected, and there's no errors. But there's no file!
> Any ideas?
> Maury|||"Andrew J. Kelly" wrote:
> That's not much to go on but is there any chance the db is in Simple
> Recovery mode?
I can't tell you, I'm running '97 and Enterprise Manager doesn't say. Was
this even an option in earlier versions?
It would certainly make sense that this could cause the problem, I never
thought of it likely because I never saw the option while poking about!
After a little googling I note that the SQL Server Agent appearently resets
the server to Simple (assuming my server has this). This might be the problem
right there.
Maury|||That's SQL Server 7.0 of course, not '97|||In 7.0 it would not be called Simple mode. It would have Truncate Log on
ChkPoint I believe.
--
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:5BF9ADF5-4465-43C6-B090-36BDF0322629@.microsoft.com...
> That's SQL Server 7.0 of course, not '97|||"Andrew J. Kelly" wrote:
> In 7.0 it would not be called Simple mode. It would have Truncate Log on
> ChkPoint I believe.
That option I do see. So I will turn this off and keep my fingers crossed.
BTW, what is a checkpoint, and when does it occur?
Maury|||Look up CHECKPOINT in BooksOnLine for details.
--
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14EEF517-A29E-40FA-983C-476E721FCD46@.microsoft.com...
> "Andrew J. Kelly" wrote:
>> In 7.0 it would not be called Simple mode. It would have Truncate Log on
>> ChkPoint I believe.
> That option I do see. So I will turn this off and keep my fingers crossed.
> BTW, what is a checkpoint, and when does it occur?
> Maury|||No luck so far...
I checked all of the log and error files (in /LOG), and don't see any
mention of a backup even running. I do, however, see an empty file for the
16th, the last time it ran.
I went into Enterprise Manager and after some poking about managed to find
the internal logs, the ones stored in the table. This did have an entry (so
why didn't the log file?!) for the 16th, which stated that "Backup can not be
performed on this database. This sub task is ignored", and said it
_succeeded_.
Either way, still no backup file!
This shouldn't be this confusing.
Maury|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:28194502-6F72-4448-935B-E8DAE25CBCEA@.microsoft.com...
> No luck so far...
> I checked all of the log and error files (in /LOG), and don't see any
> mention of a backup even running. I do, however, see an empty file for the
> 16th, the last time it ran.
> I went into Enterprise Manager and after some poking about managed to find
> the internal logs, the ones stored in the table. This did have an entry
(so
> why didn't the log file?!) for the 16th, which stated that "Backup can not
be
> performed on this database. This sub task is ignored", and said it
> _succeeded_.
> Either way, still no backup file!
I missed it, is this a full or transaction log backup?
If it's a transaction log backup either your database may be in SIMPLE
recovery mode or a bulk insert has been done since the last full backup.
> This shouldn't be this confusing.
> Maury|||"Greg D. Moore (Strider)" wrote:
> > Either way, still no backup file!
> I missed it, is this a full or transaction log backup?
Transaction log.
> If it's a transaction log backup either your database may be in SIMPLE
> recovery mode or a bulk insert has been done since the last full backup.
There is no "simple" in 7.0, but I have turned off the confusingly-named
options that seem to imply transaction logs should now work.
The only bulk work we do is SELECT INTO on #temp tables. If THAT is the
cause of the problem, I can't imagine why -- bulks into temp tables should be
ignored for the purpose of transaction logging.
And if that is the cause, what is the solution? Diff backups prior to trans
backups?
Maury|||What is the setting of "SELECT INTO / BULKCOPY"? Try turning it off. It doesn't have to be on for
SELECTINTO into temp tables...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:856788B4-5904-4027-89AD-CAFC5275009C@.microsoft.com...
> "Greg D. Moore (Strider)" wrote:
>> > Either way, still no backup file!
>> I missed it, is this a full or transaction log backup?
> Transaction log.
>> If it's a transaction log backup either your database may be in SIMPLE
>> recovery mode or a bulk insert has been done since the last full backup.
> There is no "simple" in 7.0, but I have turned off the confusingly-named
> options that seem to imply transaction logs should now work.
> The only bulk work we do is SELECT INTO on #temp tables. If THAT is the
> cause of the problem, I can't imagine why -- bulks into temp tables should be
> ignored for the purpose of transaction logging.
> And if that is the cause, what is the solution? Diff backups prior to trans
> backups?
> Maury

Backup claims to have run, but there's no file

I have been trying to figure out why my transaction log backup task doesn't
seem to do anything. I changed the user account to SA, and now I see in the
logs that the task did run (no more account errors).
When I View Job History on the task, I see that it ran last night at 5AM as
I expected, and there's no errors. But there's no file!
Any ideas?
MauryThat's not much to go on but is there any chance the db is in Simple
Recovery mode?
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:4E2A71B7-814A-4DC8-98B3-0974F1778827@.microsoft.com...
>I have been trying to figure out why my transaction log backup task doesn't
> seem to do anything. I changed the user account to SA, and now I see in
> the
> logs that the task did run (no more account errors).
> When I View Job History on the task, I see that it ran last night at 5AM
> as
> I expected, and there's no errors. But there's no file!
> Any ideas?
> Maury|||"Andrew J. Kelly" wrote:
> That's not much to go on but is there any chance the db is in Simple
> Recovery mode?
I can't tell you, I'm running '97 and Enterprise Manager doesn't say. Was
this even an option in earlier versions?
It would certainly make sense that this could cause the problem, I never
thought of it likely because I never saw the option while poking about!
After a little googling I note that the SQL Server Agent appearently resets
the server to Simple (assuming my server has this). This might be the proble
m
right there.
Maury|||That's SQL Server 7.0 of course, not '97|||In 7.0 it would not be called Simple mode. It would have Truncate Log on
ChkPoint I believe.
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:5BF9ADF5-4465-43C6-B090-36BDF0322629@.microsoft.com...
> That's SQL Server 7.0 of course, not '97|||"Andrew J. Kelly" wrote:
> In 7.0 it would not be called Simple mode. It would have Truncate Log on
> ChkPoint I believe.
That option I do see. So I will turn this off and keep my fingers crossed.
BTW, what is a checkpoint, and when does it occur?
Maury|||Look up CHECKPOINT in BooksOnLine for details.
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14EEF517-A29E-40FA-983C-476E721FCD46@.microsoft.com...
> "Andrew J. Kelly" wrote:
> That option I do see. So I will turn this off and keep my fingers crossed.
> BTW, what is a checkpoint, and when does it occur?
> Maury|||No luck so far...
I checked all of the log and error files (in /LOG), and don't see any
mention of a backup even running. I do, however, see an empty file for the
16th, the last time it ran.
I went into Enterprise Manager and after some poking about managed to find
the internal logs, the ones stored in the table. This did have an entry (so
why didn't the log file?!) for the 16th, which stated that "Backup can not b
e
performed on this database. This sub task is ignored", and said it
_succeeded_.
Either way, still no backup file!
This shouldn't be this confusing.
Maury|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:28194502-6F72-4448-935B-E8DAE25CBCEA@.microsoft.com...
> No luck so far...
> I checked all of the log and error files (in /LOG), and don't see any
> mention of a backup even running. I do, however, see an empty file for the
> 16th, the last time it ran.
> I went into Enterprise Manager and after some poking about managed to find
> the internal logs, the ones stored in the table. This did have an entry
(so
> why didn't the log file?!) for the 16th, which stated that "Backup can not
be
> performed on this database. This sub task is ignored", and said it
> _succeeded_.
> Either way, still no backup file!
I missed it, is this a full or transaction log backup?
If it's a transaction log backup either your database may be in SIMPLE
recovery mode or a bulk insert has been done since the last full backup.

> This shouldn't be this confusing.
> Maury|||"Greg D. Moore (Strider)" wrote:
> I missed it, is this a full or transaction log backup?
Transaction log.

> If it's a transaction log backup either your database may be in SIMPLE
> recovery mode or a bulk insert has been done since the last full backup.
There is no "simple" in 7.0, but I have turned off the confusingly-named
options that seem to imply transaction logs should now work.
The only bulk work we do is SELECT INTO on #temp tables. If THAT is the
cause of the problem, I can't imagine why -- bulks into temp tables should b
e
ignored for the purpose of transaction logging.
And if that is the cause, what is the solution? Diff backups prior to trans
backups?
Maury

Friday, February 24, 2012

Backup and Job view and execute permissions

"Sue Hoegemeier" wrote:

> Using TargetServerRole isn't a documented approach - the
> permissions for this role depend on what service pack you
> are on.
> To view and execute jobs, the user needs to be the job owner
> or a member of sysadmins.
> Viewing contents of a device executes a restore headeronly
> which any user can execute.
> -Sue
> On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
> <RumulusKyle@.discussions.microsoft.com> wrote:
>
>

Backup and Job view and execute permissions

Can you please help me to identify which roles or permissions i need to grant
to a user to do the following task without assigning sysadmin role?
1. View and execute SQL Jobs
2. View backup device and its contents
I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
required permission to perform the above operation.Using TargetServerRole isn't a documented approach - the
permissions for this role depend on what service pack you
are on.
To view and execute jobs, the user needs to be the job owner
or a member of sysadmins.
Viewing contents of a device executes a restore headeronly
which any user can execute.
-Sue
On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
<RumulusKyle@.discussions.microsoft.com> wrote:
>Can you please help me to identify which roles or permissions i need to grant
>to a user to do the following task without assigning sysadmin role?
>1. View and execute SQL Jobs
>2. View backup device and its contents
>I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
>required permission to perform the above operation.|||Thanks Sue. :)
"Sue Hoegemeier" wrote:
> Using TargetServerRole isn't a documented approach - the
> permissions for this role depend on what service pack you
> are on.
> To view and execute jobs, the user needs to be the job owner
> or a member of sysadmins.
> Viewing contents of a device executes a restore headeronly
> which any user can execute.
> -Sue
> On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
> <RumulusKyle@.discussions.microsoft.com> wrote:
> >Can you please help me to identify which roles or permissions i need to grant
> >to a user to do the following task without assigning sysadmin role?
> >
> >1. View and execute SQL Jobs
> >2. View backup device and its contents
> >
> >I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
> >required permission to perform the above operation.
>

Backup and Job view and execute permissions

Backup a Database with a number as name

I've got some numbered databases in an SQL Server 2000 instance with SP3a (eg
'300'). When I create a task to backup this database, I can't get the
statement parsed. I've tried the following:
backup database 301 to Disk_301 with init;
backup database '301' to Disk_301 with init;
backup database "301" to Disk_301 with init;
All statements above result in "Error 170: Incorrect syntax near '301'"
What's going on here?
Wilbert,
You need square brackets.
create database [300]
go
backup database [300] to disk = 'c:\temp\300.bak'
go
Gives:
The CREATE DATABASE process is allocating 0.63 MB on disk '300'.
The CREATE DATABASE process is allocating 0.49 MB on disk '300_log'.
Processed 80 pages for database '300', file '300' on file 1.
Processed 1 pages for database '300', file '300_log' on file 1.
BACKUP DATABASE successfully processed 81 pages in 0.254 seconds (2.588
MB/sec).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Wilbert wrote:
> I've got some numbered databases in an SQL Server 2000 instance with SP3a (eg
> '300'). When I create a task to backup this database, I can't get the
> statement parsed. I've tried the following:
> backup database 301 to Disk_301 with init;
> backup database '301' to Disk_301 with init;
> backup database "301" to Disk_301 with init;
> All statements above result in "Error 170: Incorrect syntax near '301'"
> What's going on here?
>

Backup a Database with a number as name

I've got some numbered databases in an SQL Server 2000 instance with SP3a (e
g
'300'). When I create a task to backup this database, I can't get the
statement parsed. I've tried the following:
backup database 301 to Disk_301 with init;
backup database '301' to Disk_301 with init;
backup database "301" to Disk_301 with init;
All statements above result in "Error 170: Incorrect syntax near '301'"
What's going on here?Wilbert,
You need square brackets.
create database [300]
go
backup database [300] to disk = 'c:\temp\300.bak'
go
Gives:
The CREATE DATABASE process is allocating 0.63 MB on disk '300'.
The CREATE DATABASE process is allocating 0.49 MB on disk '300_log'.
Processed 80 pages for database '300', file '300' on file 1.
Processed 1 pages for database '300', file '300_log' on file 1.
BACKUP DATABASE successfully processed 81 pages in 0.254 seconds (2.588
MB/sec).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Wilbert wrote:
> I've got some numbered databases in an SQL Server 2000 instance with SP3a
(eg
> '300'). When I create a task to backup this database, I can't get the
> statement parsed. I've tried the following:
> backup database 301 to Disk_301 with init;
> backup database '301' to Disk_301 with init;
> backup database "301" to Disk_301 with init;
> All statements above result in "Error 170: Incorrect syntax near '301'"
> What's going on here?
>

Backup a Database with a number as name

I've got some numbered databases in an SQL Server 2000 instance with SP3a (eg
'300'). When I create a task to backup this database, I can't get the
statement parsed. I've tried the following:
backup database 301 to Disk_301 with init;
backup database '301' to Disk_301 with init;
backup database "301" to Disk_301 with init;
All statements above result in "Error 170: Incorrect syntax near '301'"
What's going on here?Wilbert,
You need square brackets.
create database [300]
go
backup database [300] to disk = 'c:\temp\300.bak'
go
Gives:
The CREATE DATABASE process is allocating 0.63 MB on disk '300'.
The CREATE DATABASE process is allocating 0.49 MB on disk '300_log'.
Processed 80 pages for database '300', file '300' on file 1.
Processed 1 pages for database '300', file '300_log' on file 1.
BACKUP DATABASE successfully processed 81 pages in 0.254 seconds (2.588
MB/sec).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Wilbert wrote:
> I've got some numbered databases in an SQL Server 2000 instance with SP3a (eg
> '300'). When I create a task to backup this database, I can't get the
> statement parsed. I've tried the following:
> backup database 301 to Disk_301 with init;
> backup database '301' to Disk_301 with init;
> backup database "301" to Disk_301 with init;
> All statements above result in "Error 170: Incorrect syntax near '301'"
> What's going on here?
>

Sunday, February 12, 2012

Backing Up Transaction Logs

Dear sirs/ma'am

When setting up a maintenance plan in 2000, I can remove files older than 1 day.

When creating a maintenance task in 2005, I do not have that option (the database is in full mode). The only thing I can do (it seems) is to;

1. create a job with a step that uses a script from going through the motions of creating a log backup directly off the database. This is where I can say that the backup set will expire after 1 day, but in the destination field, the backup filename is hard-coded.

2. Then I schedule this script to run every hour. Sure enough, the log backup is overwritten due to the hard coded name.

The script reads;

BACKUP LOG [CSEPPWebCADB] TO DISK = N'D:\MSSQL\BACKUP\CSEPPWebCADB_backup.trn' WITH RETAINDAYS = 1, NOFORMAT, NOINIT, NAME = N'WebCADB_backup', SKIP, REWIND, NOUNLOAD, STATS = 10

How can I maintain a days worth of log backups or better yet, set up a maintenance plan to give me log backups every hour & delete them after they are a day old?

Sorry, I'm just not seeing how to do this.

Thanks for your help.

what is the srevice pack on this instance ? there are many enhancement in Maintenaceplan component in SP2 and the patch released after sp2 release. post back the result of

select @.@.Version

Madhu

|||

I don't believe that the person setting this up put SP 2 or the patch. I'll look into this and reply.

Thanks for your suggestion.

|||You need the correct SP2 loaded on your machine to start with. When you build a maintenance plan, you need to put in 2 tasks. One task is the backup database task. The second task is a clean up task. The clean up task is what removes the files older than X.|||

Thank you sir!

JDA

Sunday, March 25, 2012

Backup error

Thursday, March 22, 2012

Backup error

Tuesday, March 20, 2012

Back-up DB but keep the files for 3 days?

Back-up DB but keep the files for 3 days?

Back-up DB but keep the files for 3 days?

Back-up DB but keep the files for 3 days?

Monday, March 19, 2012

Backup Database using Files

Backup Database Task

Backup Database Task

Thursday, March 8, 2012

Backup claims to have run, but there's no file

Backup claims to have run, but there's no file

Backup claims to have run, but there's no file

Friday, February 24, 2012

Backup and Job view and execute permissions

Backup and Job view and execute permissions

Backup and Job view and execute permissions

Backup a Database with a number as name

Backup a Database with a number as name

Backup a Database with a number as name

Sunday, February 12, 2012

Backing Up Transaction Logs

Sql Backing up Tables

Blog Archive

About Me