Sql Backing up Tables: bak

Showing posts with label bak. Show all posts

Tuesday, March 27, 2012

backup extension is?

when you make a backup of an SQL dbase...
what is the extension?
Is it not whatever.bak ?
JimWell that depends on how you did the backup. Some extra information in that
area would surely help to answer that question.
--
Andrew J. Kelly SQL MVP
"JVRudnick" <JVRudnick@.discussions.microsoft.com> wrote in message
news:D2CA43DA-2737-435A-8039-645FA44D380E@.microsoft.com...
> when you make a backup of an SQL dbase...
> what is the extension?
> Is it not whatever.bak ?
> Jim|||Hi,
There is no limitation on Extension. Backup file extension can be any thing.
Thanks
Hari
SQL Server MVP
"JVRudnick" <JVRudnick@.discussions.microsoft.com> wrote in message
news:D2CA43DA-2737-435A-8039-645FA44D380E@.microsoft.com...
> when you make a backup of an SQL dbase...
> what is the extension?
> Is it not whatever.bak ?
> Jim|||Hmm...
I rec'd an SQL backup file (supposedly) that has NO extension on it at all.
I tried to use it to do a restore, and EM tells me 'sorry, that is not a
backup file..."
'
"Hari Prasad" wrote:
> Hi,
> There is no limitation on Extension. Backup file extension can be any thing.
> Thanks
> Hari
> SQL Server MVP
> "JVRudnick" <JVRudnick@.discussions.microsoft.com> wrote in message
> news:D2CA43DA-2737-435A-8039-645FA44D380E@.microsoft.com...
> > when you make a backup of an SQL dbase...
> >
> > what is the extension?
> >
> > Is it not whatever.bak ?
> >
> > Jim
>
>|||Whether or not it has an extension depends on the person who created the backup. SQL Server doesn't
care at all about extensions for backup or database files. They are for our convenience. Perhaps it
isn't a SQL Server backup file? Did you try RESTORE HEADERONLY?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JVRudnick" <JVRudnick@.discussions.microsoft.com> wrote in message
news:90C28FB5-0EC6-42B1-BD69-C1FF47515567@.microsoft.com...
> Hmm...
> I rec'd an SQL backup file (supposedly) that has NO extension on it at all.
> I tried to use it to do a restore, and EM tells me 'sorry, that is not a
> backup file..."
> '
> "Hari Prasad" wrote:
>> Hi,
>> There is no limitation on Extension. Backup file extension can be any thing.
>> Thanks
>> Hari
>> SQL Server MVP
>> "JVRudnick" <JVRudnick@.discussions.microsoft.com> wrote in message
>> news:D2CA43DA-2737-435A-8039-645FA44D380E@.microsoft.com...
>> > when you make a backup of an SQL dbase...
>> >
>> > what is the extension?
>> >
>> > Is it not whatever.bak ?
>> >
>> > Jim
>>|||This is a multi-part message in MIME format.
--040308040903000906030205
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Perhaps it's even a detached data file...<shrug> You might want to also
try sp_attach_db
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp>
at a guess.
--
*mike hodgson*
blog: http:sqlnerd.blogspot.com
Tibor Karaszi wrote:
> Whether or not it has an extension depends on the person who created
> the backup. SQL Server doesn't care at all about extensions for backup
> or database files. They are for our convenience. Perhaps it isn't a
> SQL Server backup file? Did you try RESTORE HEADERONLY?
>
--040308040903000906030205
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Perhaps it's even a detached data file...<shrug>Â You might
want to also try <a
href="http://links.10026.com/?link=sp_attach_db</a>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp">sp_attach_db</a>
at a guess. 
</tt>
<div class="moz-signature">
<title> </title>
<meta http-equiv="Content-Type" content="text/html; ">

 -- 
mike hodgson 
blog:Â <a href="http://links.10026.com/?link=http:sqlnerd.blogspot.com</a>">http:sqlnerd.blogspot.com">http:sqlnerd.blogspot.com</a>

 </div>
 
 
Tibor Karaszi wrote:
<blockquote cite="midOdlpdzxqFHA.2996@.tk2msftngp13.phx.gbl" type="cite">Whether
or not it has an extension depends on the person who created the
backup. SQL Server doesn't care at all about extensions for backup or
database files. They are for our convenience. Perhaps it isn't a SQL
Server backup file? Did you try RESTORE HEADERONLY?
 
 
</blockquote>
</body>
</html>
--040308040903000906030205--|||Good thinking Mike.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uFcbTJ4qFHA.2604@.TK2MSFTNGP14.phx.gbl...
> Perhaps it's even a detached data file...<shrug> You might want to also
> try sp_attach_db
> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp>
> at a guess.
> --
> *mike hodgson*
> blog: http:sqlnerd.blogspot.com
>
> Tibor Karaszi wrote:
>> Whether or not it has an extension depends on the person who created
>> the backup. SQL Server doesn't care at all about extensions for backup
>> or database files. They are for our convenience. Perhaps it isn't a
>> SQL Server backup file? Did you try RESTORE HEADERONLY?
>|||See if they have used a third party backup utility like the sql liteSpeed
--
Vikram Vamshi
Database Engineer
Eclipsys Corporation
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uCgBwRqqFHA.240@.tk2msftngp13.phx.gbl...
> Well that depends on how you did the backup. Some extra information in
> that area would surely help to answer that question.
> --
> Andrew J. Kelly SQL MVP
>
> "JVRudnick" <JVRudnick@.discussions.microsoft.com> wrote in message
> news:D2CA43DA-2737-435A-8039-645FA44D380E@.microsoft.com...
>> when you make a backup of an SQL dbase...
>> what is the extension?
>> Is it not whatever.bak ?
>> Jim
>sql

