Database corrupted: back

Showing posts with label back. Show all posts

Tuesday, March 27, 2012

DATABASE Becomes suspect

I have a SQL 7.0 Clustered Server with SP3. Sometimes a DB (16GB) becomes
suspect and the only way to get it back online is to reboot the server and
that gets it back to normal. This happens every week.
Any ideas?
Also can you tell me how to change the location where Microsoft Clustered
Server changes the Temporary file location. Currently on my server it is
c:\winnt which we want to change
Thanks
Rod
Hey Rod, great name!
I am of no help really, but I can tell you I run 18 SQL clusters with
several Databases way larger then 16GB and we never see this. We are running
SQL 2000 on Windows Server 2003 with various hardware. If you can migrate to
a newer config, that may solve the problem.
Now, for one of the real SQL experts to help you, do you have any log events
or error messages or stats of what is going on when this occurs? Does it
happen on all the nodes? Anything else you can give will help.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Rod" <Rod@.discussions.microsoft.com> wrote in message
news:77254067-96EE-46D3-BA60-EB9BA92C0D75@.microsoft.com...
>I have a SQL 7.0 Clustered Server with SP3. Sometimes a DB (16GB) becomes
> suspect and the only way to get it back online is to reboot the server and
> that gets it back to normal. This happens every week.
> Any ideas?
> Also can you tell me how to change the location where Microsoft Clustered
> Server changes the Temporary file location. Currently on my server it is
> c:\winnt which we want to change
> Thanks
> Rod
|||Hey Rodney,
My name is Rodney too. I agree Great Name.
Well here are some details/comments about the below issue:
Two things I saw on the event log were
1). The C drive was out of space. we were constantly getting the following
error in the system log:
" The Microsoft Cluster could write file (c:\winnt\CLSF9D.TMP). The Disk may
be low in space or some serious other condition exists "
The Qurorm Drive is the Q Drive.
My question is why is Cluster Server trying to write temp files to the
c:\winnt\ folder.
Can we change this location and if so how?
Thanks for your help
Rod
"Rodney R. Fournier [MVP]" wrote:

> Hey Rod, great name!
> I am of no help really, but I can tell you I run 18 SQL clusters with
> several Databases way larger then 16GB and we never see this. We are running
> SQL 2000 on Windows Server 2003 with various hardware. If you can migrate to
> a newer config, that may solve the problem.
> Now, for one of the real SQL experts to help you, do you have any log events
> or error messages or stats of what is going on when this occurs? Does it
> happen on all the nodes? Anything else you can give will help.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://www.msmvps.com/clustering - Blog
> "Rod" <Rod@.discussions.microsoft.com> wrote in message
> news:77254067-96EE-46D3-BA60-EB9BA92C0D75@.microsoft.com...
>
>
|||Each node has its own cluster log and temp space. Why was your C drive
running out of space? Have you fixed that yet?
I am not sure how to change the WINNT temp folder that clustering writes to.
Rod
"Rod" <Rod@.discussions.microsoft.com> wrote in message
news:84068A17-BE1E-44CB-BD06-597F33A5BE91@.microsoft.com...[vbcol=seagreen]
> Hey Rodney,
> My name is Rodney too. I agree Great Name.
> Well here are some details/comments about the below issue:
> Two things I saw on the event log were
> 1). The C drive was out of space. we were constantly getting the following
> error in the system log:
> " The Microsoft Cluster could write file (c:\winnt\CLSF9D.TMP). The Disk
> may
> be low in space or some serious other condition exists "
> The Qurorm Drive is the Q Drive.
> My question is why is Cluster Server trying to write temp files to the
> c:\winnt\ folder.
> Can we change this location and if so how?
> Thanks for your help
> Rod
> "Rodney R. Fournier [MVP]" wrote:

Sunday, March 25, 2012

Database Backups and Transaction Logs

Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks
You must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks
|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>
|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>
|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
was
>
|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set up
> that functionality as part of a job. I will add a nightly backup. We have to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> was
>

Database Backups and Transaction Logs

Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
ThanksYou must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
was[vbcol=seagreen]
>|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set u
p
> that functionality as part of a job. I will add a nightly backup. We have
to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> was
>

Database Backups and Transaction Logs

Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
ThanksYou must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> > Problem: Transaction log grew too big.
> >
> > I took my database off line and then back online to ensure that no one
was
> > connected to the database and then backed up the database.
> >
> > I figured at this point I would be able to shrink the transaction log
> > because all of the 'pending' transactions would be clear - however I
> > cannot.
> >
> > Do I have to backup the transaction log? And in so doing will this solve
> > my
> > problem?
> >
> > Thanks
> >
> >
> >
>|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set up
> that functionality as part of a job. I will add a nightly backup. We have to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
>> Stephen,
>> Production database? If so, backup the transaction log then shrink the
>> transaction log file using DBCC SHRINKFILE statment.
>> HTH
>> Jerry
>> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
>> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
>> > Problem: Transaction log grew too big.
>> >
>> > I took my database off line and then back online to ensure that no one
> was
>> > connected to the database and then backed up the database.
>> >
>> > I figured at this point I would be able to shrink the transaction log
>> > because all of the 'pending' transactions would be clear - however I
>> > cannot.
>> >
>> > Do I have to backup the transaction log? And in so doing will this solve
>> > my
>> > problem?
>> >
>> > Thanks
>> >
>> >
>> >
>>
>

database backup with encryption

Hi,

I want to backup my database at regular intervals and encrypt it. Also I want to verify the back up. How do I do it?

I want the back ups to run every fortnight.

Thanks.

For encrypting backups, you have a few different options. Some would include:

Backup or move the backup to an encrypted folder.

Write your own file encryption routine.

Use a third party backup utility that will backup and encrypt. Tools like

Red Gate's SQL Backup

http://www.red-gate.com/products/SQL_Backup/index.htm

Quest's LiteSpeed

http://www.quest.com/litespeed-for-sql-server/

In terms of verifying, that depends on what you mean by verify. You can use the restore verifyonly option on backups. That will tell you if the backup file is complete and readable by SQL Server. If you need verification beyond that, you need to test this by restoring the backup to another server (or another database name if using the same server)

-Sue

database backup using Veritas

Hi
My client wants to use veritas tape back in SQL Server 2000 Standalone and
cluster environment.
Can anyone tell disadvantages/practical issuse with veritas backup?
thanks
Kalyan
Hi Kalyan
I am using Veritas netbackup for SQL server on SQl server 2000 and has no
issues with it. It is easy to use but I would prefer SQL*LITE
which provide much better functionality than veritas. The latest version
support tape backups too.
Tks
Mangesh
"Kalyan" wrote:

> Hi
> My client wants to use veritas tape back in SQL Server 2000 Standalone and
> cluster environment.
> Can anyone tell disadvantages/practical issuse with veritas backup?
> thanks
> Kalyan
>
|||Personally from what I have experienced with VERITAS using backup tapes you
are better off using LightSpeed for backing up your SQL server because it
compresses the databases backup sizes by about 60% of what the normal backup
size would be on the hard drive. Also tape backups are not the most reliable
form of backup method in my opinon. I have used backup software and rotated
on removalable storage drives so that you can have a copy of database data
stored off location in case of a major disaster you have company livelyhood
in another location at all times.
Hope this helps. Below is the VERITAS information:
VERITAS Backup Exec 10 for Windows Small Business Server provides
exceptional value at a single price point. Best of all, the products are easy
to install and manage!
KEY BENEFITS
Complete data protection for Microsoft Windows Small Business Server 2000
and 2003
Easy to use, deploy and manage through user-friendly wizards
Low-cost, highly adaptable solution supporting backup and restore from disk
and tape devices
Intelligent Disaster Recovery reduces downtime for the local server and all
protected client systems at no extra cost
Fast backup and restore of Exchange Server, SQL Server, and SharePoint
Services
Continuous backup protection option for desktops and laptops
PRODUCT HIGHLIGHTS
ENHANCED! Cost Effective Complete Data Protection Solution – Backup Exec
software’s Small Business Server Edition provides complete data protection
for Microsoft Windows Small Business Server 2000 and 2003 in one totally
comprehensive suite. Core functionality includes database agent technology
for protecting Microsoft Exchange Server, SQL Server, and SharePoint
Services, Intelligent Disaster Recovery, and single tape drive autoloader
support.
ENHANCED! Ease of Use – Enhanced install wizard helps ensure fast and
successful installations. Web-based administration console, new policy
wizard, and the new Backup Exec assistant, enable easy management and improve
the overall customer experience.
ENHANCED! Disaster Recovery for the Entire Environment – Provides recovery
solution for media server and all systems backed up by the Backup Exec server
at no extra cost, eliminating the need to first reload the entire operating
system of crashed servers. Includes the ability to create system specific
recovery media for protected systems even AFTER a system crashes.
ENHANCED! Desktop and Laptop Option – Provides continuous disk-based
protection and synchronization for users that are in the office or on the
road. Designed to automatically copy user data to existing network shares or
storage, it enables quick recovery of data by the administrator or the user
for optimum efficiency. The new push-install functionality from within Backup
Exec streamlines deployment, and user-specific backup reports improve data
and service level visibility.
Support for Microsoft Volume Shadow Copy Services Writer – Backup Exec
Software’s Small Business Server Edition supports Microsoft’s VSS writers
simplifying the protection and recovery of Exchange and SharePoint Services
Faster, Flexible Backup and Restore Management of Online Exchange Servers –
Single Instance Storage (SIS) of email message attachments, incremental or
differential mailbox backups and individual public folder restore provide
granularity and deliver faster backup and recovery of Exchange servers. The
agent also empowers administrators to perform individual mailbox backup with
selective restore, down to an individual message.
INCLUDED AGENTS AND OPTIONS
ENHANCED! Intelligent Disaster Recovery Option Saves recovery time by
automating the traditional manual, error prone process. The Intelligent
Disaster Recovery Option automates server recovery, reducing the time to
recovery and gets you back into business fast. Implement a server recovery
solution for both local and remote Windows servers, eliminating the need to
first reload the entire operating system of crashed servers. Using either
diskette-based, CD-R/CD-RW or bootable tape, the Intelligent Disaster
Recovery Option will quickly recover downed servers and workstations enabling
restores from the last complete backup set including full, differential,
incremental, and working set backups. The Intelligent Disaster Recovery
Option integrates directly with Microsoft’s Automated System Recovery (ASR)
functionality in Windows Server 2003 and Windows XP to provide complete
disaster recovery on Windows servers. With Offline Protection functionality,
even when computer crashes before administrators create disaster recovery
media, IDR can still recover the computer using a full backup that includes
information needed to restore the system made prior to the crash.
ENHANCED! Agent for Microsoft Exchange Server Fast, flexible technology to
protect vital Exchange Server 5.5, 2000 and 2003 data while the application
is online. This agent provides full, incremental, or differential backup and
restores of embedded objects, attributes, and all Outlook components. Restore
can automatically mount the database upon completion of backups, which
ensures the valid database is brought back online quickly. Using the Single
Instance Storage (SIS) feature eliminates backup of duplicate information by
storing only the first instance of attachments distributed to multiple
mailboxes. For Microsoft Windows Exchange 2003 Server support, the mailbox or
message level restores from a full traditional backup does not require
installation of a separate Exchange 2003 server. When running on Microsoft
Windows Server 2003, the Backup Exec Agent for Exchange Server has added
support for Microsoft Exchange Server 2003 Volume Snapshot (VSS) technology
enabling the user to select either the VERITAS or the Microsoft proprietary
frozen image technology for protecting open files. The agent provides the
ability to perform consistency checks before backup when using Microsoft
Volume Shadow Copy Service.
ENHANCED! Agent for Microsoft SQL Server Ensures business- ritical databases
and e-business data are protected in the event of application or
hardware-based corruption or loss. This agent provides SQL Server 7.0 and SQL
Server 2000 users granular protection down to the individual database or file
group. Execute differential backups as well as Transaction Log backups with
automatic truncation. Restore with one-pass recovery of the last full and any
subsequent backup as a single restore job or perform “rollback restores”,
enabling a database to be recovered to a specific moment in time, rather than
a specific restore point based on the last backup job. Virtual Device
Interface (VDI) support gives users the most dependable and fastest method
available for complete SQL Server protection. The agent supports Microsoft
Volume Shadow Copy Service (VSS) snapshot technology to create point-in-time,
recovery consistent snapshots of SQL Server 2000 on Windows Server 2003.
Consistency checks can be performed before backup when using Microsoft VSS
snapshot provider.
ADDITIONAL SMALL BUSINESS SERVER AGENTS AND OPTIONS SOLD SEPARATELY ENHANCED!
Desktop and Laptop Option Most businesses rely on users to manually copy
business critical data to a server. When users do not comply, the majority of
business-critical information residing on employee workstations, desktop and
laptop does not get protected. The Desktop and Laptop Option delivers
continuous data protection to desktops and laptops whether in the office or
on the road. Not only improving data protection and efficiency, this option
enables users to restore their own files, and maintains synchronization
between multiple desktops and laptops so the most up-to-date file versions
are available on all of a user’s computers. Because the Desktop and Laptop
Option does not require a dedicated stand alone server as competing products
do, it easily integrates into existing IT infrastructure and policies,
helping lower the total cost of ownership. The new push-install functionality
from within Backup Exec centralizes deployment. This version also adds
Outlook 2003 support, reporting and console access control.
Library Expansion Option
Scale the media storage system by leveraging additional drives within
multi-drive tape or optical autoloader/library storage systems. This option
leverages Backup Exec’s Advanced Device and Media Management (ADAMM)
technology to provide extensive configuration and management capabilities to
include “lights-out” backup and restore operations, bar-code reader and
portal support for both SCSI and fibre-attached devices. Users can partition
slots within libraries to target backup jobs to a specific slot.
ENHANCED! Advanced Open File Option
Helps ensure that files on local or remote servers are protected even while
in use. The VERITAS Backup Exec? Advanced Open File Option handles open files
at the volume level and is seamlessly integrated into Backup Exec software.
There is no need to know which files are open ahead of time; just set a
scheduled backup to use this option with a simple mouse click. It can backup
multiple volumes in one job while creating a snapshot of only one logical
volume at a time. After the logical volume is snapped and backed up, the
snapshot is deleted before the next logical volume is snapped. This feature
increases the ability to meet the minimum quiet time needed to complete a
snapshot. Advanced technology also provides the ability to detect and
leverage alternate frozen image technologies such as Microsoft Volume
Snapshot Service (VSS) on Windows Server 2003 or VERITAS Storage Foundation
for Windows FlashSnap? for increased application availability.
ENHANCED! Remote Agent Client Access License (CAL) for Windows Servers
Expand network-wide data protection and optimize data transfers for 32 and
64-bit remote Windows servers, including local Registry and System State
information. Exclusive Agent Accelerator technology maximizes backup and
recovery performance by providing source-level compression and distributed
processing at the client. The result is reduced network traffic and maximized
data throughput.
ADDITIONAL BACKUP EXEC FEATURES
Backup & Restore
Support for Microsoft Windows Small Business Server 2003 Standard Edition
Support for Microsoft Windows Small Business Server 2003 Premium Edition
Support for Microsoft Windows Small Business Server 2000
Single-step protection of Server and Exchange data
Individual Mail Message Restore
Single-pass recovery of SQL Server
One-Button Backup capability
Complete Workstation Protection and Recovery
Desktop & Laptop Data Protection Option
Remote System Management capabilities
Environment check utility for the proper system configuration
CA ARCserve Tape Read Capability
Report Generation
44 Backup reports
Other Features
Include/Exclude File Selection
Onscreen Progress and Status Indicators
User Configurable Alerts
Automatic Job Retry
Scheduled Job “Test Run”
Single Drive Library Support
100% Compatible with Microsoft Tape Format
Software Compression
Hardware Compression
Backup to Disk
Specified Backup NIC
SharePoint Services Support via Volume Shadow Copy Services “Writer”
Integration
"Kalyan" wrote:

