Backup file and database name

Hi
I have a backup file.
How can I find that to which database this backup is related?
I want to do it programmaitcally.
thanks in advanceRESTORE HEADERONLY
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"perspolis" <rezarms@.hotmail.com> wrote in message news:OGbhokv0FHA.612@.TK2MSFTNGP10.phx.gb
l...
> Hi
> I have a backup file.
> How can I find that to which database this backup is related?
> I want to do it programmaitcally.
> thanks in advance
>|||Perspolis,
Might consider using Enterprise Manager for your restores - much easier and
much less coding.
HTH
Jerry
"perspolis" <rezarms@.hotmail.com> wrote in message
news:OGbhokv0FHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a backup file.
> How can I find that to which database this backup is related?
> I want to do it programmaitcally.
> thanks in advance
>

Backup Error

Hi
I have SQL7 running and is being backed up with ArcServ 9. The server has Ar
cServ SQL backup client running but the backups keep coming up with errors w
hen trying to backup the contents of the \MSSQL\FTDATA directory. The messag
e is "The process cannot ac
cess the file because it is being used by another process."
Is it important to backup the FTDATA directory?
cheers
nuvethe arcserve is not able to backup ftdata directory cause its used by sql
server.. it might have mdf and ldf files in it..
ur already using arcserv sql backup client .. why dont u use that to backup
sql database instead of backing up the whole directory. (if u dont have
arcserve open file backup agent this directory will never be backed up)
other alternate is backup the database using sql jobs in to one of the
directory may be d:\backup and backup this directory by arcserve .
Hope this would solve your query ..
Regards,
Mayur
"Nuve" <anonymous@.discussions.microsoft.com> wrote in message
news:8B7872E2-9CBB-4E44-AC44-E1A03E9525AB@.microsoft.com...
> Hi
> I have SQL7 running and is being backed up with ArcServ 9. The server has
ArcServ SQL backup client running but the backups keep coming up with errors
when trying to backup the contents of the \MSSQL\FTDATA directory. The
message is "The process cannot access the file because it is being used by
another process."
> Is it important to backup the FTDATA directory?
> cheers
> nuve|||The ftdata directory does not have any databases in it (.mdf or .ldf files).
This is the location where the full text search indexes and catalogs are
kept.
If Arcserve will not backup open files, then you will have to bring the
databases, or the server down in order to backup that directory... and if
you are using full text search it is important. ( But can be regenerated
from the data in the tables if you must... It would just take a while.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Nuve" <anonymous@.discussions.microsoft.com> wrote in message
news:8B7872E2-9CBB-4E44-AC44-E1A03E9525AB@.microsoft.com...
> Hi
> I have SQL7 running and is being backed up with ArcServ 9. The server has
ArcServ SQL backup client running but the backups keep coming up with errors
when trying to backup the contents of the \MSSQL\FTDATA directory. The
message is "The process cannot access the file because it is being used by
another process."
> Is it important to backup the FTDATA directory?
> cheers
> nuvesql

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

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

sql

Backup databases

Hi

I'm trying to setup a back up plan for a number of databases, I initially set up one plan to include all user databases which worked fine or so I thought, when I check them a few days later I noticed that some of the databases were not appearing in the backup set, the only way I could get these to appear is to set the comp level to 90, now when we run certain applications we get an error, when I return the comp level back to 70 then the application works fine, is there a reason I can not back up any database on sql 2005 without it being a comp level 90?

Thanks inadvance

I'm guessing that the maintenance plan you've created uses some feature or syntax which didn't exist in SQL 7.0

Look over the TSQL backup commands in your maintenance plan and verify that the syntax there is compatible with SQL 7.0

|||

Hi thanks for the reply, but its not getting that far, stepping through the wizard,first it asks for the database(s) to back up, the choice being all system databases, or specific databases and I don't see any database that has not been set to comp level 90, so no tsql to check.

Thanks

|||

I see that on my system as well.

Is compatibility level 80 an option for you? Databases with that compatibility level do show up in the Wizard.

I'll check on why 70 databases are excluded, but I suspect that it has to do with what was supported at that version.

Ultimately your best option may be to write a backup script yourself.

|||

Hi

Yes thanks, not sure why I didn't think of that, but setting to comp level 80 does the trick,, thanks!

|||I have encountered the same problem when I setup the maintenance plan with the maintenance plan Wizard. Any help if I cannot set the comp. level to 80? Please advice, thanks!

Backup and Restore with STOPAT

Hi
I want to implement the following simple scenario as a test prior the real
implementation to see if these scenario is plausible. I want to test the
STOPAT option of the RESTORE LOG command.
1. First I want to full backup the database (the "Recovery model"
option is set to "Full") using the following code:
BACKUP DATABASE atsTables
TO DISK = 'c:\temp\20060318_1236_atsTables.dat'
WITH
DESCRIPTION = 'atsTables Backup1'
2. After backup I will insert a new row in one table of the db with the
following command (clock time: 12:37:xx):
INSERT INTO Classes(Title)
VALUES('Test1')
3. After at least one minute from the first insert I will insert
another row to the db (now the time is: 12:38:xx)
INSERT INTO Classes(Title)
VALUES('Test2')
4. At 12:39:xx I will backup the Transaction protocol with the
following command:
BACKUP LOG atsTables
TO DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
5. Now I will restore the db:
RESTORE DATABASE atsTables
FROM DISK = 'c:\temp\20060318_1236_atsTables.dat'
WITH
NORECOVERY
, REPLACE
6. I will now restore the transaction protocol and stop at 12:37:59
DECLARE @.dtm as DateTime
SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
RESTORE LOG atsTABLES
FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
WITH
Recovery
, STOPAT = @.dtm
7. I expect to have now in the "Classes" table only the "Test1" class
"Title".
USE atsTables
SELECT *
FROM Classes
8. But I get the error that the DB cannot be opened because it is still
restoring
9. Ok, so I don't know for sure what's wrong so I use the RESTORE LOG
command again as before:
DECLARE @.dtm as DateTime
SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
RESTORE LOG atsTABLES
FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
WITH
Recovery
, STOPAT = @.dtm
10. I select again the Classes table:
USE atsTables
SELECT *
FROM Classes
11. Now it works but I get both classes ("Test1" and "Test2") instead of
only the "Test1" class title
The question is:
Why is the STOPAT option of the RESTORE LOG not working as I expected? Is my
scenario wrong? Can someone help me further?
Any answer can help me a lot.
Thanks, George.Hi
Read this article
http://www.karaszi.com/SQLServer/in...veral_times.asp
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:e68eWErSGHA.196@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I want to implement the following simple scenario as a test prior the real
> implementation to see if these scenario is plausible. I want to test the
> STOPAT option of the RESTORE LOG command.
>
> 1. First I want to full backup the database (the "Recovery model"
> option is set to "Full") using the following code:
>
> BACKUP DATABASE atsTables
> TO DISK = 'c:\temp\20060318_1236_atsTables.dat'
> WITH
> DESCRIPTION = 'atsTables Backup1'
>
> 2. After backup I will insert a new row in one table of the db with
> the following command (clock time: 12:37:xx):
>
> INSERT INTO Classes(Title)
> VALUES('Test1')
>
> 3. After at least one minute from the first insert I will insert
> another row to the db (now the time is: 12:38:xx)
>
> INSERT INTO Classes(Title)
> VALUES('Test2')
>
> 4. At 12:39:xx I will backup the Transaction protocol with the
> following command:
>
> BACKUP LOG atsTables
> TO DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
>
> 5. Now I will restore the db:
>
> RESTORE DATABASE atsTables
> FROM DISK = 'c:\temp\20060318_1236_atsTables.dat'
> WITH
> NORECOVERY
> , REPLACE
>
> 6. I will now restore the transaction protocol and stop at 12:37:59
>
> DECLARE @.dtm as DateTime
> SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
>
> RESTORE LOG atsTABLES
> FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
> WITH
> Recovery
> , STOPAT = @.dtm
>
> 7. I expect to have now in the "Classes" table only the "Test1" class
> "Title".
>
> USE atsTables
> SELECT *
> FROM Classes
>
> 8. But I get the error that the DB cannot be opened because it is
> still restoring
> 9. Ok, so I don't know for sure what's wrong so I use the RESTORE LOG
> command again as before:
>
> DECLARE @.dtm as DateTime
> SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
>
> RESTORE LOG atsTABLES
> FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
> WITH
> Recovery
> , STOPAT = @.dtm
>
> 10. I select again the Classes table:
>
> USE atsTables
> SELECT *
> FROM Classes
>
> 11. Now it works but I get both classes ("Test1" and "Test2") instead of
> only the "Test1" class title
>
> The question is:
> Why is the STOPAT option of the RESTORE LOG not working as I expected? Is
> my scenario wrong? Can someone help me further?
>
> Any answer can help me a lot.
> Thanks, George.
>|||Hi, George
You should verify that the @.dtm variable contains the expected value. I
guess that the conversion of the varchar value to a datetime resulted
in a different value than expected. You should specify the dates in a
language neutral format, for example '20060318 12:37:59' or
'2006-03-18T12:37:59'. See the following article for more informations:
http://www.karaszi.com/SQLServer/info_datetime.asp
Razvan|||After reading the article everithing is clear now and works.
Thanks for help.
George.
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:e68eWErSGHA.196@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I want to implement the following simple scenario as a test prior the real
> implementation to see if these scenario is plausible. I want to test the
> STOPAT option of the RESTORE LOG command.
>
> 1. First I want to full backup the database (the "Recovery model"
> option is set to "Full") using the following code:
>
> BACKUP DATABASE atsTables
> TO DISK = 'c:\temp\20060318_1236_atsTables.dat'
> WITH
> DESCRIPTION = 'atsTables Backup1'
>
> 2. After backup I will insert a new row in one table of the db with
> the following command (clock time: 12:37:xx):
>
> INSERT INTO Classes(Title)
> VALUES('Test1')
>
> 3. After at least one minute from the first insert I will insert
> another row to the db (now the time is: 12:38:xx)
>
> INSERT INTO Classes(Title)
> VALUES('Test2')
>
> 4. At 12:39:xx I will backup the Transaction protocol with the
> following command:
>
> BACKUP LOG atsTables
> TO DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
>
> 5. Now I will restore the db:
>
> RESTORE DATABASE atsTables
> FROM DISK = 'c:\temp\20060318_1236_atsTables.dat'
> WITH
> NORECOVERY
> , REPLACE
>
> 6. I will now restore the transaction protocol and stop at 12:37:59
>
> DECLARE @.dtm as DateTime
> SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
>
> RESTORE LOG atsTABLES
> FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
> WITH
> Recovery
> , STOPAT = @.dtm
>
> 7. I expect to have now in the "Classes" table only the "Test1" class
> "Title".
>
> USE atsTables
> SELECT *
> FROM Classes
>
> 8. But I get the error that the DB cannot be opened because it is
> still restoring
> 9. Ok, so I don't know for sure what's wrong so I use the RESTORE LOG
> command again as before:
>
> DECLARE @.dtm as DateTime
> SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
>
> RESTORE LOG atsTABLES
> FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
> WITH
> Recovery
> , STOPAT = @.dtm
>
> 10. I select again the Classes table:
>
> USE atsTables
> SELECT *
> FROM Classes
>
> 11. Now it works but I get both classes ("Test1" and "Test2") instead of
> only the "Test1" class title
>
> The question is:
> Why is the STOPAT option of the RESTORE LOG not working as I expected? Is
> my scenario wrong? Can someone help me further?
>
> Any answer can help me a lot.
> Thanks, George.
>

Backup 2005 Express

Hi
I just tried to schedule a backup for a database created in SQL Server 2005
Express and all I could do was the actual backup but I couldn't specify the
schedule so it will backup every day
Any advice?
Samuel
Express doesn't come with Agent. Check out
http://sqldbatips.com/showcode.asp?ID=26
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:%23K87P5T8GHA.1244@.TK2MSFTNGP03.phx.gbl...
> Hi
> I just tried to schedule a backup for a database created in SQL Server 2005 Express and all I
> could do was the actual backup but I couldn't specify the schedule so it will backup every day
> Any advice?
> Samuel
>

Backup 2005 Express

Hi
I just tried to schedule a backup for a database created in SQL Server 2005
Express and all I could do was the actual backup but I couldn't specify the
schedule so it will backup every day
Any advice?
SamuelExpress doesn't come with Agent. Check out
http://sqldbatips.com/showcode.asp?ID=26
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:%23K87P5T8GHA.1244@.TK2MSFTNGP03.phx.gbl...
> Hi
> I just tried to schedule a backup for a database created in SQL Server 200
5 Express and all I
> could do was the actual backup but I couldn't specify the schedule so it w
ill backup every day
> Any advice?
> Samuel
>

Backup

Hi

I had problem with my backup file. I'm taking backup on remote machine a full backup in append mode just because my database size is small.Due to some reason the backup was incompleted that was shown when i'm looking the file. Now when i' try to backup on same file it gives an error messages. Is it possible to remove the incomplete mark on file.Even if i try overite the file its not allowed. Tell me why its so and how to use the same file without moving it.Howdy

No - rename the old file & use a new file . If the old file is corrupted, you run the risk of continually using a corrupted backup - not a good thing.

Cheers,

SG.

Backup

Hi
I am having problem with schedule tape backup in maintenance plan sql server
2005<o:p></o:p>
I have made a schedule for tape backup on Ultrium 2 LTO drive for all
databases. Backup succesfully completes but the tape could not be unloaded
even after pressing the eject button on the tape drive. I could not find any
tape unload/rewind option in the maintenace plan.<o:p></o:p>
Is there any option avilable in sql server 2005 to unload the tape in the
maintenance plan schedule backup, if so pls let me know the same at your
earliest and that will be a great help for me<o:p></o:p>
Thanks with regards
VijayHi
"Vijay" wrote:

> Hi
> I am having problem with schedule tape backup in maintenance plan sql serv
er
> 2005<o:p></o:p>
> I have made a schedule for tape backup on Ultrium 2 LTO drive for all
> databases. Backup succesfully completes but the tape could not be unloaded
> even after pressing the eject button on the tape drive. I could not find a
ny
> tape unload/rewind option in the maintenace plan.<o:p></o:p>
> Is there any option avilable in sql server 2005 to unload the tape in the
> maintenance plan schedule backup, if so pls let me know the same at your
> earliest and that will be a great help for me<o:p></o:p>
> Thanks with regards
> Vijay
>
I don't know of any option, but you could add a job step that did a RESTORE
REWINDONLY at the end of the job. See Books inline for more.
John

Backup

Hi
I am having problem with schedule tape backup in maintenance plan sql server
2005<o:p></o:p>
I have made a schedule for tape backup on Ultrium 2 LTO drive for all
databases. Backup succesfully completes but the tape could not be unloaded
even after pressing the eject button on the tape drive. I could not find any
tape unload/rewind option in the maintenace plan.<o:p></o:p>
Is there any option avilable in sql server 2005 to unload the tape in the
maintenance plan schedule backup, if so pls let me know the same at your
earliest and that will be a great help for me<o:p></o:p>
Thanks with regards
Vijay
Hi
"Vijay" wrote:

> Hi
> I am having problem with schedule tape backup in maintenance plan sql server
> 2005<o:p></o:p>
> I have made a schedule for tape backup on Ultrium 2 LTO drive for all
> databases. Backup succesfully completes but the tape could not be unloaded
> even after pressing the eject button on the tape drive. I could not find any
> tape unload/rewind option in the maintenace plan.<o:p></o:p>
> Is there any option avilable in sql server 2005 to unload the tape in the
> maintenance plan schedule backup, if so pls let me know the same at your
> earliest and that will be a great help for me<o:p></o:p>
> Thanks with regards
> Vijay
>
I don't know of any option, but you could add a job step that did a RESTORE
REWINDONLY at the end of the job. See Books inline for more.
John