Sunday, March 25, 2012

Backup error

Hi all!
I take backup of a database every night using
"BACKUP DATABASE [Db1] TO DISK = N'\\server\e$\Backup\Databases\Db1\Db1.bak' WITH INIT , NOUNLOAD , NAME = N'Db1 backup', NOSKIP , STATS = 10, NOFORMAT"
But some nights a have recived the following error message when I view the job history.
"'\\server\e$\Backup\Db1\Db1\Db1.bak' is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed."
I have tried to delete the backup devices and created new ones. I am backing up close to twenty databases to the same folder every night. But I get error on a couple backup jobs every night, not the same ones every night. I have tride to run the jobs manually daytime and sometimes they run ok and sometimes they fails.
I have read the MS KB 290787 but i'm not sure if it answers my question. Does anybody has any more info/help/hints it would be greatly appriciated.
Thansk
FredrikSounds like you may have some disk issues. Have you checked the integrity
of that disk lately?
--
Andrew J. Kelly SQL MVP
"Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
news:46FACB20-2670-41DD-9452-CE9753680453@.microsoft.com...
> Hi all!
> I take backup of a database every night using
> "BACKUP DATABASE [Db1] TO DISK =N'\\server\e$\Backup\Databases\Db1\Db1.bak' WITH INIT , NOUNLOAD , NAME =N'Db1 backup', NOSKIP , STATS = 10, NOFORMAT"
> But some nights a have recived the following error message when I view the
job history.
> "'\\server\e$\Backup\Db1\Db1\Db1.bak' is incorrectly formatted. Backups
cannot be appended, but existing backup sets may still be usable. [SQLSTATE
42000] (Error 3266) BACKUP DATABASE is terminating abnormally. [SQLSTATE
42000] (Error 3013). The step failed."
> I have tried to delete the backup devices and created new ones. I am
backing up close to twenty databases to the same folder every night. But I
get error on a couple backup jobs every night, not the same ones every
night. I have tride to run the jobs manually daytime and sometimes they run
ok and sometimes they fails.
> I have read the MS KB 290787 but i'm not sure if it answers my question.
Does anybody has any more info/help/hints it would be greatly appriciated.
> Thansk
> Fredrik|||sounds like a hard drive issue (?) also is this device
mirrored?
Kevin
>--Original Message--
>Hi all!
>I take backup of a database every night using
>"BACKUP DATABASE [Db1] TO DISK =N'\\server\e$\Backup\Databases\Db1\Db1.bak' WITH INIT ,
NOUNLOAD , NAME = N'Db1 backup', NOSKIP , STATS = 10,
NOFORMAT"
>But some nights a have recived the following error
message when I view the job history.
>"'\\server\e$\Backup\Db1\Db1\Db1.bak' is incorrectly
formatted. Backups cannot be appended, but existing backup
sets may still be usable. [SQLSTATE 42000] (Error 3266)
BACKUP DATABASE is terminating abnormally. [SQLSTATE
42000] (Error 3013). The step failed."
>I have tried to delete the backup devices and created new
ones. I am backing up close to twenty databases to the
same folder every night. But I get error on a couple
backup jobs every night, not the same ones every night. I
have tride to run the jobs manually daytime and sometimes
they run ok and sometimes they fails.
>I have read the MS KB 290787 but i'm not sure if it
answers my question. Does anybody has any more
info/help/hints it would be greatly appriciated.
>Thansk
>Fredrik
>.
>|||Hi Fredrik
I have a similar problem and was able to fix it, quick and simple.
I renamed the backup and log file
and re ran my backup job, it obviously was unable to append as the file was not fould (due to name change), this created new file
and the backup job suceeded.
Note before all this I manually backed up DB (FULL) and Log file (for each db that I had the problem for) Just in case :)
For completness my exact error was
Executed as user: [USER NAME]. The backup data in '[DB NAME]' is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
obviously i was able to delete all non required excess backup files after a few days as I was happy with my backups
Good Luck
Eoin B
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

Thursday, March 22, 2012

Backup error

For completness my exact error was
Executed as user: [USER NAME]. The backup data in '[DB NAME]' is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (E
rror 3013). The step failed.
obviously i was able to delete all non required excess backup files after a few days as I was happy with my backups
Good Luck
Eoin B
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
sql

Backup error

For completness my exact error was
Executed as user: [USER NAME]. The backup data in '[DB NAME]' is inc
orrectly formatted. Backups cannot be appended, but existing backup sets may
still be usable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is term
inating abnormally. [SQLSTATE 42000] (E
rror 3013). The step failed.
obviously i was able to delete all non required excess backup files after a
few days as I was happy with my backups
Good Luck
Eoin B
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.sql