> Hi
> My client wants to use veritas tape back in SQL Server 2000 Standalone and
> cluster environment.
> Can anyone tell disadvantages/practical issuse with veritas backup?
> thanks
> Kalyan
>

database backup using Veritas

Hi
My client wants to use veritas tape back in SQL Server 2000 Standalone and
cluster environment.
Can anyone tell disadvantages/practical issuse with veritas backup?
thanks
KalyanHi Kalyan
I am using Veritas netbackup for SQL server on SQl server 2000 and has no
issues with it. It is easy to use but I would prefer SQL*LITE
which provide much better functionality than veritas. The latest version
support tape backups too.
Tks
Mangesh
"Kalyan" wrote:
> Hi
> My client wants to use veritas tape back in SQL Server 2000 Standalone and
> cluster environment.
> Can anyone tell disadvantages/practical issuse with veritas backup?
> thanks
> Kalyan
>|||Personally from what I have experienced with VERITAS using backup tapes you
are better off using LightSpeed for backing up your SQL server because it
compresses the databases backup sizes by about 60% of what the normal backup
size would be on the hard drive. Also tape backups are not the most reliable
form of backup method in my opinon. I have used backup software and rotated
on removalable storage drives so that you can have a copy of database data
stored off location in case of a major disaster you have company livelyhood
in another location at all times.
Hope this helps. Below is the VERITAS information:
---
VERITAS Backup Exec 10 for Windows Small Business Server provides
exceptional value at a single price point. Best of all, the products are easy
to install and manage!
KEY BENEFITS
Complete data protection for Microsoft Windows Small Business Server 2000
and 2003
Easy to use, deploy and manage through user-friendly wizards
Low-cost, highly adaptable solution supporting backup and restore from disk
and tape devices
Intelligent Disaster Recovery reduces downtime for the local server and all
protected client systems at no extra cost
Fast backup and restore of Exchange Server, SQL Server, and SharePoint
Services
Continuous backup protection option for desktops and laptops
PRODUCT HIGHLIGHTS
ENHANCED! Cost Effective Complete Data Protection Solution â' Backup Exec
softwareâ's Small Business Server Edition provides complete data protection
for Microsoft Windows Small Business Server 2000 and 2003 in one totally
comprehensive suite. Core functionality includes database agent technology
for protecting Microsoft Exchange Server, SQL Server, and SharePoint
Services, Intelligent Disaster Recovery, and single tape drive autoloader
support.
ENHANCED! Ease of Use â' Enhanced install wizard helps ensure fast and
successful installations. Web-based administration console, new policy
wizard, and the new Backup Exec assistant, enable easy management and improve
the overall customer experience.
ENHANCED! Disaster Recovery for the Entire Environment â' Provides recovery
solution for media server and all systems backed up by the Backup Exec server
at no extra cost, eliminating the need to first reload the entire operating
system of crashed servers. Includes the ability to create system specific
recovery media for protected systems even AFTER a system crashes.
ENHANCED! Desktop and Laptop Option â' Provides continuous disk-based
protection and synchronization for users that are in the office or on the
road. Designed to automatically copy user data to existing network shares or
storage, it enables quick recovery of data by the administrator or the user
for optimum efficiency. The new push-install functionality from within Backup
Exec streamlines deployment, and user-specific backup reports improve data
and service level visibility.
Support for Microsoft Volume Shadow Copy Services Writer â' Backup Exec
Softwareâ's Small Business Server Edition supports Microsoftâ's VSS writers
simplifying the protection and recovery of Exchange and SharePoint Services
Faster, Flexible Backup and Restore Management of Online Exchange Servers â'
Single Instance Storage (SIS) of email message attachments, incremental or
differential mailbox backups and individual public folder restore provide
granularity and deliver faster backup and recovery of Exchange servers. The
agent also empowers administrators to perform individual mailbox backup with
selective restore, down to an individual message.
INCLUDED AGENTS AND OPTIONS
ENHANCED! Intelligent Disaster Recovery Option Saves recovery time by
automating the traditional manual, error prone process. The Intelligent
Disaster Recovery Option automates server recovery, reducing the time to
recovery and gets you back into business fast. Implement a server recovery
solution for both local and remote Windows servers, eliminating the need to
first reload the entire operating system of crashed servers. Using either
diskette-based, CD-R/CD-RW or bootable tape, the Intelligent Disaster
Recovery Option will quickly recover downed servers and workstations enabling
restores from the last complete backup set including full, differential,
incremental, and working set backups. The Intelligent Disaster Recovery
Option integrates directly with Microsoftâ's Automated System Recovery (ASR)
functionality in Windows Server 2003 and Windows XP to provide complete
disaster recovery on Windows servers. With Offline Protection functionality,
even when computer crashes before administrators create disaster recovery
media, IDR can still recover the computer using a full backup that includes
information needed to restore the system made prior to the crash.
ENHANCED! Agent for Microsoft Exchange Server Fast, flexible technology to
protect vital Exchange Server 5.5, 2000 and 2003 data while the application
is online. This agent provides full, incremental, or differential backup and
restores of embedded objects, attributes, and all Outlook components. Restore
can automatically mount the database upon completion of backups, which
ensures the valid database is brought back online quickly. Using the Single
Instance Storage (SIS) feature eliminates backup of duplicate information by
storing only the first instance of attachments distributed to multiple
mailboxes. For Microsoft Windows Exchange 2003 Server support, the mailbox or
message level restores from a full traditional backup does not require
installation of a separate Exchange 2003 server. When running on Microsoft
Windows Server 2003, the Backup Exec Agent for Exchange Server has added
support for Microsoft Exchange Server 2003 Volume Snapshot (VSS) technology
enabling the user to select either the VERITAS or the Microsoft proprietary
frozen image technology for protecting open files. The agent provides the
ability to perform consistency checks before backup when using Microsoft
Volume Shadow Copy Service.
ENHANCED! Agent for Microsoft SQL Server Ensures business- ritical databases
and e-business data are protected in the event of application or
hardware-based corruption or loss. This agent provides SQL Server 7.0 and SQL
Server 2000 users granular protection down to the individual database or file
group. Execute differential backups as well as Transaction Log backups with
automatic truncation. Restore with one-pass recovery of the last full and any
subsequent backup as a single restore job or perform â'rollback restoresâ',
enabling a database to be recovered to a specific moment in time, rather than
a specific restore point based on the last backup job. Virtual Device
Interface (VDI) support gives users the most dependable and fastest method
available for complete SQL Server protection. The agent supports Microsoft
Volume Shadow Copy Service (VSS) snapshot technology to create point-in-time,
recovery consistent snapshots of SQL Server 2000 on Windows Server 2003.
Consistency checks can be performed before backup when using Microsoft VSS
snapshot provider.
ADDITIONAL SMALL BUSINESS SERVER AGENTS AND OPTIONS SOLD SEPARATELY ENHANCED!
Desktop and Laptop Option Most businesses rely on users to manually copy
business critical data to a server. When users do not comply, the majority of
business-critical information residing on employee workstations, desktop and
laptop does not get protected. The Desktop and Laptop Option delivers
continuous data protection to desktops and laptops whether in the office or
on the road. Not only improving data protection and efficiency, this option
enables users to restore their own files, and maintains synchronization
between multiple desktops and laptops so the most up-to-date file versions
are available on all of a userâ's computers. Because the Desktop and Laptop
Option does not require a dedicated stand alone server as competing products
do, it easily integrates into existing IT infrastructure and policies,
helping lower the total cost of ownership. The new push-install functionality
from within Backup Exec centralizes deployment. This version also adds
Outlook 2003 support, reporting and console access control.
Library Expansion Option
Scale the media storage system by leveraging additional drives within
multi-drive tape or optical autoloader/library storage systems. This option
leverages Backup Execâ's Advanced Device and Media Management (ADAMM)
technology to provide extensive configuration and management capabilities to
include â'lights-outâ' backup and restore operations, bar-code reader and
portal support for both SCSI and fibre-attached devices. Users can partition
slots within libraries to target backup jobs to a specific slot.
ENHANCED! Advanced Open File Option
Helps ensure that files on local or remote servers are protected even while
in use. The VERITAS Backup Execâ?¢ Advanced Open File Option handles open files
at the volume level and is seamlessly integrated into Backup Exec software.
There is no need to know which files are open ahead of time; just set a
scheduled backup to use this option with a simple mouse click. It can backup
multiple volumes in one job while creating a snapshot of only one logical
volume at a time. After the logical volume is snapped and backed up, the
snapshot is deleted before the next logical volume is snapped. This feature
increases the ability to meet the minimum quiet time needed to complete a
snapshot. Advanced technology also provides the ability to detect and
leverage alternate frozen image technologies such as Microsoft Volume
Snapshot Service (VSS) on Windows Server 2003 or VERITAS Storage Foundation
for Windows FlashSnapâ?¢ for increased application availability.
ENHANCED! Remote Agent Client Access License (CAL) for Windows Servers
Expand network-wide data protection and optimize data transfers for 32 and
64-bit remote Windows servers, including local Registry and System State
information. Exclusive Agent Accelerator technology maximizes backup and
recovery performance by providing source-level compression and distributed
processing at the client. The result is reduced network traffic and maximized
data throughput.
ADDITIONAL BACKUP EXEC FEATURES
Backup & Restore
Support for Microsoft Windows Small Business Server 2003 Standard Edition
Support for Microsoft Windows Small Business Server 2003 Premium Edition
Support for Microsoft Windows Small Business Server 2000
Single-step protection of Server and Exchange data
Individual Mail Message Restore
Single-pass recovery of SQL Server
One-Button Backup capability
Complete Workstation Protection and Recovery
Desktop & Laptop Data Protection Option
Remote System Management capabilities
Environment check utility for the proper system configuration
CA ARCserve Tape Read Capability
Report Generation
44 Backup reports
Other Features
Include/Exclude File Selection
Onscreen Progress and Status Indicators
User Configurable Alerts
Automatic Job Retry
Scheduled Job â'Test Runâ'
Single Drive Library Support
100% Compatible with Microsoft Tape Format
Software Compression
Hardware Compression
Backup to Disk
Specified Backup NIC
SharePoint Services Support via Volume Shadow Copy Services â'Writerâ'
Integration
"Kalyan" wrote:
> Hi
> My client wants to use veritas tape back in SQL Server 2000 Standalone and
> cluster environment.
> Can anyone tell disadvantages/practical issuse with veritas backup?
> thanks
> Kalyan
>sql

database backup using Veritas

Hi
My client wants to use veritas tape back in SQL Server 2000 Standalone and
cluster environment.
Can anyone tell disadvantages/practical issuse with veritas backup?
thanks
KalyanHi Kalyan
I am using Veritas netbackup for SQL server on SQl server 2000 and has no
issues with it. It is easy to use but I would prefer SQL*LITE
which provide much better functionality than veritas. The latest version
support tape backups too.
Tks
Mangesh
"Kalyan" wrote:

> Hi
> My client wants to use veritas tape back in SQL Server 2000 Standalone and
> cluster environment.
> Can anyone tell disadvantages/practical issuse with veritas backup?
> thanks
> Kalyan
>|||Personally from what I have experienced with VERITAS using backup tapes you
are better off using LightSpeed for backing up your SQL server because it
compresses the databases backup sizes by about 60% of what the normal backup
size would be on the hard drive. Also tape backups are not the most reliabl
e
form of backup method in my opinon. I have used backup software and rotated
on removalable storage drives so that you can have a copy of database data
stored off location in case of a major disaster you have company livelyhood
in another location at all times.
Hope this helps. Below is the VERITAS information:
---
VERITAS Backup Exec 10 for Windows Small Business Server provides
exceptional value at a single price point. Best of all, the products are eas
y
to install and manage!
KEY BENEFITS
Complete data protection for Microsoft Windows Small Business Server 2000
and 2003
Easy to use, deploy and manage through user-friendly wizards
Low-cost, highly adaptable solution supporting backup and restore from disk
and tape devices
Intelligent Disaster Recovery reduces downtime for the local server and all
protected client systems at no extra cost
Fast backup and restore of Exchange Server, SQL Server, and SharePoint
Services
Continuous backup protection option for desktops and laptops
PRODUCT HIGHLIGHTS
ENHANCED! Cost Effective Complete Data Protection Solution – Backup Exec
software’s Small Business Server Edition provides complete data protection
for Microsoft Windows Small Business Server 2000 and 2003 in one totally
comprehensive suite. Core functionality includes database agent technology
for protecting Microsoft Exchange Server, SQL Server, and SharePoint
Services, Intelligent Disaster Recovery, and single tape drive autoloader
support.
ENHANCED! Ease of Use – Enhanced install wizard helps ensure fast and
successful installations. Web-based administration console, new policy
wizard, and the new Backup Exec assistant, enable easy management and improv
e
the overall customer experience.
ENHANCED! Disaster Recovery for the Entire Environment – Provides recovery
solution for media server and all systems backed up by the Backup Exec serve
r
at no extra cost, eliminating the need to first reload the entire operating
system of crashed servers. Includes the ability to create system specific
recovery media for protected systems even AFTER a system crashes.
ENHANCED! Desktop and Laptop Option – Provides continuous disk-based
protection and synchronization for users that are in the office or on the
road. Designed to automatically copy user data to existing network shares or
storage, it enables quick recovery of data by the administrator or the user
for optimum efficiency. The new push-install functionality from within Backu
p
Exec streamlines deployment, and user-specific backup reports improve data
and service level visibility.
Support for Microsoft Volume Shadow Copy Services Writer – Backup Exec
Software’s Small Business Server Edition supports Microsoft’s VSS writer
s
simplifying the protection and recovery of Exchange and SharePoint Services
Faster, Flexible Backup and Restore Management of Online Exchange Servers
Single Instance Storage (SIS) of email message attachments, incremental or
differential mailbox backups and individual public folder restore provide
granularity and deliver faster backup and recovery of Exchange servers. The
agent also empowers administrators to perform individual mailbox backup with
selective restore, down to an individual message.
INCLUDED AGENTS AND OPTIONS
ENHANCED! Intelligent Disaster Recovery Option Saves recovery time by
automating the traditional manual, error prone process. The Intelligent
Disaster Recovery Option automates server recovery, reducing the time to
recovery and gets you back into business fast. Implement a server recovery
solution for both local and remote Windows servers, eliminating the need to
first reload the entire operating system of crashed servers. Using either
diskette-based, CD-R/CD-RW or bootable tape, the Intelligent Disaster
Recovery Option will quickly recover downed servers and workstations enablin
g
restores from the last complete backup set including full, differential,
incremental, and working set backups. The Intelligent Disaster Recovery
Option integrates directly with Microsoft’s Automated System Recovery (ASR
)
functionality in Windows Server 2003 and Windows XP to provide complete
disaster recovery on Windows servers. With Offline Protection functionality,
even when computer crashes before administrators create disaster recovery
media, IDR can still recover the computer using a full backup that includes
information needed to restore the system made prior to the crash.
ENHANCED! Agent for Microsoft Exchange Server Fast, flexible technology to
protect vital Exchange Server 5.5, 2000 and 2003 data while the application
is online. This agent provides full, incremental, or differential backup and
restores of embedded objects, attributes, and all Outlook components. Restor
e
can automatically mount the database upon completion of backups, which
ensures the valid database is brought back online quickly. Using the Single
Instance Storage (SIS) feature eliminates backup of duplicate information by
storing only the first instance of attachments distributed to multiple
mailboxes. For Microsoft Windows Exchange 2003 Server support, the mailbox o
r
message level restores from a full traditional backup does not require
installation of a separate Exchange 2003 server. When running on Microsoft
Windows Server 2003, the Backup Exec Agent for Exchange Server has added
support for Microsoft Exchange Server 2003 Volume Snapshot (VSS) technology
enabling the user to select either the VERITAS or the Microsoft proprietary
frozen image technology for protecting open files. The agent provides the
ability to perform consistency checks before backup when using Microsoft
Volume Shadow Copy Service.
ENHANCED! Agent for Microsoft SQL Server Ensures business- ritical databases
and e-business data are protected in the event of application or
hardware-based corruption or loss. This agent provides SQL Server 7.0 and SQ
L
Server 2000 users granular protection down to the individual database or fil
e
group. Execute differential backups as well as Transaction Log backups with
automatic truncation. Restore with one-pass recovery of the last full and an
y
subsequent backup as a single restore job or perform “rollback restores”
,
enabling a database to be recovered to a specific moment in time, rather tha
n
a specific restore point based on the last backup job. Virtual Device
Interface (VDI) support gives users the most dependable and fastest method
available for complete SQL Server protection. The agent supports Microsoft
Volume Shadow Copy Service (VSS) snapshot technology to create point-in-time
,
recovery consistent snapshots of SQL Server 2000 on Windows Server 2003.
Consistency checks can be performed before backup when using Microsoft VSS
snapshot provider.
ADDITIONAL SMALL BUSINESS SERVER AGENTS AND OPTIONS SOLD SEPARATELY ENHANCED
!
Desktop and Laptop Option Most businesses rely on users to manually copy
business critical data to a server. When users do not comply, the majority o
f
business-critical information residing on employee workstations, desktop and
laptop does not get protected. The Desktop and Laptop Option delivers
continuous data protection to desktops and laptops whether in the office or
on the road. Not only improving data protection and efficiency, this option
enables users to restore their own files, and maintains synchronization
between multiple desktops and laptops so the most up-to-date file versions
are available on all of a user’s computers. Because the Desktop and Laptop
Option does not require a dedicated stand alone server as competing products
do, it easily integrates into existing IT infrastructure and policies,
helping lower the total cost of ownership. The new push-install functionalit
y
from within Backup Exec centralizes deployment. This version also adds
Outlook 2003 support, reporting and console access control.
Library Expansion Option
Scale the media storage system by leveraging additional drives within
multi-drive tape or optical autoloader/library storage systems. This option
leverages Backup Exec’s Advanced Device and Media Management (ADAMM)
technology to provide extensive configuration and management capabilities to
include “lights-out” backup and restore operations, bar-code reader and
portal support for both SCSI and fibre-attached devices. Users can partition
slots within libraries to target backup jobs to a specific slot.
ENHANCED! Advanced Open File Option
Helps ensure that files on local or remote servers are protected even while
in use. The VERITAS Backup Exec? Advanced Open File Option handles open fi
les
at the volume level and is seamlessly integrated into Backup Exec software.
There is no need to know which files are open ahead of time; just set a
scheduled backup to use this option with a simple mouse click. It can backup
multiple volumes in one job while creating a snapshot of only one logical
volume at a time. After the logical volume is snapped and backed up, the
snapshot is deleted before the next logical volume is snapped. This feature
increases the ability to meet the minimum quiet time needed to complete a
snapshot. Advanced technology also provides the ability to detect and
leverage alternate frozen image technologies such as Microsoft Volume
Snapshot Service (VSS) on Windows Server 2003 or VERITAS Storage Foundation
for Windows FlashSnap? for increased application availability.
ENHANCED! Remote Agent Client Access License (CAL) for Windows Servers
Expand network-wide data protection and optimize data transfers for 32 and
64-bit remote Windows servers, including local Registry and System State
information. Exclusive Agent Accelerator technology maximizes backup and
recovery performance by providing source-level compression and distributed
processing at the client. The result is reduced network traffic and maximize
d
data throughput.
ADDITIONAL BACKUP EXEC FEATURES
Backup & Restore
Support for Microsoft Windows Small Business Server 2003 Standard Edition
Support for Microsoft Windows Small Business Server 2003 Premium Edition
Support for Microsoft Windows Small Business Server 2000
Single-step protection of Server and Exchange data
Individual Mail Message Restore
Single-pass recovery of SQL Server
One-Button Backup capability
Complete Workstation Protection and Recovery
Desktop & Laptop Data Protection Option
Remote System Management capabilities
Environment check utility for the proper system configuration
CA ARCserve Tape Read Capability
Report Generation
44 Backup reports
Other Features
Include/Exclude File Selection
Onscreen Progress and Status Indicators
User Configurable Alerts
Automatic Job Retry
Scheduled Job “Test Run”
Single Drive Library Support
100% Compatible with Microsoft Tape Format
Software Compression
Hardware Compression
Backup to Disk
Specified Backup NIC
SharePoint Services Support via Volume Shadow Copy Services “Writer”
Integration
"Kalyan" wrote:

> Hi
> My client wants to use veritas tape back in SQL Server 2000 Standalone and
> cluster environment.
> Can anyone tell disadvantages/practical issuse with veritas backup?
> thanks
> Kalyan
>

Thursday, March 22, 2012

database backup job quits for no apparent reason

I created a maintenance plan to back up a database as a file to another server. Full backup was specified. The job has been running for about 3 months, then quit last night (after the server was rebooted). Error is:

Message
Executed as user: xxxxx\Administrator. The command line parameters are invalid. The step failed.

not sure what to do to fix this - -

Do a profiler trace to see if it's a TSQL issue somewhere, maybe the plan was accidentally changed somehow.|||I turned on Profiler to trace database backups - now all the backups are running fine -|||This is typically caused by the account changing the password. If you change the password of the account you used to run the job, you won't see any problems until you reboot your server/restart SQL Server service as this is the only time that the new security credentials will take effect.|||the jobs were created by and are run under the domain master system administrator account|||

Please go the SQL server jobs and change user domain\Administrator to sa (SQL Administrators).

it should be resolve......

Database Backup Issue

Hi Guys
I have been working on database backups and recently I realised, when I take back up from enterprise manager and by my own scripts, these two files have differ in size like first one is MB and second one is GB.

Procedure One: I Have my own script to take backup
Checking DB Integrity
Rebuilding Table Indexes
Shrinking DB
DB Full backup

Procedure Two: Simply by right clicking on the DB using Enterprise manager.

Your advice will be appreciated.

PoisonHi
Thanks Alot .. I have resolved the issue...|||What was the resolution?|||I would bet that one of the methods was not initializing the backup file, but appending to it.|||Hi Geroge
With my script I was appending the data where as in Enterprise manager I was backing up with over write option...
Its my mistake...|||backup ... with init

Wednesday, March 21, 2012

Database Backup

Monday, March 19, 2012

Database Backup