Tuesday, March 20, 2012

Backup device creation problem

I have an issue while taking a backup into a device. It
throws out as below
" Cannot open backup device 'D:\MSSQL7\BACKUP\xyz.bak'.
Device error or device off-line. "
Can anyone who has already experience this help me out ?
Thanks,
Raghu1. Is D: a local drive?
2. Is xyz.bak writable?
3. Does the account under which the SQL Server service runs have permissions
to access the file?
--
Scott Nichol
"raghu" <anonymous@.discussions.microsoft.com> wrote in message
news:0dd101c3a851$99dbebd0$a301280a@.phx.gbl...
> I have an issue while taking a backup into a device. It
> throws out as below
> " Cannot open backup device 'D:\MSSQL7\BACKUP\xyz.bak'.
> Device error or device off-line. "
> Can anyone who has already experience this help me out ?
> Thanks,
> Raghusql

Backup Device

I am trying to add a backup device to SQL that will use our NAS. When I enter
the UNC \\ProCom-SERVER\BACKUP\DISK.BAK for the file location I get a message
that SQL Server can not verify the existenance of the backup file location.
The path \\PROCOM-SERVER\BACKUP exist and I use it for other backup files.
The SQL Server is a stand alone server and is not a member of a domain and
neither is the NAS, this is in a peer to peer workgroup network. I have
applied service pack 4 as well.
Any help will be appreciated.
Does the account that SQL Server is running under have rights to that share
on the NAS?
Andrew J. Kelly SQL MVP
"ProCom" <ProCom@.discussions.microsoft.com> wrote in message
news:5FB2ABE4-6012-46AF-910C-C375DA6D6ABA@.microsoft.com...
>I am trying to add a backup device to SQL that will use our NAS. When I
>enter
> the UNC \\ProCom-SERVER\BACKUP\DISK.BAK for the file location I get a
> message
> that SQL Server can not verify the existenance of the backup file
> location.
> The path \\PROCOM-SERVER\BACKUP exist and I use it for other backup files.
> The SQL Server is a stand alone server and is not a member of a domain and
> neither is the NAS, this is in a peer to peer workgroup network. I have
> applied service pack 4 as well.
>
> Any help will be appreciated.
>

Backup Device

I am trying to add a backup device to SQL that will use our NAS. When I ente
r
the UNC \\ProCom-SERVER\BACKUP\DISK.BAK for the file location I get a messag
e
that SQL Server can not verify the existenance of the backup file location.
The path \\PROCOM-SERVER\BACKUP exist and I use it for other backup files.
The SQL Server is a stand alone server and is not a member of a domain and
neither is the NAS, this is in a peer to peer workgroup network. I have
applied service pack 4 as well.
Any help will be appreciated.Does the account that SQL Server is running under have rights to that share
on the NAS?
Andrew J. Kelly SQL MVP
"ProCom" <ProCom@.discussions.microsoft.com> wrote in message
news:5FB2ABE4-6012-46AF-910C-C375DA6D6ABA@.microsoft.com...
>I am trying to add a backup device to SQL that will use our NAS. When I
>enter
> the UNC \\ProCom-SERVER\BACKUP\DISK.BAK for the file location I get a
> message
> that SQL Server can not verify the existenance of the backup file
> location.
> The path \\PROCOM-SERVER\BACKUP exist and I use it for other backup files.
> The SQL Server is a stand alone server and is not a member of a domain and
> neither is the NAS, this is in a peer to peer workgroup network. I have
> applied service pack 4 as well.
>
> Any help will be appreciated.
>

Backup Device

I am trying to add a backup device to SQL that will use our NAS. When I enter
the UNC \\ProCom-SERVER\BACKUP\DISK.BAK for the file location I get a message
that SQL Server can not verify the existenance of the backup file location.
The path \\PROCOM-SERVER\BACKUP exist and I use it for other backup files.
The SQL Server is a stand alone server and is not a member of a domain and
neither is the NAS, this is in a peer to peer workgroup network. I have
applied service pack 4 as well.
Any help will be appreciated.Does the account that SQL Server is running under have rights to that share
on the NAS?
--
Andrew J. Kelly SQL MVP
"ProCom" <ProCom@.discussions.microsoft.com> wrote in message
news:5FB2ABE4-6012-46AF-910C-C375DA6D6ABA@.microsoft.com...
>I am trying to add a backup device to SQL that will use our NAS. When I
>enter
> the UNC \\ProCom-SERVER\BACKUP\DISK.BAK for the file location I get a
> message
> that SQL Server can not verify the existenance of the backup file
> location.
> The path \\PROCOM-SERVER\BACKUP exist and I use it for other backup files.
> The SQL Server is a stand alone server and is not a member of a domain and
> neither is the NAS, this is in a peer to peer workgroup network. I have
> applied service pack 4 as well.
>
> Any help will be appreciated.
>

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 with SQL to backup directory

I had write out a sql command to backup the database.

BACKUP DATABASE NorthWind
to disk = 'c:\NorthWind_20031113.bak'
with name = 'NorthWind_backup'

However, I intend to backup the database to the SQL server default backup directory, "\MSSQL7\Backup". How can I do that? Given that I don't know the SQL server were installed in C drive or D drive.

Another question was, can I do a integrity check using the sql command on the backup copy that i had created??

I'll be appreciate on the help that you offered.
Thanks.I tried this and it works, ie put the backup into the MSSQL/Backup folder

BACKUP DATABASE NorthWind
to disk = 'NorthWind_20031113.bak'
with name = 'NorthWind_backup'

Trick seems to be to not to specify a location and by default it used the backup folder

Mark|||Thank for your reply. I had try on the solution you given, however, the backup copy was placed to my "winnt\system32" directory. I had try both on my own pc and to backup remotely on the database server. Both of the trial was same where the backup copy being placed to the window system directory.|||The Holy book says :

If a relative path name is entered for a backup to disk, the backup file is placed in the default backup directory. This directory is set during installation and stored in the BackupDirectory registry key under

KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer.

Reffering again from the Holy Book for your second question

RESTORE VERIFYONLY
Verifies the backup but does not restore the backup. Checks to see that the backup set is complete and that all volumes are readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. If the backup is valid, Microsoft SQL Server 2000 returns the message: "The backup set is valid."

Syntax
RESTORE VERIFYONLY
FROM < backup_device > [ ,...n ]
[ WITH
[ FILE = file_number ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] LOADHISTORY ]
[ [ , ] PASSWORD = { password | @.password_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @.mediapassword_variable } ]
[ [ , ] { NOREWIND | REWIND } ]
]

< backup_device > ::=
{
{ 'logical_backup_device_name' | @.logical_backup_device_name_var }
| { DISK | TAPE } =
{ 'physical_backup_device_name' | @.physical_backup_device_name_var }
}|||If a relative path name is entered for a backup to disk, the backup file is placed in the default backup directory. This directory is set during installation and stored in the BackupDirectory registry key under

KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer.

I also found the statement above at the "Holy Book", however, i can't really understand what it means by relative path name. If I had to go to registry to read for the path, then as I know I can't do it.
Actually now I'm writing a simple program to do the database backup at the client pc and the database server only have MSDE but no enterprise manager. So, I can't check for the backup path from the registry value cause my program was running at the client pc.
I'm really grateful for the help you offered. Thanks for the answer for the second question.

:D|||Relative path means ... specifying no drive or directory .. just the filename|||I had tried it before, but the backup copy were being create at the system directory, "c:\winnt\system32" insteed of the SQL Backup folder.|||what does the BackupDirectory registry key under

KEY_LOCAL_MACHINE\Software\Microsoft\ MSSQLServer\MSSQLServer point to ?

You can see it by opening regedit and navigating to the key|||I check on it already, is pointing to the sql path. D:\MSSQL7\Backup\|||Originally posted by coffytan
I check on it already, is pointing to the sql path. D:\MSSQL7\Backup\

Still backup gos to systerm Winnt ,, wierd !!!!!!!!

Sunday, March 11, 2012

Backup database creates smaller files....