We need to create a back up at a specific time for a client.
Now they have specified that the timings must be exact. The question I
have is this.
Say the back up takes 20 minutes to perform. Will any transactions
that take place in that 20 mins be logged in the database? Or is the
database locked for those transactions and then added to the database
when the backup has finished?
We can't unfortunately take the server off-line while it is in the
back up process.
Many Thanks in advance for your help.
MarkMark,
Go ahead. SQL Server backups are good for all committed transactions at
the time the backup completes. Note: open transactions are not included
in the backup file at the time the backup completes.
The database is not locked during the backup, so you can modify the data
in there and it will still be captured, if it is committed by the time
the backup finishes. There are some restrictions like you can't grow or
shrink the data files during a backup.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mark wrote:
> We need to create a back up at a specific time for a client.
> Now they have specified that the timings must be exact. The question I
> have is this.
> Say the back up takes 20 minutes to perform. Will any transactions
> that take place in that 20 mins be logged in the database? Or is the
> database locked for those transactions and then added to the database
> when the backup has finished?
> We can't unfortunately take the server off-line while it is in the
> back up process.
> Many Thanks in advance for your help.
> Mark|||Hi
The database dump is consistent and point in time correct at the end of the
dump as the dump always copies in the un-truncated commited log transactions.
In effect, the dump process takes the pages off the database, writes them to
the dump, without locking, and once the DB is dumped, takes the transaction
log and adds it's contents to the same file.
So, a restore is a restore of the Db and a transaction log replay.
The dump is a copy of the database at 10:20 and not at 10:00 if the dump
takes 20 minutes.
Regards
Mike
"Mark" wrote:
> We need to create a back up at a specific time for a client.
> Now they have specified that the timings must be exact. The question I
> have is this.
> Say the back up takes 20 minutes to perform. Will any transactions
> that take place in that 20 mins be logged in the database? Or is the
> database locked for those transactions and then added to the database
> when the backup has finished?
> We can't unfortunately take the server off-line while it is in the
> back up process.
> Many Thanks in advance for your help.
> Mark
>|||In addition tot he other posts (in case it isn't obvious already):
If they have a need to have a backup from a certain point in time, say 2 pm:
Do the db backup at a some time (perhaps 2pm). After the db backup, do a log backup. You can now
restore the db backup and then the log backup in which you stop at 2 pm.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <mroffey@.hotmail.com> wrote in message
news:1da0fab5.0410210430.6c03d311@.posting.google.com...
> We need to create a back up at a specific time for a client.
> Now they have specified that the timings must be exact. The question I
> have is this.
> Say the back up takes 20 minutes to perform. Will any transactions
> that take place in that 20 mins be logged in the database? Or is the
> database locked for those transactions and then added to the database
> when the backup has finished?
> We can't unfortunately take the server off-line while it is in the
> back up process.
> Many Thanks in advance for your help.
> Mark|||Does this mean that for the duration of the backup, committed data is never
flushed to the data file(s)? Thus, the longer the backup, the larger the
trx log file will grow, regardless of the recovery model?
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!|||I want to add a datetime stamp on my backup file names but what I have is not
adding to the filename.
Does anyone know how to do this?|||It would help if you gave a little more background or at least showed what
you were doing now. Here is an example of adding a datetime to a backup
device name but I don't know hot it fits your situation.
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
-- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
PRINT 'Backing up database ' + @.DBName
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
PRINT '--- '
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>I want to add a datetime stamp on my backup file names but what I have is
>not
> adding to the filename.
> Does anyone know how to do this?|||I also want to do this for the transaction log files.
"Andrew J. Kelly" wrote:
> It would help if you gave a little more background or at least showed what
> you were doing now. Here is an example of adding a datetime to a backup
> device name but I don't know hot it fits your situation.
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
>
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> -- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
>
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
> SET @.Name = @.DBName + N' Full Backup'
> PRINT 'Backing up database ' + @.DBName
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> PRINT '--- '
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
> >I want to add a datetime stamp on my backup file names but what I have is
> >not
> > adding to the filename.
> >
> > Does anyone know how to do this?
>
>|||Sorry. Currently I am performing complete backups to a network drive. I want
to modify the backups by appending the date and time stamp to the backup
filenames. Then run either a batch file or DTS package to clean-up the folder
by deleting SQL backups after 7 days.
"Andrew J. Kelly" wrote:
> It would help if you gave a little more background or at least showed what
> you were doing now. Here is an example of adding a datetime to a backup
> device name but I don't know hot it fits your situation.
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
>
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> -- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
>
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
> SET @.Name = @.DBName + N' Full Backup'
> PRINT 'Backing up database ' + @.DBName
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> PRINT '--- '
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
> >I want to add a datetime stamp on my backup file names but what I have is
> >not
> > adding to the filename.
> >
> > Does anyone know how to do this?
>
>|||Andrew,
Thanks worked. How can I add the time to it as well. I am going to create a
DTS package to run this query.
"Andrew J. Kelly" wrote:
> It would help if you gave a little more background or at least showed what
> you were doing now. Here is an example of adding a datetime to a backup
> device name but I don't know hot it fits your situation.
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
>
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> -- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
>
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
> SET @.Name = @.DBName + N' Full Backup'
> PRINT 'Backing up database ' + @.DBName
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> PRINT '--- '
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
> >I want to add a datetime stamp on my backup file names but what I have is
> >not
> > adding to the filename.
> >
> > Does anyone know how to do this?
>
>|||Just change the CONVERT to use an different format and make the variable
large enough to handle the extra characters. You will have to look at
CONVERT in BOL to see which suites your requirement best. The only thing is
that when you add the time portion you usually have to use a format that
inserts dashs or slashes. See if this works for you:
LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
GETDATE(),120),'-',''),':',''),' ',''),12)
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
> Andrew,
> Thanks worked. How can I add the time to it as well. I am going to create
> a
> DTS package to run this query.
> "Andrew J. Kelly" wrote:
>> It would help if you gave a little more background or at least showed
>> what
>> you were doing now. Here is an example of adding a datetime to a backup
>> device name but I don't know hot it fits your situation.
>> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
>>
>> DECLARE cur_DBs CURSOR STATIC LOCAL
>> FOR SELECT Catalog_Name
>> FROM Information_Schema.Schemata
>> -- WHERE [Catalog_Name] NOT IN
>> ('MASTER','MODEL','MSDB','TEMPDB')
>> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
>>
>> OPEN cur_DBs
>> FETCH NEXT FROM cur_DBs INTO @.DBName
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
>> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
>> SET @.Name = @.DBName + N' Full Backup'
>> PRINT 'Backing up database ' + @.DBName
>> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
>> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
>> PRINT '--- '
>> FETCH NEXT FROM cur_DBs INTO @.DBName
>> END
>> CLOSE cur_DBs
>> DEALLOCATE cur_DBs
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>> >I want to add a datetime stamp on my backup file names but what I have
>> >is
>> >not
>> > adding to the filename.
>> >
>> > Does anyone know how to do this?
>>|||That worked thank you.
"Andrew J. Kelly" wrote:
> Just change the CONVERT to use an different format and make the variable
> large enough to handle the extra characters. You will have to look at
> CONVERT in BOL to see which suites your requirement best. The only thing is
> that when you add the time portion you usually have to use a format that
> inserts dashs or slashes. See if this works for you:
> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
> GETDATE(),120),'-',''),':',''),' ',''),12)
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
> > Andrew,
> >
> > Thanks worked. How can I add the time to it as well. I am going to create
> > a
> > DTS package to run this query.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> It would help if you gave a little more background or at least showed
> >> what
> >> you were doing now. Here is an example of adding a datetime to a backup
> >> device name but I don't know hot it fits your situation.
> >>
> >> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
> >>
> >>
> >> DECLARE cur_DBs CURSOR STATIC LOCAL
> >> FOR SELECT Catalog_Name
> >> FROM Information_Schema.Schemata
> >> -- WHERE [Catalog_Name] NOT IN
> >> ('MASTER','MODEL','MSDB','TEMPDB')
> >> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
> >>
> >>
> >> OPEN cur_DBs
> >> FETCH NEXT FROM cur_DBs INTO @.DBName
> >>
> >> WHILE @.@.FETCH_STATUS = 0
> >> BEGIN
> >>
> >> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
> >> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
> >>
> >> SET @.Name = @.DBName + N' Full Backup'
> >>
> >> PRINT 'Backing up database ' + @.DBName
> >>
> >> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
> >> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
> >>
> >> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> >>
> >> PRINT '--- '
> >>
> >> FETCH NEXT FROM cur_DBs INTO @.DBName
> >> END
> >>
> >> CLOSE cur_DBs
> >> DEALLOCATE cur_DBs
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
> >> >I want to add a datetime stamp on my backup file names but what I have
> >> >is
> >> >not
> >> > adding to the filename.
> >> >
> >> > Does anyone know how to do this?
> >>
> >>
> >>
>
>|||Andrew,
Do you know how to access a network drive within Enterprise Mgr?
I'm trying to restore a db where the backups are located on the network. I
can see other network drives but cannot view the I need. I've already checked
the permissions and made sure the drive was shared. Do you have any
suggestions?
"Andrew J. Kelly" wrote:
> Just change the CONVERT to use an different format and make the variable
> large enough to handle the extra characters. You will have to look at
> CONVERT in BOL to see which suites your requirement best. The only thing is
> that when you add the time portion you usually have to use a format that
> inserts dashs or slashes. See if this works for you:
> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
> GETDATE(),120),'-',''),':',''),' ',''),12)
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
> > Andrew,
> >
> > Thanks worked. How can I add the time to it as well. I am going to create
> > a
> > DTS package to run this query.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> It would help if you gave a little more background or at least showed
> >> what
> >> you were doing now. Here is an example of adding a datetime to a backup
> >> device name but I don't know hot it fits your situation.
> >>
> >> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
> >>
> >>
> >> DECLARE cur_DBs CURSOR STATIC LOCAL
> >> FOR SELECT Catalog_Name
> >> FROM Information_Schema.Schemata
> >> -- WHERE [Catalog_Name] NOT IN
> >> ('MASTER','MODEL','MSDB','TEMPDB')
> >> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
> >>
> >>
> >> OPEN cur_DBs
> >> FETCH NEXT FROM cur_DBs INTO @.DBName
> >>
> >> WHILE @.@.FETCH_STATUS = 0
> >> BEGIN
> >>
> >> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
> >> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
> >>
> >> SET @.Name = @.DBName + N' Full Backup'
> >>
> >> PRINT 'Backing up database ' + @.DBName
> >>
> >> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
> >> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
> >>
> >> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> >>
> >> PRINT '--- '
> >>
> >> FETCH NEXT FROM cur_DBs INTO @.DBName
> >> END
> >>
> >> CLOSE cur_DBs
> >> DEALLOCATE cur_DBs
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
> >> >I want to add a datetime stamp on my backup file names but what I have
> >> >is
> >> >not
> >> > adding to the filename.
> >> >
> >> > Does anyone know how to do this?
> >>
> >>
> >>
>
>|||Don't use EM, use a script instead. EM has some limitations and things like
this can be much more controlled through scripts. The restore syntax is
pretty simple and there are examples in BOL. If you have troubles with it
post your script and we can help.
--
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:B202FDF2-5301-4E8E-9558-F2E0505F563C@.microsoft.com...
> Andrew,
> Do you know how to access a network drive within Enterprise Mgr?
> I'm trying to restore a db where the backups are located on the network. I
> can see other network drives but cannot view the I need. I've already
> checked
> the permissions and made sure the drive was shared. Do you have any
> suggestions?
> "Andrew J. Kelly" wrote:
>> Just change the CONVERT to use an different format and make the variable
>> large enough to handle the extra characters. You will have to look at
>> CONVERT in BOL to see which suites your requirement best. The only thing
>> is
>> that when you add the time portion you usually have to use a format that
>> inserts dashs or slashes. See if this works for you:
>> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
>> GETDATE(),120),'-',''),':',''),' ',''),12)
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
>> > Andrew,
>> >
>> > Thanks worked. How can I add the time to it as well. I am going to
>> > create
>> > a
>> > DTS package to run this query.
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> It would help if you gave a little more background or at least showed
>> >> what
>> >> you were doing now. Here is an example of adding a datetime to a
>> >> backup
>> >> device name but I don't know hot it fits your situation.
>> >>
>> >> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name
>> >> NVARCHAR(150)
>> >>
>> >>
>> >> DECLARE cur_DBs CURSOR STATIC LOCAL
>> >> FOR SELECT Catalog_Name
>> >> FROM Information_Schema.Schemata
>> >> -- WHERE [Catalog_Name] NOT IN
>> >> ('MASTER','MODEL','MSDB','TEMPDB')
>> >> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
>> >>
>> >>
>> >> OPEN cur_DBs
>> >> FETCH NEXT FROM cur_DBs INTO @.DBName
>> >>
>> >> WHILE @.@.FETCH_STATUS = 0
>> >> BEGIN
>> >>
>> >> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
>> >> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
>> >>
>> >> SET @.Name = @.DBName + N' Full Backup'
>> >>
>> >> PRINT 'Backing up database ' + @.DBName
>> >>
>> >> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
>> >> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>> >>
>> >> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
>> >>
>> >> PRINT '--- '
>> >>
>> >> FETCH NEXT FROM cur_DBs INTO @.DBName
>> >> END
>> >>
>> >> CLOSE cur_DBs
>> >> DEALLOCATE cur_DBs
>> >>
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>> >> >I want to add a datetime stamp on my backup file names but what I
>> >> >have
>> >> >is
>> >> >not
>> >> > adding to the filename.
>> >> >
>> >> > Does anyone know how to do this?
>> >>
>> >>
>> >>
>>|||Andrew or anyone,
I'm trying to write a short sql stmts to attached to a folder and query that
folder for *.bak and if the*.bak is older than a certain amount of days, then
I want to delete them.
Is this possible using tsql?
"Andrew J. Kelly" wrote:
> Don't use EM, use a script instead. EM has some limitations and things like
> this can be much more controlled through scripts. The restore syntax is
> pretty simple and there are examples in BOL. If you have troubles with it
> post your script and we can help.
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:B202FDF2-5301-4E8E-9558-F2E0505F563C@.microsoft.com...
> > Andrew,
> >
> > Do you know how to access a network drive within Enterprise Mgr?
> >
> > I'm trying to restore a db where the backups are located on the network. I
> > can see other network drives but cannot view the I need. I've already
> > checked
> > the permissions and made sure the drive was shared. Do you have any
> > suggestions?
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Just change the CONVERT to use an different format and make the variable
> >> large enough to handle the extra characters. You will have to look at
> >> CONVERT in BOL to see which suites your requirement best. The only thing
> >> is
> >> that when you add the time portion you usually have to use a format that
> >> inserts dashs or slashes. See if this works for you:
> >>
> >> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
> >> GETDATE(),120),'-',''),':',''),' ',''),12)
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
> >> > Andrew,
> >> >
> >> > Thanks worked. How can I add the time to it as well. I am going to
> >> > create
> >> > a
> >> > DTS package to run this query.
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> It would help if you gave a little more background or at least showed
> >> >> what
> >> >> you were doing now. Here is an example of adding a datetime to a
> >> >> backup
> >> >> device name but I don't know hot it fits your situation.
> >> >>
> >> >> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name
> >> >> NVARCHAR(150)
> >> >>
> >> >>
> >> >> DECLARE cur_DBs CURSOR STATIC LOCAL
> >> >> FOR SELECT Catalog_Name
> >> >> FROM Information_Schema.Schemata
> >> >> -- WHERE [Catalog_Name] NOT IN
> >> >> ('MASTER','MODEL','MSDB','TEMPDB')
> >> >> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
> >> >>
> >> >>
> >> >> OPEN cur_DBs
> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
> >> >>
> >> >> WHILE @.@.FETCH_STATUS = 0
> >> >> BEGIN
> >> >>
> >> >> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
> >> >> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
> >> >>
> >> >> SET @.Name = @.DBName + N' Full Backup'
> >> >>
> >> >> PRINT 'Backing up database ' + @.DBName
> >> >>
> >> >> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
> >> >> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
> >> >>
> >> >> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> >> >>
> >> >> PRINT '--- '
> >> >>
> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
> >> >> END
> >> >>
> >> >> CLOSE cur_DBs
> >> >> DEALLOCATE cur_DBs
> >> >>
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >>
> >> >>
> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> >> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
> >> >> >I want to add a datetime stamp on my backup file names but what I
> >> >> >have
> >> >> >is
> >> >> >not
> >> >> > adding to the filename.
> >> >> >
> >> >> > Does anyone know how to do this?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||You can't do this strictly with TSQL but this is as close as you will get.
There is a sample sp that will delete old log file backups based on the
timestamp in the name of the files.
-- Removing Older Backup Files --
-- Remove any log files older than 7 days
DECLARE @.Error INT, @.D DATETIME
SET @.D = DATEADD(dd,-7,GETDATE())
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
--
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
-- Used later when we cast strings to Datetimes
SET DATEFORMAT MDY
-- Create a table to hold the results of the DIR command
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
-- Create a table to hold any errors
IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
DROP TABLE #Errors
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec @.Return = master..xp_cmdshell 'dir /OD C:\Data\Backups\*.Bak'
SET @.Error = @.@.ERROR
IF @.Error <> 0 OR @.Return <> 0
BEGIN
IF @.Return = 1
SET @.Error = -1
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL OR
FName LIKE '%<DIR>%'
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.BAK'
ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec @.Return = master..xp_cmdshell @.Delete
IF @.@.RowCount > 1 OR @.Return = 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
> Andrew or anyone,
> I'm trying to write a short sql stmts to attached to a folder and query
> that
> folder for *.bak and if the*.bak is older than a certain amount of days,
> then
> I want to delete them.
> Is this possible using tsql?
> "Andrew J. Kelly" wrote:
>> Don't use EM, use a script instead. EM has some limitations and things
>> like
>> this can be much more controlled through scripts. The restore syntax is
>> pretty simple and there are examples in BOL. If you have troubles with it
>> post your script and we can help.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> news:B202FDF2-5301-4E8E-9558-F2E0505F563C@.microsoft.com...
>> > Andrew,
>> >
>> > Do you know how to access a network drive within Enterprise Mgr?
>> >
>> > I'm trying to restore a db where the backups are located on the
>> > network. I
>> > can see other network drives but cannot view the I need. I've already
>> > checked
>> > the permissions and made sure the drive was shared. Do you have any
>> > suggestions?
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Just change the CONVERT to use an different format and make the
>> >> variable
>> >> large enough to handle the extra characters. You will have to look at
>> >> CONVERT in BOL to see which suites your requirement best. The only
>> >> thing
>> >> is
>> >> that when you add the time portion you usually have to use a format
>> >> that
>> >> inserts dashs or slashes. See if this works for you:
>> >>
>> >> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
>> >> GETDATE(),120),'-',''),':',''),' ',''),12)
>> >>
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
>> >> > Andrew,
>> >> >
>> >> > Thanks worked. How can I add the time to it as well. I am going to
>> >> > create
>> >> > a
>> >> > DTS package to run this query.
>> >> >
>> >> > "Andrew J. Kelly" wrote:
>> >> >
>> >> >> It would help if you gave a little more background or at least
>> >> >> showed
>> >> >> what
>> >> >> you were doing now. Here is an example of adding a datetime to a
>> >> >> backup
>> >> >> device name but I don't know hot it fits your situation.
>> >> >>
>> >> >> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name
>> >> >> NVARCHAR(150)
>> >> >>
>> >> >>
>> >> >> DECLARE cur_DBs CURSOR STATIC LOCAL
>> >> >> FOR SELECT Catalog_Name
>> >> >> FROM Information_Schema.Schemata
>> >> >> -- WHERE [Catalog_Name] NOT IN
>> >> >> ('MASTER','MODEL','MSDB','TEMPDB')
>> >> >> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
>> >> >>
>> >> >>
>> >> >> OPEN cur_DBs
>> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
>> >> >>
>> >> >> WHILE @.@.FETCH_STATUS = 0
>> >> >> BEGIN
>> >> >>
>> >> >> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
>> >> >> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
>> >> >>
>> >> >> SET @.Name = @.DBName + N' Full Backup'
>> >> >>
>> >> >> PRINT 'Backing up database ' + @.DBName
>> >> >>
>> >> >> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT ,
>> >> >> NOUNLOAD ,
>> >> >> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>> >> >>
>> >> >> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
>> >> >>
>> >> >> PRINT '--- '
>> >> >>
>> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
>> >> >> END
>> >> >>
>> >> >> CLOSE cur_DBs
>> >> >> DEALLOCATE cur_DBs
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Andrew J. Kelly SQL MVP
>> >> >>
>> >> >>
>> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> >> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>> >> >> >I want to add a datetime stamp on my backup file names but what I
>> >> >> >have
>> >> >> >is
>> >> >> >not
>> >> >> > adding to the filename.
>> >> >> >
>> >> >> > Does anyone know how to do this?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Thanks.. I'm going through the code and I'm running into an error when you
use the directory list command. I'm not able to view the temporary table to
see if data is being stored.
"Andrew J. Kelly" wrote:
> You can't do this strictly with TSQL but this is as close as you will get.
> There is a sample sp that will delete old log file backups based on the
> timestamp in the name of the files.
> -- Removing Older Backup Files --
> -- Remove any log files older than 7 days
> DECLARE @.Error INT, @.D DATETIME
> SET @.D = DATEADD(dd,-7,GETDATE())
> EXEC @.Error = remove_old_log_files @.D
>
> SELECT @.Error
> --
> CREATE PROCEDURE remove_old_log_files
> @.DelDate DATETIME
> AS
> SET NOCOUNT ON
> DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
> DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
> -- Used later when we cast strings to Datetimes
> SET DATEFORMAT MDY
> -- Create a table to hold the results of the DIR command
> IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
> DROP TABLE #DirList
> CREATE TABLE #dirlist (FName VARCHAR(1000))
> -- Create a table to hold any errors
> IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
> DROP TABLE #Errors
> CREATE TABLE #Errors (Results VARCHAR(1000))
> -- Insert the results of the dir cmd into a table so we can scan it
> INSERT INTO #dirlist (FName)
> exec @.Return = master..xp_cmdshell 'dir /OD C:\Data\Backups\*.Bak'
> SET @.Error = @.@.ERROR
> IF @.Error <> 0 OR @.Return <> 0
> BEGIN
> IF @.Return = 1
> SET @.Error = -1
> SET @.Msg = 'Error while getting the filenames with DIR '
> GOTO On_Error
> END
> -- Remove the garbage
> DELETE #dirlist WHERE
> SUBSTRING(FName,1,2) < '00' OR
> SUBSTRING(FName,1,2) > '99' OR
> FName IS NULL OR
> FName LIKE '%<DIR>%'
>
> -- Create a cursor and for each file name do the processing.
> -- The files will be processed in date order.
> DECLARE curDir CURSOR READ_ONLY LOCAL
> FOR
> SELECT SUBSTRING(FName,40,40) AS FName
> FROM #dirlist
> WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
> AND SUBSTRING(FName,40,40) LIKE '%.BAK'
> ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
> OPEN curDir
> FETCH NEXT FROM curDir INTO @.Fname
> WHILE (@.@.fetch_status = 0)
> BEGIN
>
> -- Delete the old backup files
> SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
> INSERT INTO #Errors (Results)
> exec @.Return = master..xp_cmdshell @.Delete
>
> IF @.@.RowCount > 1 OR @.Return = 1
> BEGIN
> SET @.Error = -1
> SET @.Msg = 'Error while Deleting file ' + @.FName
> GOTO On_Error
> END
> PRINT 'Deleted ' + @.FName + ' at ' +
> CONVERT(VARCHAR(28),GETDATE(),113)
> FETCH NEXT FROM curDir INTO @.Fname
> END
> CLOSE curDir
> DEALLOCATE curDir
> DROP TABLE #DirList
> DROP TABLE #Errors
> RETURN @.Error
> On_Error:
> BEGIN
> IF @.Error <> 0
> BEGIN
> SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
> RAISERROR(@.Msg,12,1)
> RETURN @.Error
> END
> END
> GO
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
> > Andrew or anyone,
> >
> > I'm trying to write a short sql stmts to attached to a folder and query
> > that
> > folder for *.bak and if the*.bak is older than a certain amount of days,
> > then
> > I want to delete them.
> >
> > Is this possible using tsql?
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Don't use EM, use a script instead. EM has some limitations and things
> >> like
> >> this can be much more controlled through scripts. The restore syntax is
> >> pretty simple and there are examples in BOL. If you have troubles with it
> >> post your script and we can help.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> news:B202FDF2-5301-4E8E-9558-F2E0505F563C@.microsoft.com...
> >> > Andrew,
> >> >
> >> > Do you know how to access a network drive within Enterprise Mgr?
> >> >
> >> > I'm trying to restore a db where the backups are located on the
> >> > network. I
> >> > can see other network drives but cannot view the I need. I've already
> >> > checked
> >> > the permissions and made sure the drive was shared. Do you have any
> >> > suggestions?
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> Just change the CONVERT to use an different format and make the
> >> >> variable
> >> >> large enough to handle the extra characters. You will have to look at
> >> >> CONVERT in BOL to see which suites your requirement best. The only
> >> >> thing
> >> >> is
> >> >> that when you add the time portion you usually have to use a format
> >> >> that
> >> >> inserts dashs or slashes. See if this works for you:
> >> >>
> >> >> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
> >> >> GETDATE(),120),'-',''),':',''),' ',''),12)
> >> >>
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >>
> >> >>
> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> >> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
> >> >> > Andrew,
> >> >> >
> >> >> > Thanks worked. How can I add the time to it as well. I am going to
> >> >> > create
> >> >> > a
> >> >> > DTS package to run this query.
> >> >> >
> >> >> > "Andrew J. Kelly" wrote:
> >> >> >
> >> >> >> It would help if you gave a little more background or at least
> >> >> >> showed
> >> >> >> what
> >> >> >> you were doing now. Here is an example of adding a datetime to a
> >> >> >> backup
> >> >> >> device name but I don't know hot it fits your situation.
> >> >> >>
> >> >> >> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name
> >> >> >> NVARCHAR(150)
> >> >> >>
> >> >> >>
> >> >> >> DECLARE cur_DBs CURSOR STATIC LOCAL
> >> >> >> FOR SELECT Catalog_Name
> >> >> >> FROM Information_Schema.Schemata
> >> >> >> -- WHERE [Catalog_Name] NOT IN
> >> >> >> ('MASTER','MODEL','MSDB','TEMPDB')
> >> >> >> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
> >> >> >>
> >> >> >>
> >> >> >> OPEN cur_DBs
> >> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
> >> >> >>
> >> >> >> WHILE @.@.FETCH_STATUS = 0
> >> >> >> BEGIN
> >> >> >>
> >> >> >> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
> >> >> >> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
> >> >> >>
> >> >> >> SET @.Name = @.DBName + N' Full Backup'
> >> >> >>
> >> >> >> PRINT 'Backing up database ' + @.DBName
> >> >> >>
> >> >> >> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT ,
> >> >> >> NOUNLOAD ,
> >> >> >> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
> >> >> >>
> >> >> >> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> >> >> >>
> >> >> >> PRINT '--- '
> >> >> >>
> >> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
> >> >> >> END
> >> >> >>
> >> >> >> CLOSE cur_DBs
> >> >> >> DEALLOCATE cur_DBs
> >> >> >>
> >> >> >>
> >> >> >> --
> >> >> >> Andrew J. Kelly SQL MVP
> >> >> >>
> >> >> >>
> >> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> >> >> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
> >> >> >> >I want to add a datetime stamp on my backup file names but what I
> >> >> >> >have
> >> >> >> >is
> >> >> >> >not
> >> >> >> > adding to the filename.
> >> >> >> >
> >> >> >> > Does anyone know how to do this?
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Do you mean this line: INSERT INTO #dirlist (FName)
exec @.Return = master..xp_cmdshell 'dir /OD C:\Data\Backups\*.Bak'
Can you be so kind as to list exactly what the error is that you are
getting? I don't understand why you can't see this table? Are you running
the select in the same connection as the one that created the table? If not
you won't be able to see it since it is a local temporary table. You really
need to be a little more specific on what you are doing and what the real
errors are that you are getting otherwise I am simply guessing.
--
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:61C4CAE3-8398-4905-AE4C-411D6A55DA12@.microsoft.com...
> Thanks.. I'm going through the code and I'm running into an error when you
> use the directory list command. I'm not able to view the temporary table
> to
> see if data is being stored.
> "Andrew J. Kelly" wrote:
>> You can't do this strictly with TSQL but this is as close as you will
>> get.
>> There is a sample sp that will delete old log file backups based on the
>> timestamp in the name of the files.
>> -- Removing Older Backup Files --
>> -- Remove any log files older than 7 days
>> DECLARE @.Error INT, @.D DATETIME
>> SET @.D = DATEADD(dd,-7,GETDATE())
>> EXEC @.Error = remove_old_log_files @.D
>>
>> SELECT @.Error
>> --
>> CREATE PROCEDURE remove_old_log_files
>> @.DelDate DATETIME
>> AS
>> SET NOCOUNT ON
>> DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
>> DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
>> -- Used later when we cast strings to Datetimes
>> SET DATEFORMAT MDY
>> -- Create a table to hold the results of the DIR command
>> IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
>> DROP TABLE #DirList
>> CREATE TABLE #dirlist (FName VARCHAR(1000))
>> -- Create a table to hold any errors
>> IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
>> DROP TABLE #Errors
>> CREATE TABLE #Errors (Results VARCHAR(1000))
>> -- Insert the results of the dir cmd into a table so we can scan it
>> INSERT INTO #dirlist (FName)
>> exec @.Return = master..xp_cmdshell 'dir /OD
>> C:\Data\Backups\*.Bak'
>> SET @.Error = @.@.ERROR
>> IF @.Error <> 0 OR @.Return <> 0
>> BEGIN
>> IF @.Return = 1
>> SET @.Error = -1
>> SET @.Msg = 'Error while getting the filenames with DIR '
>> GOTO On_Error
>> END
>> -- Remove the garbage
>> DELETE #dirlist WHERE
>> SUBSTRING(FName,1,2) < '00' OR
>> SUBSTRING(FName,1,2) > '99' OR
>> FName IS NULL OR
>> FName LIKE '%<DIR>%'
>>
>> -- Create a cursor and for each file name do the processing.
>> -- The files will be processed in date order.
>> DECLARE curDir CURSOR READ_ONLY LOCAL
>> FOR
>> SELECT SUBSTRING(FName,40,40) AS FName
>> FROM #dirlist
>> WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
>> AND SUBSTRING(FName,40,40) LIKE '%.BAK'
>> ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
>> OPEN curDir
>> FETCH NEXT FROM curDir INTO @.Fname
>> WHILE (@.@.fetch_status = 0)
>> BEGIN
>>
>> -- Delete the old backup files
>> SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
>> INSERT INTO #Errors (Results)
>> exec @.Return = master..xp_cmdshell @.Delete
>>
>> IF @.@.RowCount > 1 OR @.Return = 1
>> BEGIN
>> SET @.Error = -1
>> SET @.Msg = 'Error while Deleting file ' + @.FName
>> GOTO On_Error
>> END
>> PRINT 'Deleted ' + @.FName + ' at ' +
>> CONVERT(VARCHAR(28),GETDATE(),113)
>> FETCH NEXT FROM curDir INTO @.Fname
>> END
>> CLOSE curDir
>> DEALLOCATE curDir
>> DROP TABLE #DirList
>> DROP TABLE #Errors
>> RETURN @.Error
>> On_Error:
>> BEGIN
>> IF @.Error <> 0
>> BEGIN
>> SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
>> RAISERROR(@.Msg,12,1)
>> RETURN @.Error
>> END
>> END
>> GO
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
>> > Andrew or anyone,
>> >
>> > I'm trying to write a short sql stmts to attached to a folder and query
>> > that
>> > folder for *.bak and if the*.bak is older than a certain amount of
>> > days,
>> > then
>> > I want to delete them.
>> >
>> > Is this possible using tsql?
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Don't use EM, use a script instead. EM has some limitations and
>> >> things
>> >> like
>> >> this can be much more controlled through scripts. The restore syntax
>> >> is
>> >> pretty simple and there are examples in BOL. If you have troubles with
>> >> it
>> >> post your script and we can help.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> news:B202FDF2-5301-4E8E-9558-F2E0505F563C@.microsoft.com...
>> >> > Andrew,
>> >> >
>> >> > Do you know how to access a network drive within Enterprise Mgr?
>> >> >
>> >> > I'm trying to restore a db where the backups are located on the
>> >> > network. I
>> >> > can see other network drives but cannot view the I need. I've
>> >> > already
>> >> > checked
>> >> > the permissions and made sure the drive was shared. Do you have any
>> >> > suggestions?
>> >> >
>> >> > "Andrew J. Kelly" wrote:
>> >> >
>> >> >> Just change the CONVERT to use an different format and make the
>> >> >> variable
>> >> >> large enough to handle the extra characters. You will have to look
>> >> >> at
>> >> >> CONVERT in BOL to see which suites your requirement best. The only
>> >> >> thing
>> >> >> is
>> >> >> that when you add the time portion you usually have to use a format
>> >> >> that
>> >> >> inserts dashs or slashes. See if this works for you:
>> >> >>
>> >> >> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
>> >> >> GETDATE(),120),'-',''),':',''),' ',''),12)
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Andrew J. Kelly SQL MVP
>> >> >>
>> >> >>
>> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> >> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
>> >> >> > Andrew,
>> >> >> >
>> >> >> > Thanks worked. How can I add the time to it as well. I am going
>> >> >> > to
>> >> >> > create
>> >> >> > a
>> >> >> > DTS package to run this query.
>> >> >> >
>> >> >> > "Andrew J. Kelly" wrote:
>> >> >> >
>> >> >> >> It would help if you gave a little more background or at least
>> >> >> >> showed
>> >> >> >> what
>> >> >> >> you were doing now. Here is an example of adding a datetime to
>> >> >> >> a
>> >> >> >> backup
>> >> >> >> device name but I don't know hot it fits your situation.
>> >> >> >>
>> >> >> >> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name
>> >> >> >> NVARCHAR(150)
>> >> >> >>
>> >> >> >>
>> >> >> >> DECLARE cur_DBs CURSOR STATIC LOCAL
>> >> >> >> FOR SELECT Catalog_Name
>> >> >> >> FROM Information_Schema.Schemata
>> >> >> >> -- WHERE [Catalog_Name] NOT IN
>> >> >> >> ('MASTER','MODEL','MSDB','TEMPDB')
>> >> >> >> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
>> >> >> >>
>> >> >> >>
>> >> >> >> OPEN cur_DBs
>> >> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
>> >> >> >>
>> >> >> >> WHILE @.@.FETCH_STATUS = 0
>> >> >> >> BEGIN
>> >> >> >>
>> >> >> >> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
>> >> >> >> CONVERT(NVARCHAR(16),GETDATE(),112) +
>> >> >> >> N'.BAK'
>> >> >> >>
>> >> >> >> SET @.Name = @.DBName + N' Full Backup'
>> >> >> >>
>> >> >> >> PRINT 'Backing up database ' + @.DBName
>> >> >> >>
>> >> >> >> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT ,
>> >> >> >> NOUNLOAD ,
>> >> >> >> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>> >> >> >>
>> >> >> >> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
>> >> >> >>
>> >> >> >> PRINT '--- '
>> >> >> >>
>> >> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
>> >> >> >> END
>> >> >> >>
>> >> >> >> CLOSE cur_DBs
>> >> >> >> DEALLOCATE cur_DBs
>> >> >> >>
>> >> >> >>
>> >> >> >> --
>> >> >> >> Andrew J. Kelly SQL MVP
>> >> >> >>
>> >> >> >>
>> >> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> >> >> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>> >> >> >> >I want to add a datetime stamp on my backup file names but what
>> >> >> >> >I
>> >> >> >> >have
>> >> >> >> >is
>> >> >> >> >not
>> >> >> >> > adding to the filename.
>> >> >> >> >
>> >> >> >> > Does anyone know how to do this?
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||When I try to query the temp table within the same connection, it says the
object is invalid. So I'm not sure the script is working because I can't view
the table.
I don't have any more specific errors. Sorry if I was vague in my previous
messages.
"Andrew J. Kelly" wrote:
> Do you mean this line: INSERT INTO #dirlist (FName)
> exec @.Return = master..xp_cmdshell 'dir /OD C:\Data\Backups\*.Bak'
> Can you be so kind as to list exactly what the error is that you are
> getting? I don't understand why you can't see this table? Are you running
> the select in the same connection as the one that created the table? If not
> you won't be able to see it since it is a local temporary table. You really
> need to be a little more specific on what you are doing and what the real
> errors are that you are getting otherwise I am simply guessing.
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:61C4CAE3-8398-4905-AE4C-411D6A55DA12@.microsoft.com...
> > Thanks.. I'm going through the code and I'm running into an error when you
> > use the directory list command. I'm not able to view the temporary table
> > to
> > see if data is being stored.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> You can't do this strictly with TSQL but this is as close as you will
> >> get.
> >> There is a sample sp that will delete old log file backups based on the
> >> timestamp in the name of the files.
> >>
> >> -- Removing Older Backup Files --
> >>
> >> -- Remove any log files older than 7 days
> >> DECLARE @.Error INT, @.D DATETIME
> >> SET @.D = DATEADD(dd,-7,GETDATE())
> >>
> >> EXEC @.Error = remove_old_log_files @.D
> >>
> >>
> >> SELECT @.Error
> >>
> >> --
> >> CREATE PROCEDURE remove_old_log_files
> >> @.DelDate DATETIME
> >>
> >> AS
> >>
> >> SET NOCOUNT ON
> >>
> >> DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
> >> DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
> >>
> >> -- Used later when we cast strings to Datetimes
> >> SET DATEFORMAT MDY
> >>
> >> -- Create a table to hold the results of the DIR command
> >> IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
> >> DROP TABLE #DirList
> >>
> >> CREATE TABLE #dirlist (FName VARCHAR(1000))
> >>
> >> -- Create a table to hold any errors
> >> IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
> >> DROP TABLE #Errors
> >>
> >> CREATE TABLE #Errors (Results VARCHAR(1000))
> >>
> >> -- Insert the results of the dir cmd into a table so we can scan it
> >> INSERT INTO #dirlist (FName)
> >> exec @.Return = master..xp_cmdshell 'dir /OD
> >> C:\Data\Backups\*.Bak'
> >>
> >> SET @.Error = @.@.ERROR
> >>
> >> IF @.Error <> 0 OR @.Return <> 0
> >> BEGIN
> >> IF @.Return = 1
> >> SET @.Error = -1
> >> SET @.Msg = 'Error while getting the filenames with DIR '
> >> GOTO On_Error
> >> END
> >>
> >> -- Remove the garbage
> >> DELETE #dirlist WHERE
> >> SUBSTRING(FName,1,2) < '00' OR
> >> SUBSTRING(FName,1,2) > '99' OR
> >> FName IS NULL OR
> >> FName LIKE '%<DIR>%'
> >>
> >>
> >> -- Create a cursor and for each file name do the processing.
> >> -- The files will be processed in date order.
> >> DECLARE curDir CURSOR READ_ONLY LOCAL
> >> FOR
> >> SELECT SUBSTRING(FName,40,40) AS FName
> >> FROM #dirlist
> >> WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
> >> AND SUBSTRING(FName,40,40) LIKE '%.BAK'
> >> ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
> >>
> >> OPEN curDir
> >>
> >> FETCH NEXT FROM curDir INTO @.Fname
> >> WHILE (@.@.fetch_status = 0)
> >> BEGIN
> >>
> >>
> >> -- Delete the old backup files
> >> SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
> >>
> >> INSERT INTO #Errors (Results)
> >> exec @.Return = master..xp_cmdshell @.Delete
> >>
> >>
> >> IF @.@.RowCount > 1 OR @.Return = 1
> >> BEGIN
> >> SET @.Error = -1
> >> SET @.Msg = 'Error while Deleting file ' + @.FName
> >> GOTO On_Error
> >> END
> >>
> >> PRINT 'Deleted ' + @.FName + ' at ' +
> >> CONVERT(VARCHAR(28),GETDATE(),113)
> >>
> >> FETCH NEXT FROM curDir INTO @.Fname
> >> END
> >>
> >> CLOSE curDir
> >> DEALLOCATE curDir
> >>
> >> DROP TABLE #DirList
> >> DROP TABLE #Errors
> >>
> >> RETURN @.Error
> >>
> >> On_Error:
> >> BEGIN
> >> IF @.Error <> 0
> >> BEGIN
> >> SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
> >> RAISERROR(@.Msg,12,1)
> >> RETURN @.Error
> >> END
> >> END
> >> GO
> >>
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
> >> > Andrew or anyone,
> >> >
> >> > I'm trying to write a short sql stmts to attached to a folder and query
> >> > that
> >> > folder for *.bak and if the*.bak is older than a certain amount of
> >> > days,
> >> > then
> >> > I want to delete them.
> >> >
> >> > Is this possible using tsql?
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> Don't use EM, use a script instead. EM has some limitations and
> >> >> things
> >> >> like
> >> >> this can be much more controlled through scripts. The restore syntax
> >> >> is
> >> >> pretty simple and there are examples in BOL. If you have troubles with
> >> >> it
> >> >> post your script and we can help.
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >>
> >> >>
> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> >> news:B202FDF2-5301-4E8E-9558-F2E0505F563C@.microsoft.com...
> >> >> > Andrew,
> >> >> >
> >> >> > Do you know how to access a network drive within Enterprise Mgr?
> >> >> >
> >> >> > I'm trying to restore a db where the backups are located on the
> >> >> > network. I
> >> >> > can see other network drives but cannot view the I need. I've
> >> >> > already
> >> >> > checked
> >> >> > the permissions and made sure the drive was shared. Do you have any
> >> >> > suggestions?
> >> >> >
> >> >> > "Andrew J. Kelly" wrote:
> >> >> >
> >> >> >> Just change the CONVERT to use an different format and make the
> >> >> >> variable
> >> >> >> large enough to handle the extra characters. You will have to look
> >> >> >> at
> >> >> >> CONVERT in BOL to see which suites your requirement best. The only
> >> >> >> thing
> >> >> >> is
> >> >> >> that when you add the time portion you usually have to use a format
> >> >> >> that
> >> >> >> inserts dashs or slashes. See if this works for you:
> >> >> >>
> >> >> >> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
> >> >> >> GETDATE(),120),'-',''),':',''),' ',''),12)
> >> >> >>
> >> >> >>
> >> >> >> --
> >> >> >> Andrew J. Kelly SQL MVP
> >> >> >>
> >> >> >>
> >> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> >> >> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
> >> >> >> > Andrew,
> >> >> >> >
> >> >> >> > Thanks worked. How can I add the time to it as well. I am going
> >> >> >> > to
> >> >> >> > create
> >> >> >> > a
> >> >> >> > DTS package to run this query.
> >> >> >> >
> >> >> >> > "Andrew J. Kelly" wrote:
> >> >> >> >
> >> >> >> >> It would help if you gave a little more background or at least
> >> >> >> >> showed
> >> >> >> >> what
> >> >> >> >> you were doing now. Here is an example of adding a datetime to
> >> >> >> >> a
> >> >> >> >> backup
> >> >> >> >> device name but I don't know hot it fits your situation.
> >> >> >> >>
> >> >> >> >> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name
> >> >> >> >> NVARCHAR(150)
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> DECLARE cur_DBs CURSOR STATIC LOCAL
> >> >> >> >> FOR SELECT Catalog_Name
> >> >> >> >> FROM Information_Schema.Schemata
> >> >> >> >> -- WHERE [Catalog_Name] NOT IN
> >> >> >> >> ('MASTER','MODEL','MSDB','TEMPDB')
> >> >> >> >> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> OPEN cur_DBs
> >> >> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
> >> >> >> >>
> >> >> >> >> WHILE @.@.FETCH_STATUS = 0
> >> >> >> >> BEGIN
> >> >> >> >>
> >> >> >> >> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
> >> >> >> >> CONVERT(NVARCHAR(16),GETDATE(),112) +
> >> >> >> >> N'.BAK'
> >> >> >> >>
> >> >> >> >> SET @.Name = @.DBName + N' Full Backup'
> >> >> >> >>
> >> >> >> >> PRINT 'Backing up database ' + @.DBName
> >> >> >> >>
> >> >> >> >> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT ,
> >> >> >> >> NOUNLOAD ,
> >> >> >> >> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
> >> >> >> >>
> >> >> >> >> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> >> >> >> >>
> >> >> >> >> PRINT '--- '
> >> >> >> >>
> >> >> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
> >> >> >> >> END
> >> >> >> >>
> >> >> >> >> CLOSE cur_DBs
> >> >> >> >> DEALLOCATE cur_DBs
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Andrew J. Kelly SQL MVP
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> >> >> >> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
> >> >> >> >> >I want to add a datetime stamp on my backup file names but what
> >> >> >> >> >I
> >> >> >> >> >have
> >> >> >> >> >is
> >> >> >> >> >not
> >> >> >> >> > adding to the filename.
> >> >> >> >> >
> >> >> >> >> > Does anyone know how to do this?
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Sonya,
The temp table is created inside of a stored procedure. If you run the sp
and it creates a temp table it only lives for the duration of the sp
execution. So by the time you query for it the table has been destroyed.
This is how local temp tables work and is the whole purpose of them. If you
wan to play around with it you need to remove the code from the sp and run
it as a standard batch in query analyzer.
--
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:D7770A92-6EEA-4B7D-8C9C-6A9DFBC92046@.microsoft.com...
> When I try to query the temp table within the same connection, it says the
> object is invalid. So I'm not sure the script is working because I can't
> view
> the table.
> I don't have any more specific errors. Sorry if I was vague in my previous
> messages.
> "Andrew J. Kelly" wrote:
>> Do you mean this line: INSERT INTO #dirlist (FName)
>> exec @.Return = master..xp_cmdshell 'dir /OD
>> C:\Data\Backups\*.Bak'
>> Can you be so kind as to list exactly what the error is that you are
>> getting? I don't understand why you can't see this table? Are you
>> running
>> the select in the same connection as the one that created the table? If
>> not
>> you won't be able to see it since it is a local temporary table. You
>> really
>> need to be a little more specific on what you are doing and what the real
>> errors are that you are getting otherwise I am simply guessing.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> news:61C4CAE3-8398-4905-AE4C-411D6A55DA12@.microsoft.com...
>> > Thanks.. I'm going through the code and I'm running into an error when
>> > you
>> > use the directory list command. I'm not able to view the temporary
>> > table
>> > to
>> > see if data is being stored.
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> You can't do this strictly with TSQL but this is as close as you will
>> >> get.
>> >> There is a sample sp that will delete old log file backups based on
>> >> the
>> >> timestamp in the name of the files.
>> >>
>> >> -- Removing Older Backup Files --
>> >>
>> >> -- Remove any log files older than 7 days
>> >> DECLARE @.Error INT, @.D DATETIME
>> >> SET @.D = DATEADD(dd,-7,GETDATE())
>> >>
>> >> EXEC @.Error = remove_old_log_files @.D
>> >>
>> >>
>> >> SELECT @.Error
>> >>
>> >> --
>> >> CREATE PROCEDURE remove_old_log_files
>> >> @.DelDate DATETIME
>> >>
>> >> AS
>> >>
>> >> SET NOCOUNT ON
>> >>
>> >> DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
>> >> DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
>> >>
>> >> -- Used later when we cast strings to Datetimes
>> >> SET DATEFORMAT MDY
>> >>
>> >> -- Create a table to hold the results of the DIR command
>> >> IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
>> >> DROP TABLE #DirList
>> >>
>> >> CREATE TABLE #dirlist (FName VARCHAR(1000))
>> >>
>> >> -- Create a table to hold any errors
>> >> IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
>> >> DROP TABLE #Errors
>> >>
>> >> CREATE TABLE #Errors (Results VARCHAR(1000))
>> >>
>> >> -- Insert the results of the dir cmd into a table so we can scan it
>> >> INSERT INTO #dirlist (FName)
>> >> exec @.Return = master..xp_cmdshell 'dir /OD
>> >> C:\Data\Backups\*.Bak'
>> >>
>> >> SET @.Error = @.@.ERROR
>> >>
>> >> IF @.Error <> 0 OR @.Return <> 0
>> >> BEGIN
>> >> IF @.Return = 1
>> >> SET @.Error = -1
>> >> SET @.Msg = 'Error while getting the filenames with DIR '
>> >> GOTO On_Error
>> >> END
>> >>
>> >> -- Remove the garbage
>> >> DELETE #dirlist WHERE
>> >> SUBSTRING(FName,1,2) < '00' OR
>> >> SUBSTRING(FName,1,2) > '99' OR
>> >> FName IS NULL OR
>> >> FName LIKE '%<DIR>%'
>> >>
>> >>
>> >> -- Create a cursor and for each file name do the processing.
>> >> -- The files will be processed in date order.
>> >> DECLARE curDir CURSOR READ_ONLY LOCAL
>> >> FOR
>> >> SELECT SUBSTRING(FName,40,40) AS FName
>> >> FROM #dirlist
>> >> WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
>> >> AND SUBSTRING(FName,40,40) LIKE '%.BAK'
>> >> ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
>> >>
>> >> OPEN curDir
>> >>
>> >> FETCH NEXT FROM curDir INTO @.Fname
>> >> WHILE (@.@.fetch_status = 0)
>> >> BEGIN
>> >>
>> >>
>> >> -- Delete the old backup files
>> >> SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
>> >>
>> >> INSERT INTO #Errors (Results)
>> >> exec @.Return = master..xp_cmdshell @.Delete
>> >>
>> >>
>> >> IF @.@.RowCount > 1 OR @.Return = 1
>> >> BEGIN
>> >> SET @.Error = -1
>> >> SET @.Msg = 'Error while Deleting file ' + @.FName
>> >> GOTO On_Error
>> >> END
>> >>
>> >> PRINT 'Deleted ' + @.FName + ' at ' +
>> >> CONVERT(VARCHAR(28),GETDATE(),113)
>> >>
>> >> FETCH NEXT FROM curDir INTO @.Fname
>> >> END
>> >>
>> >> CLOSE curDir
>> >> DEALLOCATE curDir
>> >>
>> >> DROP TABLE #DirList
>> >> DROP TABLE #Errors
>> >>
>> >> RETURN @.Error
>> >>
>> >> On_Error:
>> >> BEGIN
>> >> IF @.Error <> 0
>> >> BEGIN
>> >> SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
>> >> RAISERROR(@.Msg,12,1)
>> >> RETURN @.Error
>> >> END
>> >> END
>> >> GO
>> >>
>> >>
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
>> >> > Andrew or anyone,
>> >> >
>> >> > I'm trying to write a short sql stmts to attached to a folder and
>> >> > query
>> >> > that
>> >> > folder for *.bak and if the*.bak is older than a certain amount of
>> >> > days,
>> >> > then
>> >> > I want to delete them.
>> >> >
>> >> > Is this possible using tsql?
>> >> >
>> >> > "Andrew J. Kelly" wrote:
>> >> >
>> >> >> Don't use EM, use a script instead. EM has some limitations and
>> >> >> things
>> >> >> like
>> >> >> this can be much more controlled through scripts. The restore
>> >> >> syntax
>> >> >> is
>> >> >> pretty simple and there are examples in BOL. If you have troubles
>> >> >> with
>> >> >> it
>> >> >> post your script and we can help.
>> >> >>
>> >> >> --
>> >> >> Andrew J. Kelly SQL MVP
>> >> >>
>> >> >>
>> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> >> news:B202FDF2-5301-4E8E-9558-F2E0505F563C@.microsoft.com...
>> >> >> > Andrew,
>> >> >> >
>> >> >> > Do you know how to access a network drive within Enterprise Mgr?
>> >> >> >
>> >> >> > I'm trying to restore a db where the backups are located on the
>> >> >> > network. I
>> >> >> > can see other network drives but cannot view the I need. I've
>> >> >> > already
>> >> >> > checked
>> >> >> > the permissions and made sure the drive was shared. Do you have
>> >> >> > any
>> >> >> > suggestions?
>> >> >> >
>> >> >> > "Andrew J. Kelly" wrote:
>> >> >> >
>> >> >> >> Just change the CONVERT to use an different format and make the
>> >> >> >> variable
>> >> >> >> large enough to handle the extra characters. You will have to
>> >> >> >> look
>> >> >> >> at
>> >> >> >> CONVERT in BOL to see which suites your requirement best. The
>> >> >> >> only
>> >> >> >> thing
>> >> >> >> is
>> >> >> >> that when you add the time portion you usually have to use a
>> >> >> >> format
>> >> >> >> that
>> >> >> >> inserts dashs or slashes. See if this works for you:
>> >> >> >>
>> >> >> >> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
>> >> >> >> GETDATE(),120),'-',''),':',''),' ',''),12)
>> >> >> >>
>> >> >> >>
>> >> >> >> --
>> >> >> >> Andrew J. Kelly SQL MVP
>> >> >> >>
>> >> >> >>
>> >> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> >> >> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
>> >> >> >> > Andrew,
>> >> >> >> >
>> >> >> >> > Thanks worked. How can I add the time to it as well. I am
>> >> >> >> > going
>> >> >> >> > to
>> >> >> >> > create
>> >> >> >> > a
>> >> >> >> > DTS package to run this query.
>> >> >> >> >
>> >> >> >> > "Andrew J. Kelly" wrote:
>> >> >> >> >
>> >> >> >> >> It would help if you gave a little more background or at
>> >> >> >> >> least
>> >> >> >> >> showed
>> >> >> >> >> what
>> >> >> >> >> you were doing now. Here is an example of adding a datetime
>> >> >> >> >> to
>> >> >> >> >> a
>> >> >> >> >> backup
>> >> >> >> >> device name but I don't know hot it fits your situation.
>> >> >> >> >>
>> >> >> >> >> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name
>> >> >> >> >> NVARCHAR(150)
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> DECLARE cur_DBs CURSOR STATIC LOCAL
>> >> >> >> >> FOR SELECT Catalog_Name
>> >> >> >> >> FROM Information_Schema.Schemata
>> >> >> >> >> -- WHERE [Catalog_Name] NOT IN
>> >> >> >> >> ('MASTER','MODEL','MSDB','TEMPDB')
>> >> >> >> >> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> OPEN cur_DBs
>> >> >> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
>> >> >> >> >>
>> >> >> >> >> WHILE @.@.FETCH_STATUS = 0
>> >> >> >> >> BEGIN
>> >> >> >> >>
>> >> >> >> >> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_'
>> >> >> >> >> +
>> >> >> >> >> CONVERT(NVARCHAR(16),GETDATE(),112) +
>> >> >> >> >> N'.BAK'
>> >> >> >> >>
>> >> >> >> >> SET @.Name = @.DBName + N' Full Backup'
>> >> >> >> >>
>> >> >> >> >> PRINT 'Backing up database ' + @.DBName
>> >> >> >> >>
>> >> >> >> >> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT ,
>> >> >> >> >> NOUNLOAD ,
>> >> >> >> >> NAME = @.Name, NOSKIP , STATS = 10,
>> >> >> >> >> NOFORMAT
>> >> >> >> >>
>> >> >> >> >> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
>> >> >> >> >>
>> >> >> >> >> PRINT '--- '
>> >> >> >> >>
>> >> >> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
>> >> >> >> >> END
>> >> >> >> >>
>> >> >> >> >> CLOSE cur_DBs
>> >> >> >> >> DEALLOCATE cur_DBs
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> --
>> >> >> >> >> Andrew J. Kelly SQL MVP
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> >> >> >> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>> >> >> >> >> >I want to add a datetime stamp on my backup file names but
>> >> >> >> >> >what
>> >> >> >> >> >I
>> >> >> >> >> >have
>> >> >> >> >> >is
>> >> >> >> >> >not
>> >> >> >> >> > adding to the filename.
>> >> >> >> >> >
>> >> >> >> >> > Does anyone know how to do this?
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Ok, I was doing that as well. I'll have to remove or reference the return
function differently. Thank you for all of your help. The message that I
received is the following:
Server: Msg 178, Level 15, State 1, Line 92
A RETURN statement with a return value cannot be used in this context.
Server: Msg 178, Level 15, State 1, Line 99
A RETURN statement with a return value cannot be used in this context.
"Andrew J. Kelly" wrote:
> Sonya,
> The temp table is created inside of a stored procedure. If you run the sp
> and it creates a temp table it only lives for the duration of the sp
> execution. So by the time you query for it the table has been destroyed.
> This is how local temp tables work and is the whole purpose of them. If you
> wan to play around with it you need to remove the code from the sp and run
> it as a standard batch in query analyzer.
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:D7770A92-6EEA-4B7D-8C9C-6A9DFBC92046@.microsoft.com...
> > When I try to query the temp table within the same connection, it says the
> > object is invalid. So I'm not sure the script is working because I can't
> > view
> > the table.
> >
> > I don't have any more specific errors. Sorry if I was vague in my previous
> > messages.
> > "Andrew J. Kelly" wrote:
> >
> >> Do you mean this line: INSERT INTO #dirlist (FName)
> >> exec @.Return = master..xp_cmdshell 'dir /OD
> >> C:\Data\Backups\*.Bak'
> >>
> >> Can you be so kind as to list exactly what the error is that you are
> >> getting? I don't understand why you can't see this table? Are you
> >> running
> >> the select in the same connection as the one that created the table? If
> >> not
> >> you won't be able to see it since it is a local temporary table. You
> >> really
> >> need to be a little more specific on what you are doing and what the real
> >> errors are that you are getting otherwise I am simply guessing.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> news:61C4CAE3-8398-4905-AE4C-411D6A55DA12@.microsoft.com...
> >> > Thanks.. I'm going through the code and I'm running into an error when
> >> > you
> >> > use the directory list command. I'm not able to view the temporary
> >> > table
> >> > to
> >> > see if data is being stored.
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> You can't do this strictly with TSQL but this is as close as you will
> >> >> get.
> >> >> There is a sample sp that will delete old log file backups based on
> >> >> the
> >> >> timestamp in the name of the files.
> >> >>
> >> >> -- Removing Older Backup Files --
> >> >>
> >> >> -- Remove any log files older than 7 days
> >> >> DECLARE @.Error INT, @.D DATETIME
> >> >> SET @.D = DATEADD(dd,-7,GETDATE())
> >> >>
> >> >> EXEC @.Error = remove_old_log_files @.D
> >> >>
> >> >>
> >> >> SELECT @.Error
> >> >>
> >> >> --
> >> >> CREATE PROCEDURE remove_old_log_files
> >> >> @.DelDate DATETIME
> >> >>
> >> >> AS
> >> >>
> >> >> SET NOCOUNT ON
> >> >>
> >> >> DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
> >> >> DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
> >> >>
> >> >> -- Used later when we cast strings to Datetimes
> >> >> SET DATEFORMAT MDY
> >> >>
> >> >> -- Create a table to hold the results of the DIR command
> >> >> IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
> >> >> DROP TABLE #DirList
> >> >>
> >> >> CREATE TABLE #dirlist (FName VARCHAR(1000))
> >> >>
> >> >> -- Create a table to hold any errors
> >> >> IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
> >> >> DROP TABLE #Errors
> >> >>
> >> >> CREATE TABLE #Errors (Results VARCHAR(1000))
> >> >>
> >> >> -- Insert the results of the dir cmd into a table so we can scan it
> >> >> INSERT INTO #dirlist (FName)
> >> >> exec @.Return = master..xp_cmdshell 'dir /OD
> >> >> C:\Data\Backups\*.Bak'
> >> >>
> >> >> SET @.Error = @.@.ERROR
> >> >>
> >> >> IF @.Error <> 0 OR @.Return <> 0
> >> >> BEGIN
> >> >> IF @.Return = 1
> >> >> SET @.Error = -1
> >> >> SET @.Msg = 'Error while getting the filenames with DIR '
> >> >> GOTO On_Error
> >> >> END
> >> >>
> >> >> -- Remove the garbage
> >> >> DELETE #dirlist WHERE
> >> >> SUBSTRING(FName,1,2) < '00' OR
> >> >> SUBSTRING(FName,1,2) > '99' OR
> >> >> FName IS NULL OR
> >> >> FName LIKE '%<DIR>%'
> >> >>
> >> >>
> >> >> -- Create a cursor and for each file name do the processing.
> >> >> -- The files will be processed in date order.
> >> >> DECLARE curDir CURSOR READ_ONLY LOCAL
> >> >> FOR
> >> >> SELECT SUBSTRING(FName,40,40) AS FName
> >> >> FROM #dirlist
> >> >> WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
> >> >> AND SUBSTRING(FName,40,40) LIKE '%.BAK'
> >> >> ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
> >> >>
> >> >> OPEN curDir
> >> >>
> >> >> FETCH NEXT FROM curDir INTO @.Fname
> >> >> WHILE (@.@.fetch_status = 0)
> >> >> BEGIN
> >> >>
> >> >>
> >> >> -- Delete the old backup files
> >> >> SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
> >> >>
> >> >> INSERT INTO #Errors (Results)
> >> >> exec @.Return = master..xp_cmdshell @.Delete
> >> >>
> >> >>
> >> >> IF @.@.RowCount > 1 OR @.Return = 1
> >> >> BEGIN
> >> >> SET @.Error = -1
> >> >> SET @.Msg = 'Error while Deleting file ' + @.FName
> >> >> GOTO On_Error
> >> >> END
> >> >>
> >> >> PRINT 'Deleted ' + @.FName + ' at ' +
> >> >> CONVERT(VARCHAR(28),GETDATE(),113)
> >> >>
> >> >> FETCH NEXT FROM curDir INTO @.Fname
> >> >> END
> >> >>
> >> >> CLOSE curDir
> >> >> DEALLOCATE curDir
> >> >>
> >> >> DROP TABLE #DirList
> >> >> DROP TABLE #Errors
> >> >>
> >> >> RETURN @.Error
> >> >>
> >> >> On_Error:
> >> >> BEGIN
> >> >> IF @.Error <> 0
> >> >> BEGIN
> >> >> SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
> >> >> RAISERROR(@.Msg,12,1)
> >> >> RETURN @.Error
> >> >> END
> >> >> END
> >> >> GO
> >> >>
> >> >>
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >>
> >> >>
> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> >> news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
> >> >> > Andrew or anyone,
> >> >> >
> >> >> > I'm trying to write a short sql stmts to attached to a folder and
> >> >> > query
> >> >> > that
> >> >> > folder for *.bak and if the*.bak is older than a certain amount of
> >> >> > days,
> >> >> > then
> >> >> > I want to delete them.
> >> >> >
> >> >> > Is this possible using tsql?
> >> >> >
> >> >> > "Andrew J. Kelly" wrote:
> >> >> >
> >> >> >> Don't use EM, use a script instead. EM has some limitations and
> >> >> >> things
> >> >> >> like
> >> >> >> this can be much more controlled through scripts. The restore
> >> >> >> syntax
> >> >> >> is
> >> >> >> pretty simple and there are examples in BOL. If you have troubles
> >> >> >> with
> >> >> >> it
> >> >> >> post your script and we can help.
> >> >> >>
> >> >> >> --
> >> >> >> Andrew J. Kelly SQL MVP
> >> >> >>
> >> >> >>
> >> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> >> >> news:B202FDF2-5301-4E8E-9558-F2E0505F563C@.microsoft.com...
> >> >> >> > Andrew,
> >> >> >> >
> >> >> >> > Do you know how to access a network drive within Enterprise Mgr?
> >> >> >> >
> >> >> >> > I'm trying to restore a db where the backups are located on the
> >> >> >> > network. I
> >> >> >> > can see other network drives but cannot view the I need. I've
> >> >> >> > already
> >> >> >> > checked
> >> >> >> > the permissions and made sure the drive was shared. Do you have
> >> >> >> > any
> >> >> >> > suggestions?
> >> >> >> >
> >> >> >> > "Andrew J. Kelly" wrote:
> >> >> >> >
> >> >> >> >> Just change the CONVERT to use an different format and make the
> >> >> >> >> variable
> >> >> >> >> large enough to handle the extra characters. You will have to
> >> >> >> >> look
> >> >> >> >> at
> >> >> >> >> CONVERT in BOL to see which suites your requirement best. The
> >> >> >> >> only
> >> >> >> >> thing
> >> >> >> >> is
> >> >> >> >> that when you add the time portion you usually have to use a
> >> >> >> >> format
> >> >> >> >> that
> >> >> >> >> inserts dashs or slashes. See if this works for you:
> >> >> >> >>
> >> >> >> >> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
> >> >> >> >> GETDATE(),120),'-',''),':',''),' ',''),12)
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Andrew J. Kelly SQL MVP
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> >> >> >> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
> >> >> >> >> > Andrew,
> >> >> >> >> >
> >> >> >> >> > Thanks worked. How can I add the time to it as well. I am
> >> >> >> >> > going
> >> >> >> >> > to
> >> >> >> >> > create
> >> >> >> >> > a
> >> >> >> >> > DTS package to run this query.
> >> >> >> >> >
> >> >> >> >> > "Andrew J. Kelly" wrote:
> >> >> >> >> >
> >> >> >> >> >> It would help if you gave a little more background or at
> >> >> >> >> >> least
> >> >> >> >> >> showed
> >> >> >> >> >> what
> >> >> >> >> >> you were doing now. Here is an example of adding a datetime
> >> >> >> >> >> to
> >> >> >> >> >> a
> >> >> >> >> >> backup
> >> >> >> >> >> device name but I don't know hot it fits your situation.
> >> >> >> >> >>
> >> >> >> >> >> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name
> >> >> >> >> >> NVARCHAR(150)
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >> DECLARE cur_DBs CURSOR STATIC LOCAL
> >> >> >> >> >> FOR SELECT Catalog_Name
> >> >> >> >> >> FROM Information_Schema.Schemata
> >> >> >> >> >> -- WHERE [Catalog_Name] NOT IN
> >> >> >> >> >> ('MASTER','MODEL','MSDB','TEMPDB')
> >> >> >> >> >> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >> OPEN cur_DBs
> >> >> >> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
> >> >> >> >> >>
> >> >> >> >> >> WHILE @.@.FETCH_STATUS = 0
> >> >> >> >> >> BEGIN
> >> >> >> >> >>
> >> >> >> >> >> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_'
> >> >> >> >> >> +
> >> >> >> >> >> CONVERT(NVARCHAR(16),GETDATE(),112) +
> >> >> >> >> >> N'.BAK'
> >> >> >> >> >>
> >> >> >> >> >> SET @.Name = @.DBName + N' Full Backup'
> >> >> >> >> >>
> >> >> >> >> >> PRINT 'Backing up database ' + @.DBName
> >> >> >> >> >>
> >> >> >> >> >> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT ,
> >> >> >> >> >> NOUNLOAD ,
> >> >> >> >> >> NAME = @.Name, NOSKIP , STATS = 10,
> >> >> >> >> >> NOFORMAT
> >> >> >> >> >>
> >> >> >> >> >> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> >> >> >> >> >>
> >> >> >> >> >> PRINT '--- '
> >> >> >> >> >>
> >> >> >> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
> >> >> >> >> >> END
> >> >> >> >> >>
> >> >> >> >> >> CLOSE cur_DBs
> >> >> >> >> >> DEALLOCATE cur_DBs
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >> --
> >> >> >> >> >> Andrew J. Kelly SQL MVP
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> >> >> >> >> >> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
> >> >> >> >> >> >I want to add a datetime stamp on my backup file names but
> >> >> >> >> >> >what
> >> >> >> >> >> >I
> >> >> >> >> >> >have
> >> >> >> >> >> >is
> >> >> >> >> >> >not
> >> >> >> >> >> > adding to the filename.
> >> >> >> >> >> >
> >> >> >> >> >> > Does anyone know how to do this?
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||You can't use RETURN if you are not in a stored procedure. Just comment
that line out for your testing.
--
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:D62D330E-685E-4048-8635-FC6943AC9F50@.microsoft.com...
> Ok, I was doing that as well. I'll have to remove or reference the return
> function differently. Thank you for all of your help. The message that I
> received is the following:
> Server: Msg 178, Level 15, State 1, Line 92
> A RETURN statement with a return value cannot be used in this context.
> Server: Msg 178, Level 15, State 1, Line 99
> A RETURN statement with a return value cannot be used in this context.
>
> "Andrew J. Kelly" wrote:
>> Sonya,
>> The temp table is created inside of a stored procedure. If you run the
>> sp
>> and it creates a temp table it only lives for the duration of the sp
>> execution. So by the time you query for it the table has been destroyed.
>> This is how local temp tables work and is the whole purpose of them. If
>> you
>> wan to play around with it you need to remove the code from the sp and
>> run
>> it as a standard batch in query analyzer.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> news:D7770A92-6EEA-4B7D-8C9C-6A9DFBC92046@.microsoft.com...
>> > When I try to query the temp table within the same connection, it says
>> > the
>> > object is invalid. So I'm not sure the script is working because I
>> > can't
>> > view
>> > the table.
>> >
>> > I don't have any more specific errors. Sorry if I was vague in my
>> > previous
>> > messages.
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Do you mean this line: INSERT INTO #dirlist (FName)
>> >> exec @.Return = master..xp_cmdshell 'dir /OD
>> >> C:\Data\Backups\*.Bak'
>> >>
>> >> Can you be so kind as to list exactly what the error is that you are
>> >> getting? I don't understand why you can't see this table? Are you
>> >> running
>> >> the select in the same connection as the one that created the table?
>> >> If
>> >> not
>> >> you won't be able to see it since it is a local temporary table. You
>> >> really
>> >> need to be a little more specific on what you are doing and what the
>> >> real
>> >> errors are that you are getting otherwise I am simply guessing.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> news:61C4CAE3-8398-4905-AE4C-411D6A55DA12@.microsoft.com...
>> >> > Thanks.. I'm going through the code and I'm running into an error
>> >> > when
>> >> > you
>> >> > use the directory list command. I'm not able to view the temporary
>> >> > table
>> >> > to
>> >> > see if data is being stored.
>> >> >
>> >> > "Andrew J. Kelly" wrote:
>> >> >
>> >> >> You can't do this strictly with TSQL but this is as close as you
>> >> >> will
>> >> >> get.
>> >> >> There is a sample sp that will delete old log file backups based on
>> >> >> the
>> >> >> timestamp in the name of the files.
>> >> >>
>> >> >> -- Removing Older Backup Files --
>> >> >>
>> >> >> -- Remove any log files older than 7 days
>> >> >> DECLARE @.Error INT, @.D DATETIME
>> >> >> SET @.D = DATEADD(dd,-7,GETDATE())
>> >> >>
>> >> >> EXEC @.Error = remove_old_log_files @.D
>> >> >>
>> >> >>
>> >> >> SELECT @.Error
>> >> >>
>> >> >> --
>> >> >> CREATE PROCEDURE remove_old_log_files
>> >> >> @.DelDate DATETIME
>> >> >>
>> >> >> AS
>> >> >>
>> >> >> SET NOCOUNT ON
>> >> >>
>> >> >> DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
>> >> >> DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
>> >> >>
>> >> >> -- Used later when we cast strings to Datetimes
>> >> >> SET DATEFORMAT MDY
>> >> >>
>> >> >> -- Create a table to hold the results of the DIR command
>> >> >> IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
>> >> >> DROP TABLE #DirList
>> >> >>
>> >> >> CREATE TABLE #dirlist (FName VARCHAR(1000))
>> >> >>
>> >> >> -- Create a table to hold any errors
>> >> >> IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
>> >> >> DROP TABLE #Errors
>> >> >>
>> >> >> CREATE TABLE #Errors (Results VARCHAR(1000))
>> >> >>
>> >> >> -- Insert the results of the dir cmd into a table so we can scan
>> >> >> it
>> >> >> INSERT INTO #dirlist (FName)
>> >> >> exec @.Return = master..xp_cmdshell 'dir /OD
>> >> >> C:\Data\Backups\*.Bak'
>> >> >>
>> >> >> SET @.Error = @.@.ERROR
>> >> >>
>> >> >> IF @.Error <> 0 OR @.Return <> 0
>> >> >> BEGIN
>> >> >> IF @.Return = 1
>> >> >> SET @.Error = -1
>> >> >> SET @.Msg = 'Error while getting the filenames with DIR '
>> >> >> GOTO On_Error
>> >> >> END
>> >> >>
>> >> >> -- Remove the garbage
>> >> >> DELETE #dirlist WHERE
>> >> >> SUBSTRING(FName,1,2) < '00' OR
>> >> >> SUBSTRING(FName,1,2) > '99' OR
>> >> >> FName IS NULL OR
>> >> >> FName LIKE '%<DIR>%'
>> >> >>
>> >> >>
>> >> >> -- Create a cursor and for each file name do the processing.
>> >> >> -- The files will be processed in date order.
>> >> >> DECLARE curDir CURSOR READ_ONLY LOCAL
>> >> >> FOR
>> >> >> SELECT SUBSTRING(FName,40,40) AS FName
>> >> >> FROM #dirlist
>> >> >> WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) <
>> >> >> @.DelDate
>> >> >> AND SUBSTRING(FName,40,40) LIKE '%.BAK'
>> >> >> ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
>> >> >>
>> >> >> OPEN curDir
>> >> >>
>> >> >> FETCH NEXT FROM curDir INTO @.Fname
>> >> >> WHILE (@.@.fetch_status = 0)
>> >> >> BEGIN
>> >> >>
>> >> >>
>> >> >> -- Delete the old backup files
>> >> >> SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
>> >> >>
>> >> >> INSERT INTO #Errors (Results)
>> >> >> exec @.Return = master..xp_cmdshell @.Delete
>> >> >>
>> >> >>
>> >> >> IF @.@.RowCount > 1 OR @.Return = 1
>> >> >> BEGIN
>> >> >> SET @.Error = -1
>> >> >> SET @.Msg = 'Error while Deleting file ' + @.FName
>> >> >> GOTO On_Error
>> >> >> END
>> >> >>
>> >> >> PRINT 'Deleted ' + @.FName + ' at ' +
>> >> >> CONVERT(VARCHAR(28),GETDATE(),113)
>> >> >>
>> >> >> FETCH NEXT FROM curDir INTO @.Fname
>> >> >> END
>> >> >>
>> >> >> CLOSE curDir
>> >> >> DEALLOCATE curDir
>> >> >>
>> >> >> DROP TABLE #DirList
>> >> >> DROP TABLE #Errors
>> >> >>
>> >> >> RETURN @.Error
>> >> >>
>> >> >> On_Error:
>> >> >> BEGIN
>> >> >> IF @.Error <> 0
>> >> >> BEGIN
>> >> >> SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
>> >> >> RAISERROR(@.Msg,12,1)
>> >> >> RETURN @.Error
>> >> >> END
>> >> >> END
>> >> >> GO
>> >> >>
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Andrew J. Kelly SQL MVP
>> >> >>
>> >> >>
>> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> >> news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
>> >> >> > Andrew or anyone,
>> >> >> >
>> >> >> > I'm trying to write a short sql stmts to attached to a folder and
>> >> >> > query
>> >> >> > that
>> >> >> > folder for *.bak and if the*.bak is older than a certain amount
>> >> >> > of
>> >> >> > days,
>> >> >> > then
>> >> >> > I want to delete them.
>> >> >> >
>> >> >> > Is this possible using tsql?
>> >> >> >
>> >> >> > "Andrew J. Kelly" wrote:
>> >> >> >
>> >> >> >> Don't use EM, use a script instead. EM has some limitations and
>> >> >> >> things
>> >> >> >> like
>> >> >> >> this can be much more controlled through scripts. The restore
>> >> >> >> syntax
>> >> >> >> is
>> >> >> >> pretty simple and there are examples in BOL. If you have
>> >> >> >> troubles
>> >> >> >> with
>> >> >> >> it
>> >> >> >> post your script and we can help.
>> >> >> >>
>> >> >> >> --
>> >> >> >> Andrew J. Kelly SQL MVP
>> >> >> >>
>> >> >> >>
>> >> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> >> >> news:B202FDF2-5301-4E8E-9558-F2E0505F563C@.microsoft.com...
>> >> >> >> > Andrew,
>> >> >> >> >
>> >> >> >> > Do you know how to access a network drive within Enterprise
>> >> >> >> > Mgr?
>> >> >> >> >
>> >> >> >> > I'm trying to restore a db where the backups are located on
>> >> >> >> > the
>> >> >> >> > network. I
>> >> >> >> > can see other network drives but cannot view the I need. I've
>> >> >> >> > already
>> >> >> >> > checked
>> >> >> >> > the permissions and made sure the drive was shared. Do you
>> >> >> >> > have
>> >> >> >> > any
>> >> >> >> > suggestions?
>> >> >> >> >
>> >> >> >> > "Andrew J. Kelly" wrote:
>> >> >> >> >
>> >> >> >> >> Just change the CONVERT to use an different format and make
>> >> >> >> >> the
>> >> >> >> >> variable
>> >> >> >> >> large enough to handle the extra characters. You will have
>> >> >> >> >> to
>> >> >> >> >> look
>> >> >> >> >> at
>> >> >> >> >> CONVERT in BOL to see which suites your requirement best.
>> >> >> >> >> The
>> >> >> >> >> only
>> >> >> >> >> thing
>> >> >> >> >> is
>> >> >> >> >> that when you add the time portion you usually have to use a
>> >> >> >> >> format
>> >> >> >> >> that
>> >> >> >> >> inserts dashs or slashes. See if this works for you:
>> >> >> >> >>
>> >> >> >> >> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
>> >> >> >> >> GETDATE(),120),'-',''),':',''),' ',''),12)
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> --
>> >> >> >> >> Andrew J. Kelly SQL MVP
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> >> >> >> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
>> >> >> >> >> > Andrew,
>> >> >> >> >> >
>> >> >> >> >> > Thanks worked. How can I add the time to it as well. I am
>> >> >> >> >> > going
>> >> >> >> >> > to
>> >> >> >> >> > create
>> >> >> >> >> > a
>> >> >> >> >> > DTS package to run this query.
>> >> >> >> >> >
>> >> >> >> >> > "Andrew J. Kelly" wrote:
>> >> >> >> >> >
>> >> >> >> >> >> It would help if you gave a little more background or at
>> >> >> >> >> >> least
>> >> >> >> >> >> showed
>> >> >> >> >> >> what
>> >> >> >> >> >> you were doing now. Here is an example of adding a
>> >> >> >> >> >> datetime
>> >> >> >> >> >> to
>> >> >> >> >> >> a
>> >> >> >> >> >> backup
>> >> >> >> >> >> device name but I don't know hot it fits your situation.
>> >> >> >> >> >>
>> >> >> >> >> >> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100),
>> >> >> >> >> >> @.Name
>> >> >> >> >> >> NVARCHAR(150)
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >> DECLARE cur_DBs CURSOR STATIC LOCAL
>> >> >> >> >> >> FOR SELECT Catalog_Name
>> >> >> >> >> >> FROM Information_Schema.Schemata
>> >> >> >> >> >> -- WHERE [Catalog_Name] NOT IN
>> >> >> >> >> >> ('MASTER','MODEL','MSDB','TEMPDB')
>> >> >> >> >> >> WHERE [Catalog_Name] IN
>> >> >> >> >> >> ('MASTER','MODEL','MSDB')
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >> OPEN cur_DBs
>> >> >> >> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
>> >> >> >> >> >>
>> >> >> >> >> >> WHILE @.@.FETCH_STATUS = 0
>> >> >> >> >> >> BEGIN
>> >> >> >> >> >>
>> >> >> >> >> >> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName +
>> >> >> >> >> >> '_Full_'
>> >> >> >> >> >> +
>> >> >> >> >> >> CONVERT(NVARCHAR(16),GETDATE(),112) +
>> >> >> >> >> >> N'.BAK'
>> >> >> >> >> >>
>> >> >> >> >> >> SET @.Name = @.DBName + N' Full Backup'
>> >> >> >> >> >>
>> >> >> >> >> >> PRINT 'Backing up database ' + @.DBName
>> >> >> >> >> >>
>> >> >> >> >> >> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT ,
>> >> >> >> >> >> NOUNLOAD ,
>> >> >> >> >> >> NAME = @.Name, NOSKIP , STATS = 10,
>> >> >> >> >> >> NOFORMAT
>> >> >> >> >> >>
>> >> >> >> >> >> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
>> >> >> >> >> >>
>> >> >> >> >> >> PRINT '--- '
>> >> >> >> >> >>
>> >> >> >> >> >> FETCH NEXT FROM cur_DBs INTO @.DBName
>> >> >> >> >> >> END
>> >> >> >> >> >>
>> >> >> >> >> >> CLOSE cur_DBs
>> >> >> >> >> >> DEALLOCATE cur_DBs
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >> --
>> >> >> >> >> >> Andrew J. Kelly SQL MVP
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
>> >> >> >> >> >> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>> >> >> >> >> >> >I want to add a datetime stamp on my backup file names
>> >> >> >> >> >> >but
>> >> >> >> >> >> >what
>> >> >> >> >> >> >I
>> >> >> >> >> >> >have
>> >> >> >> >> >> >is
>> >> >> >> >> >> >not
>> >> >> >> >> >> > adding to the filename.
>> >> >> >> >> >> >
>> >> >> >> >> >> > Does anyone know how to do this?
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

Tuesday, March 27, 2012

DATABASE Becomes suspect

Sunday, March 25, 2012

Database Backups and Transaction Logs

Database Backups and Transaction Logs

Database Backups and Transaction Logs

database backup with encryption

database backup using Veritas

database backup using Veritas

database backup using Veritas

Thursday, March 22, 2012

database backup job quits for no apparent reason

Database Backup Issue

Wednesday, March 21, 2012

Database Backup

Monday, March 19, 2012

Database Backup

Database Backup

Database corrupted

Blog Archive

About Me