...but from a 3+GB MDF file I obtain a .BAK of only 56 MB!!!!!!!!
Is it possible or am I crazy?
Fabri
('N patri campa centu figghi e centu figghi nun s'a firunu a campari a
'n patri.)Are you doing a differential backup and overwriting the old file?
"Fabri" <no@.sp.am> wrote in message news:d4o6ul$2cp$1@.news.ngi.it...
> ...but from a 3+GB MDF file I obtain a .BAK of only 56 MB!!!!!!!!
> Is it possible or am I crazy?
> --
> Fabri
> ('N patri campa centu figghi e centu figghi nun s'a firunu a campari a 'n
> patri.)|||It is possible and SQL Server backup doesn't backup unused storage. But also
make sure you are doing
the right type of backup etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Fabri" <no@.sp.am> wrote in message news:d4o6ul$2cp$1@.news.ngi.it...
> ...but from a 3+GB MDF file I obtain a .BAK of only 56 MB!!!!!!!!
> Is it possible or am I crazy?
> --
> Fabri
> ('N patri campa centu figghi e centu figghi nun s'a firunu a campari a 'n patri.)[
/color]

Backup Database at different place

Hi All!

I know that if is want to backup my db on the c: drive i do this
BACKUP DATABASE DBName
TO DISK = 'c:\isl_fs1\DBName.bak'

BUT HOW do I take this to the network. I tried
BACKUP DATABASE DBName
TO DISK = '\\isl_fs1\DBName.bak'
it doesn't work please helpIt'll be longer, slower, at risk of network blips, not portable

Dump locally, then copy...

What, you're drive is out of space?

That's a different problem...|||I agree
typically i would back up to disk and then use network backup software to come get it out of the shared folder that i backed it up into

however to directly backup to a network address
One method is to map a drive to a network location
ex
NET USE X: \\Server\Share

and then use sql backup.
Backup database northwind to disk=x:\backup.bkf

you should however use sp_adddumpdevice to map a backup location to a permanent device it cleans up the backup processes and gives you a fixed location to find all of your backups instead of temporary ones

BackUp database

Hi all

I use this code to bak up database
and its working fine but i want run this code via ASP
can anyone write it for me in ASP format?

USE master
EXEC sp_addumpdevice 'disk', 'test_2',
'C:\test_2.dat'

USE master
EXEC sp_addumpdevice 'disk', 'testlog',
'C:\testlog.dat'

BACKUP DATABASE test TO test_2

BACKUP LOG test TO testlog

thanksAll you need to do is put each TSQL command as relevant ASP command.
In your case, you need to execute 4 commands so you would need
SQLStmt, SQLstmt1,2,3 and 4 lots of RS = Connection.Execute(SQLStmt)

e.g. - example of one connection & execution :

Dim UID
Dim SQLStmt, SQLStmt1, SQLStmt2, SQLStmt3
Dim Connection,
Dim RS, RS1, RS2, RS3

Set Connection = Server.CreateObject("ADODB.Connection")

Connection.Open "PROVIDER=SQLOLEDB;DATA SOURCE=server1;UID=test;PWD=password;DATABASE=data base1"

SQLStmt = "SELECT * FROM TBL_Test where server_name = 'SERVER1' and job_run_time > (getdate() - 7)"

Set RS = Connection.Execute(SQLStmt)

Thursday, March 8, 2012

backup big db fail

I everybody,
I have a big (well, this is relative) db, when I do a full db backup, the
.bak is arount 190G
I run under windows std server 2000, 2G mem, 4 cpu,sql server 2000
enterprise ed.
I backup the database to a dedicated server via UNC name with a dedicated
network card. I have not enought space on local disk
I do a backup every day but perhaps 1 backup per week works.
I get the following error message :
Error 1450 is : Insufficient system resources exist to complete the
requested service.
what "ressource" is not enough ? this is not the disk space since the
target server (windows 2003 R2) has 3T free space.
hope some one has some advise.
thanks in advance
BackupMedium::ReportIoError :
chec write sur l'unit de sauvegarde _\\storage\backup\Backup_22\SD_db_
200707291858.BAK_.
Erreur du systme d'exploitation 1450
(Ressources systme insuffisantes pour terminer le service demand.).
La sauvegarde n'a pas russir achever la commande
BACKUP DATABASE [SIMD] TO DISK = N'\\storage\backup\Backup_22\SD_db_
200707291858.BAK'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
Internal I/O request 0x05702918:
Op: Write, pBuffer: 0x05710000, Size: 983040,
Position: 137109051904, RetryCount: 10, UMS: Internal: 0x0,
InternalHigh: 0xF0000, Offset: 0xEC561A00, OffsetHigh: 0x1F, m_buf:
0x05710000,
m_len: 983040, m_actualBytes: 0, m_errcode: 1450,
BackupFile: \\storage\backup\Backup_22\SD_db_2007072
91858.BAKIt's probably talking about memory on the server that SQL Server is running
on. Why would you possibly have the need for Enterprise Edition and 4
processors with 190GB of data but only have 2GB of memory. My laptop has
more than that. Are you using any of the 3rd part backup & compression
tools or is this a native SQL backup? If not I would highly recommend
buying one of them and adding more memory.
Andrew J. Kelly SQL MVP
"906507N" <906507N@.10090.I20034> wrote in message
news:Xns998147FE8B8D0140507NA02619@.207.46.248.16...
>I everybody,
> I have a big (well, this is relative) db, when I do a full db backup, the
> .bak is arount 190G
> I run under windows std server 2000, 2G mem, 4 cpu,sql server 2000
> enterprise ed.
> I backup the database to a dedicated server via UNC name with a dedicated
> network card. I have not enought space on local disk
> I do a backup every day but perhaps 1 backup per week works.
> I get the following error message :
> Error 1450 is : Insufficient system resources exist to complete the
> requested service.
> what "ressource" is not enough ? this is not the disk space since the
> target server (windows 2003 R2) has 3T free space.
> hope some one has some advise.
> thanks in advance
> BackupMedium::ReportIoError :
> chec write sur l'unit de sauvegarde \\storage\backup\Backup_22\SD_db_
> 200707291858.BAK .
> Erreur du systme d'exploitation 1450
> (Ressources systme insuffisantes pour terminer le service demand.).
> La sauvegarde n'a pas russir achever la commande
> BACKUP DATABASE [SIMD] TO DISK = N'\\storage\backup\Backup_22\SD_db_
> 200707291858.BAK'
> WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> Internal I/O request 0x05702918:
> Op: Write, pBuffer: 0x05710000, Size: 983040,
> Position: 137109051904, RetryCount: 10, UMS: Internal: 0x0,
> InternalHigh: 0xF0000, Offset: 0xEC561A00, OffsetHigh: 0x1F, m_buf:
> 0x05710000,
> m_len: 983040, m_actualBytes: 0, m_errcode: 1450,
> BackupFile: \\storage\backup\Backup_22\SD_db_2007072
91858.BAK|||On Aug 2, 6:55 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> It's probably talking about memory on the server that SQL Server is runni=
ng
> on. Why would you possibly have the need for Enterprise Edition and 4
> processors with 190GB of data but only have 2GB of memory. My laptop has
> more than that. Are you using any of the 3rd part backup & compression
> tools or is this a native SQL backup? If not I would highly recommend
> buying one of them and adding more memory.
> --
> Andrew J. Kelly SQL MVP
> "906507N" <9065...@.10090.I20034> wrote in message
> news:Xns998147FE8B8D0140507NA02619@.207.46.248.16...
>
>
>
he[vbcol=seagreen]
>
ed[vbcol=seagreen]
>
>
>
>
>
2\SD_db_[vbcol=seagreen]
)=2E[vbcol=seagreen]
ted text -[vbcol=seagreen]
> - Show quoted text -
That is a rather low amount of memory for a box with those
specifications and needs. I'm not sure if this problem is memory
related, but I've also read a lot of various posts in regards to
posting large files to a network location. Again, most of these will
give other errors than the ones you reported, but might be another
avenue to venture through.|||Try it out first with a local storage - probably a USB drive that can
accommodate 190GB and dump your backup there and see what happens
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1186105163.744059.159720@.i38g2000prf.googlegroups.com...
On Aug 2, 6:55 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> It's probably talking about memory on the server that SQL Server is
> running
> on. Why would you possibly have the need for Enterprise Edition and 4
> processors with 190GB of data but only have 2GB of memory. My laptop has
> more than that. Are you using any of the 3rd part backup & compression
> tools or is this a native SQL backup? If not I would highly recommend
> buying one of them and adding more memory.
> --
> Andrew J. Kelly SQL MVP
> "906507N" <9065...@.10090.I20034> wrote in message
> news:Xns998147FE8B8D0140507NA02619@.207.46.248.16...
>
>
>
>
>
>
>
>
>
> - Show quoted text -
That is a rather low amount of memory for a box with those
specifications and needs. I'm not sure if this problem is memory
related, but I've also read a lot of various posts in regards to
posting large files to a network location. Again, most of these will
give other errors than the ones you reported, but might be another
avenue to venture through.|||How's the space on your C: drive or wherever the OS pagefile is?
J.
On Thu, 02 Aug 2007 15:26:31 -0700, 906507N <906507N@.10090.I20034>
wrote:

>I everybody,
>I have a big (well, this is relative) db, when I do a full db backup, the
>.bak is arount 190G
>I run under windows std server 2000, 2G mem, 4 cpu,sql server 2000
>enterprise ed.
>I backup the database to a dedicated server via UNC name with a dedicated
>network card. I have not enought space on local disk
>I do a backup every day but perhaps 1 backup per week works.
>I get the following error message :
>Error 1450 is : Insufficient system resources exist to complete the
>requested service.
>what "ressource" is not enough ? this is not the disk space since the
>target server (windows 2003 R2) has 3T free space.
>hope some one has some advise.
>thanks in advance
>BackupMedium::ReportIoError :
>chec write sur l'unit de sauvegarde _\\storage\backup\Backup_22\SD_db_
>200707291858.BAK_.
>Erreur du systme d'exploitation 1450
>(Ressources systme insuffisantes pour terminer le service demand.).
>La sauvegarde n'a pas russir achever la commande
>BACKUP DATABASE [SIMD] TO DISK = N'\\storage\backup\Backup_22\SD_db_
>200707291858.BAK'
>WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>Internal I/O request 0x05702918:
>Op: Write, pBuffer: 0x05710000, Size: 983040,
>Position: 137109051904, RetryCount: 10, UMS: Internal: 0x0,
>InternalHigh: 0xF0000, Offset: 0xEC561A00, OffsetHigh: 0x1F, m_buf:
>0x05710000,
>m_len: 983040, m_actualBytes: 0, m_errcode: 1450,
>BackupFile: \\storage\backup\Backup_22\SD_db_2007072
91858.BAK

backup big db fail

I everybody,
I have a big (well, this is relative) db, when I do a full db backup, the
.bak is arount 190G
I run under windows std server 2000, 2G mem, 4 cpu,sql server 2000
enterprise ed.
I backup the database to a dedicated server via UNC name with a dedicated
network card. I have not enought space on local disk
I do a backup every day but perhaps 1 backup per week works.
I get the following error message :
Error 1450 is : Insufficient system resources exist to complete the
requested service.
what "ressource" is not enough ? this is not the disk space since the
target server (windows 2003 R2) has 3T free space.
hope some one has some advise.
thanks in advance
BackupMedium::ReportIoError :
échec write sur l'unité de sauvegarde « \\storage\backup\Backup_22\SD_db_
200707291858.BAK ».
Erreur du système d'exploitation 1450
(Ressources système insuffisantes pour terminer le service demandé.).
La sauvegarde n'a pas réussir à achever la commande
BACKUP DATABASE [SIMD] TO DISK = N'\\storage\backup\Backup_22\SD_db_
200707291858.BAK'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
Internal I/O request 0x05702918:
Op: Write, pBuffer: 0x05710000, Size: 983040,
Position: 137109051904, RetryCount: 10, UMS: Internal: 0x0,
InternalHigh: 0xF0000, Offset: 0xEC561A00, OffsetHigh: 0x1F, m_buf:
0x05710000,
m_len: 983040, m_actualBytes: 0, m_errcode: 1450,
BackupFile: \\storage\backup\Backup_22\SD_db_200707291858.BAKIt's probably talking about memory on the server that SQL Server is running
on. Why would you possibly have the need for Enterprise Edition and 4
processors with 190GB of data but only have 2GB of memory. My laptop has
more than that. Are you using any of the 3rd part backup & compression
tools or is this a native SQL backup? If not I would highly recommend
buying one of them and adding more memory.
--
Andrew J. Kelly SQL MVP
"906507N" <906507N@.10090.I20034> wrote in message
news:Xns998147FE8B8D0140507NA02619@.207.46.248.16...
>I everybody,
> I have a big (well, this is relative) db, when I do a full db backup, the
> .bak is arount 190G
> I run under windows std server 2000, 2G mem, 4 cpu,sql server 2000
> enterprise ed.
> I backup the database to a dedicated server via UNC name with a dedicated
> network card. I have not enought space on local disk
> I do a backup every day but perhaps 1 backup per week works.
> I get the following error message :
> Error 1450 is : Insufficient system resources exist to complete the
> requested service.
> what "ressource" is not enough ? this is not the disk space since the
> target server (windows 2003 R2) has 3T free space.
> hope some one has some advise.
> thanks in advance
> BackupMedium::ReportIoError :
> échec write sur l'unité de sauvegarde « \\storage\backup\Backup_22\SD_db_
> 200707291858.BAK ».
> Erreur du système d'exploitation 1450
> (Ressources système insuffisantes pour terminer le service demandé.).
> La sauvegarde n'a pas réussir à achever la commande
> BACKUP DATABASE [SIMD] TO DISK = N'\\storage\backup\Backup_22\SD_db_
> 200707291858.BAK'
> WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> Internal I/O request 0x05702918:
> Op: Write, pBuffer: 0x05710000, Size: 983040,
> Position: 137109051904, RetryCount: 10, UMS: Internal: 0x0,
> InternalHigh: 0xF0000, Offset: 0xEC561A00, OffsetHigh: 0x1F, m_buf:
> 0x05710000,
> m_len: 983040, m_actualBytes: 0, m_errcode: 1450,
> BackupFile: \\storage\backup\Backup_22\SD_db_200707291858.BAK|||On Aug 2, 6:55 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> It's probably talking about memory on the server that SQL Server is runni=ng
> on. Why would you possibly have the need for Enterprise Edition and 4
> processors with 190GB of data but only have 2GB of memory. My laptop has
> more than that. Are you using any of the 3rd part backup & compression
> tools or is this a native SQL backup? If not I would highly recommend
> buying one of them and adding more memory.
> --
> Andrew J. Kelly SQL MVP
> "906507N" <9065...@.10090.I20034> wrote in message
> news:Xns998147FE8B8D0140507NA02619@.207.46.248.16...
>
> >I everybody,
> > I have a big (well, this is relative) db, when I do a full db backup, t=he
> > .bak is arount 190G
> > I run under windows std server 2000, 2G mem, 4 cpu,sql server 2000
> > enterprise ed.
> > I backup the database to a dedicated server via UNC name with a dedicat=ed
> > network card. I have not enought space on local disk
> > I do a backup every day but perhaps 1 backup per week works.
> > I get the following error message :
> > Error 1450 is : Insufficient system resources exist to complete the
> > requested service.
> > what "ressource" is not enough ? this is not the disk space since the
> > target server (windows 2003 R2) has 3T free space.
> > hope some one has some advise.
> > thanks in advance
> > BackupMedium::ReportIoError :
> > =E9chec write sur l'unit=E9 de sauvegarde =AB \\storage\backup\Backup_2=2\SD_db_
> > 200707291858.BAK =BB.
> > Erreur du syst=E8me d'exploitation 1450
> > (Ressources syst=E8me insuffisantes pour terminer le service demand=E9.=)=2E
> > La sauvegarde n'a pas r=E9ussir =E0 achever la commande
> > BACKUP DATABASE [SIMD] TO DISK =3D N'\\storage\backup\Backup_22\SD_db_
> > 200707291858.BAK'
> > WITH INIT , NOUNLOAD , NOSKIP , STATS =3D 10, NOFORMAT
> > Internal I/O request 0x05702918:
> > Op: Write, pBuffer: 0x05710000, Size: 983040,
> > Position: 137109051904, RetryCount: 10, UMS: Internal: 0x0,
> > InternalHigh: 0xF0000, Offset: 0xEC561A00, OffsetHigh: 0x1F, m_buf:
> > 0x05710000,
> > m_len: 983040, m_actualBytes: 0, m_errcode: 1450,
> > BackupFile: \\storage\backup\Backup_22\SD_db_200707291858.BAK- Hide quo=ted text -
> - Show quoted text -
That is a rather low amount of memory for a box with those
specifications and needs. I'm not sure if this problem is memory
related, but I've also read a lot of various posts in regards to
posting large files to a network location. Again, most of these will
give other errors than the ones you reported, but might be another
avenue to venture through.|||Try it out first with a local storage - probably a USB drive that can
accommodate 190GB and dump your backup there and see what happens
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1186105163.744059.159720@.i38g2000prf.googlegroups.com...
On Aug 2, 6:55 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> It's probably talking about memory on the server that SQL Server is
> running
> on. Why would you possibly have the need for Enterprise Edition and 4
> processors with 190GB of data but only have 2GB of memory. My laptop has
> more than that. Are you using any of the 3rd part backup & compression
> tools or is this a native SQL backup? If not I would highly recommend
> buying one of them and adding more memory.
> --
> Andrew J. Kelly SQL MVP
> "906507N" <9065...@.10090.I20034> wrote in message
> news:Xns998147FE8B8D0140507NA02619@.207.46.248.16...
>
> >I everybody,
> > I have a big (well, this is relative) db, when I do a full db backup,
> > the
> > .bak is arount 190G
> > I run under windows std server 2000, 2G mem, 4 cpu,sql server 2000
> > enterprise ed.
> > I backup the database to a dedicated server via UNC name with a
> > dedicated
> > network card. I have not enought space on local disk
> > I do a backup every day but perhaps 1 backup per week works.
> > I get the following error message :
> > Error 1450 is : Insufficient system resources exist to complete the
> > requested service.
> > what "ressource" is not enough ? this is not the disk space since the
> > target server (windows 2003 R2) has 3T free space.
> > hope some one has some advise.
> > thanks in advance
> > BackupMedium::ReportIoError :
> > échec write sur l'unité de sauvegarde «
> > \\storage\backup\Backup_22\SD_db_
> > 200707291858.BAK ».
> > Erreur du système d'exploitation 1450
> > (Ressources système insuffisantes pour terminer le service demandé.).
> > La sauvegarde n'a pas réussir à achever la commande
> > BACKUP DATABASE [SIMD] TO DISK = N'\\storage\backup\Backup_22\SD_db_
> > 200707291858.BAK'
> > WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> > Internal I/O request 0x05702918:
> > Op: Write, pBuffer: 0x05710000, Size: 983040,
> > Position: 137109051904, RetryCount: 10, UMS: Internal: 0x0,
> > InternalHigh: 0xF0000, Offset: 0xEC561A00, OffsetHigh: 0x1F, m_buf:
> > 0x05710000,
> > m_len: 983040, m_actualBytes: 0, m_errcode: 1450,
> > BackupFile: \\storage\backup\Backup_22\SD_db_200707291858.BAK- Hide
> > quoted text -
> - Show quoted text -
That is a rather low amount of memory for a box with those
specifications and needs. I'm not sure if this problem is memory
related, but I've also read a lot of various posts in regards to
posting large files to a network location. Again, most of these will
give other errors than the ones you reported, but might be another
avenue to venture through.|||How's the space on your C: drive or wherever the OS pagefile is?
J.
On Thu, 02 Aug 2007 15:26:31 -0700, 906507N <906507N@.10090.I20034>
wrote:
>I everybody,
>I have a big (well, this is relative) db, when I do a full db backup, the
>.bak is arount 190G
>I run under windows std server 2000, 2G mem, 4 cpu,sql server 2000
>enterprise ed.
>I backup the database to a dedicated server via UNC name with a dedicated
>network card. I have not enought space on local disk
>I do a backup every day but perhaps 1 backup per week works.
>I get the following error message :
>Error 1450 is : Insufficient system resources exist to complete the
>requested service.
>what "ressource" is not enough ? this is not the disk space since the
>target server (windows 2003 R2) has 3T free space.
>hope some one has some advise.
>thanks in advance
>BackupMedium::ReportIoError :
>échec write sur l'unité de sauvegarde « \\storage\backup\Backup_22\SD_db_
>200707291858.BAK ».
>Erreur du système d'exploitation 1450
>(Ressources système insuffisantes pour terminer le service demandé.).
>La sauvegarde n'a pas réussir à achever la commande
>BACKUP DATABASE [SIMD] TO DISK = N'\\storage\backup\Backup_22\SD_db_
>200707291858.BAK'
>WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>Internal I/O request 0x05702918:
>Op: Write, pBuffer: 0x05710000, Size: 983040,
>Position: 137109051904, RetryCount: 10, UMS: Internal: 0x0,
>InternalHigh: 0xF0000, Offset: 0xEC561A00, OffsetHigh: 0x1F, m_buf:
>0x05710000,
>m_len: 983040, m_actualBytes: 0, m_errcode: 1450,
>BackupFile: \\storage\backup\Backup_22\SD_db_200707291858.BAK

Tuesday, March 27, 2012

backup extension is?

backup extension is?

backup extension is?

Sunday, March 25, 2012

Backup error

Thursday, March 22, 2012

Backup error

Backup error

Tuesday, March 20, 2012

Backup device creation problem

Backup Device

Backup Device

Backup Device

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 with SQL to backup directory

Sunday, March 11, 2012

Backup database creates smaller files....

Backup Database at different place

BackUp database

Thursday, March 8, 2012

backup big db fail

backup big db fail

Sql Backing up Tables

Blog Archive

About Me