Dyanamic Database Backups In 6.5

Nov 12, 2000

Hello,

Can anyone tell me what the impact of dynamic database backups in sql 6.06.5
will have on users using the database?

Will their user processes be blocked?
Will their queries run slower than normal (how slower)?
Will there be a lot of locking activity as the SQl tries to backup?
Will the serverdatabase run slower

Anything else I may be missing?

Regards,
Hitesh

View 3 Replies


ADVERTISEMENT

Dyanamic Sql

Apr 1, 2004

Hi I was building a dynamic sql with output please tell me where I am wrong.
Or Tell me right way to do it

My table name is variable (Stock name)
I want close price for given Stock

CREATE PROCEDURE SP_GET_CLOSE
@TableName as varchar(50),
@close_price as varchar(50) output

AS
Declare @SQL NVarChar(1000)
Declare @high varchar(50)
SET @SQL = 'Select top 1 @close_price = [close] from '+ @TableName+ ' order by trade_date desc'

print @SQL
Exec sp_executesql @SQL, N'@close_price varchar(50) output' , @close_price
GO

View 3 Replies View Related

Dyanamic Stored Procedures

Oct 19, 2005

Hi all,I'm building a search function and I need do it using stored procedures. I'm searching a table on 'Name' 'email address' and 'key words'. (basically 3 columns). The user must be able to use it as SELECT ............ WHERE  (NAME  LIKE  @name) AND/OR (Email LIKE @email) AND/OR ............. Can some one tell me how to handle this in a stored procedure. By the way is this approch correct. Please tell me if there is a better method to tackle this situation.Thanks,-VJ 

View 4 Replies View Related

DB Engine :: Will Transaction Log Backups Not Free Up Log During Full Backups

Nov 15, 2015

The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?

View 3 Replies View Related

Database Backups

Mar 16, 2001

Why is my database backup file so big? It's about 10 X bigger than the actual data files

View 1 Replies View Related

Database Backups

Nov 27, 2000

I know that I have read not to backup a database over a netwrok. So I am curious as to what others are doing out there. BAckup to your local hard drive on the server and then move the backup files to a repository some where on the network? Do others have a file structure out on another server that stores all of the backups from all of the different servers that have SQL 7.0 on them? We are a small company and are just starting to migrate data to SQL Server 7.0.

View 1 Replies View Related

Database Backups

Mar 16, 2006

I have to perform a backup for disaster recovery purposes before an application upgrade. The upgrade will alter the database and stored procedures. My cuurent backup takes a backup of master and msdb weekly. The user database uses the Full Recovery model and is backed up daily at 21:00 and the logs daily at 13:00. Assuming the databse is modified between the last backup and the upgrade starting at 9:00am what should my backup stratergy be for roll back purposes.
1) backup master, msdb and the user Database to a different location than the normal backups. Use these to restore if required
2) backup the master, msdb and user databases using the same jobs and therefore overwriting the original evening backups
3) do nothing and just restore master and msdb from a backup and replay the logs to a given point in time for thr user database should the upgrade fail

Any help or suggestions will be much appreciated.

View 3 Replies View Related

SQL Server Database Backups

Jul 28, 1998

I am looking for the best method to backup SQL Server databases. Currently we are
running a dump database statement to disk and backing up the files to tape through Arcserve.

One problem that I am having is the statement to dump the database. I would like to retain
the dump for at least three days and be able to restore the database from any one of those
three days. My current statement is:
"DUMP DATABASE CHOISDAT TO DISK=`D:BACKUPCHOIS.BAK` WITH
NOUNLOAD , STATS = 10, INIT , RETAINDAYS = 3, NOSKIP"

but, every other day I receive the message from SQL executive:
"Can`t open dump device `D:BACKUPCHOIS.BAK`, device error or device off line.
Please consult the SQL Server error log for more details. (Message 3201)"

What am I doing wrong? Any suggestions?

P.S.

Is there anyway to tell the Maintenance Wizard to delete the backups. I tried using the wizard but
the backup files still remain on the disk and I have to delete them every week.

View 1 Replies View Related

Database Backups && Compression

Jun 19, 2007

Hi All

I have a database which is 72GB, which is backed up every night as part of the maintenance plan. I have plenty of storage space, and the server that runs the database is fairly powerful (quad-processor 3.2ghz, 64bit, 48GB RAM) and is part of an active-passive cluster. The database backup is also copied to a SAN location.

My issue is with the size of the backup file. As part of the Disaster Recovery plan, I need to copy this database backup file accross the network to a remote site, so that in the event of a disaster at the site, business can continue at the remote site after restoring the database backup file. However, my database backup file is so big that I cannot copy it accross the network in time for the next morning. I have tried using WinRar and have managed to achieve a file about 20% of its original size, but it takes 2 hours to produce this file.

Is there any recommended reeading for this type of issue? Log shipping / mirroring has been investigated and will be part of the DR model but the 'powers that be' insist on having a full copy performed to the remote site.

Any suggestions? Thanks in advance guys n gals :-)

View 4 Replies View Related

Types Of Database Backups

Jun 4, 2008

HI Gurus,

Can anyone of you please post the link related to types of database backups which i can use for my reference purpose.


Thanks,
ServerTeam

View 3 Replies View Related

Auto-restore Of Database Backups

Feb 26, 2004

Our DBs are set up to do a full backup once a day (late at night) and then transaction log backups during the day at shorter intervals.

I want to setup a dev database on the same server. I want this database to be an automatically restored copy of the live database. So every night, after the full backup of the live DB, I want to restore the live DB to this dev DB.

Can this be automated? Can the restore automatically stop the dev database in case some open connections exist?

Is there a better way to do this?

View 5 Replies View Related

Cluster Database Backups Question

Sep 13, 2002

In a non-clustered environment, I am under the impression that backups must be to a local disk or local tape device.

My plan is to have a separate disk in a clustered environment on a shared array for holding my backups, until they can be transferred somewhere else.

My question is, will SQL Server 2K support backing up to the disk in the shared array since it is (I believe) not considered a local disk? What key points may I need to know.

Thanks very much for the help!!

solart

View 4 Replies View Related

SQL 2012 :: User Database Backups

Jun 2, 2014

I have inherited a new SQL Server 2008 database server and can not figure out how my user databases are being backed up. This database server is running under a VM.

All the user databases are being backed up nightly per the SQL server log. The backups are written to a virtual disk and is kicked off by the NT AUTHORITYSYSTEM user. I can not see the virtual disk. A restore task does not provide any information about the last backup. I have created a new database, and it is automatically included in the next set of backups.

I have looked at the windows event viewer with out any luck. There are no SQL Server Maintenance Plans or Agent jobs that call a backup. I have also checked the Windows Task Scheduler and can not find any task that does a backup.Could the backups be called from another server ?

View 6 Replies View Related

How To Check For Failed Database Backups

May 8, 2007

Hello everyone! Looking for some insight here on database backups that fail.

We have many SQL servers that we maintain by storing Job/Maintenence Plan history on a central server, which then emails out daily reports to let us know what backed up last night and what didn't

This was easy to do in SQL 2000, not so much in SQL 2005. I have put together a query that gathers the info I need for the successes:

SELECT DISTINCT '00000000-0000-0000-0000-000000000001' AS Plan_ID, mpld.line1 as "Plan Name", bud.database_name as "Database", mpld.server_name, 'Backup Database' as Activity, mpld.succeeded, bs.backup_finish_date, DATEDIFF (MS,bs.backup_start_date,bs.backup_finish_date) as Duration, bs.backup_start_date, mpld.error_number, mpld.error_messageFROM msdb.dbo.sysmaintplan_logdetail mpld
INNER JOIN msdb.dbo.backupset bs
on (select convert(char(12),mpld.start_time,109))=(select convert(char(12),bs.backup_start_date,109))-- on bs.database_name=bud.database_name
inner join
msdb.dbo.bu_dbs bud
on bs.database_name = bud.database_name
WHERE mpld.succeeded = 1 and mpld.line2 like 'Backup%' and bs.type='d'
and bs.backup_start_date > ( SELECT CONVERT(char(12), (GETDATE()-1), 109) )
ORDER BY bud.database_name DESC

But I am having trouble using a query to determine the databases the FAILED during backup. MSDB.BackupSet and MSDB.SYSMaintPlan_LogDetail really have nothing,because often times, even if a step in a Maint. Plan fails, the plan finishes reporting success.

Does anyone know of a good way to gather info about failed database backups?

Thank you!

Mario

View 6 Replies View Related

Database Backups Growing Exponentially

Jul 23, 2005

Hi All.I'm currently maintaining 4 servers - 1 for public/customers and 3for backups, development, etc...I regularly backup the entire SQL database for our public server andrestore it on each of the other servers. Lately, however, the databasebackups have grown (in size) incredibly fast - they've gone from about200MB to 2+ GB in 2 months. (I wasn't entirely surprised by this atfirst since our client traffic has drastically increased as well.) Theweird thing, though, is that (on two of the backup servers) when Irestore the backup then use those servers to create a new completebackup, the new backup is only about 200-300 MB in size.My assumption is that there's some kind of setting buried deep insidethe sql configuration allowing it to compress or otherwise alterbackups. Does anyone have any ideas/thoughts as to what may be causingthis issue?We're using SQL Server 7 on Windows 2000 servers.Thanks in advance.GreggJoin Bytes!

View 2 Replies View Related

Log Backups On Heavy And Busy Database

Apr 30, 2008


What will be best procedure for the following situation.

Heavy traffic database on daily basis. G growth every day. so Full backups every nights are needed.
Vendor recommends not taking Log backups but copy just log files over other location. will this help avoid degrading the performance during business hours.

if i don't take log backups, i am not able to recover Point in time if needed.
also log files can then grow faster and then i will have to shrink it more often.

what are risks and suggestions.

Thanks,

View 5 Replies View Related

Database Backups And I / O Frozen Events

Jun 5, 2015

I am trying to find out where is the maintenance plan which is backing up SQL Dbs on its own at 12 am daily where as we didn't scheduled maintenance plans at all. We see I/o frozen and resume events every day in event l/o

Log Name:      Application
Source:        MSSQL$MSSQLSERVER2K8
Date:          5/4/2015 12:00:23 AM
Event ID:      3198
Task Category: Server
Level:         Information
Keywords:      Classic

[Code] ...

View 6 Replies View Related

Named Backups Using Database Maint Wizard

Aug 20, 1999

Has anyone been able to find a way to name the files within your backup using the Database Maintenance Wizard in SQL 7.0?

Thanks for your input?

View 2 Replies View Related

Copied Database Maintains Original Name In Backups

Mar 27, 2007

I made a copy of a database "sac_prod" and named the new copy "vgs_prod". Now, when I do a backup of the new database, it still shows the name of the original. Is there any way to change this so it will be the same as the new database name?Here is the BACKUP script:BACKUPdatabase vgs_prod TODISK='\sac-srvr1data$TechnicalSharedProductionSQLBackup LasVegasvgs_prod_CopyOnly.BAK' with COPY_ONLYHere is the messages I received from this BACKUP:Processed 1752 pages for database 'vgs_prod', file 'sac_prod' on file 1.Processed 6 pages for database 'vgs_prod', file 'sac_prod_log' on file 1.BACKUP DATABASE successfully processed 1758 pages in 0.412 seconds (34.955 MB/sec). I would like to change the file 'sac_prod' to be 'vgs_prod' in lines 1 and 2 just above. Thanks,

View 2 Replies View Related

Veritas Not Truncating Log Files After Database Backups

Jan 8, 2007

Michael writes "We are running SQL and Veritas to backup the databases. Supposedly the SQL agent in Veritas, after a full backup, truncates the log files but for some reason this isn't happening... any ideas?"

View 4 Replies View Related

Error Restoring Database With Diff & Inc Backups

Sep 10, 2007

I backup a database at the begining of each month with a full and then do nightly diffs on it.

For the same database I run daily fulls and 10 minute log backups.

these two backups create / append to two different backup files.

The problem im having is that I cant restore the Differential backup set. SQL seems to restore the full just fine but alwasy throws an error when its about to start to retore the last diff. now forgive me but I clicked OK on the message and I cant find any record of the error in the logs but its something like:

"SQL cannot restore the database as the database has not been restored to the previous correct state"

is my 10 min TS log backups screwing up the DIff chain somehow?

this is really doing my head in. any help appreciated.

"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.

View 1 Replies View Related

Load SQL 7 Database And Transaction Log Backups To SQL 2000

Jul 20, 2005

Is it possible to load both the SQL 7 database and transaction logbackups to SQL 2000 ? I assume it will perform the upgrade during theload.Thanks,James

View 4 Replies View Related

Is It OK To Copy Database Backups To Tape Using Compression?

Dec 27, 2006

Hi, I back up SQL Server 2000 and SQL server 2005 databases to hard disk using the SQL Server Backup Wizard and maintenance plans. Then, I copy the resulting backups to tape using third party tape backup software and compression by the backup software and hardware. I do not use the SQL Server Agent available for the third party backup software. Is this acceptable, or does the compression performed by the third party backup system introduce opportunities for database corruption or other negative effects?

Thanks

View 1 Replies View Related

SQL Server 2008 :: How To Check If The Database Itself Is Encrypted Or Only The Backups

Jul 9, 2015

I queried sys.databases in one of the sql server and found "is_encrypted" is "1" for four of the databases.

Does that mean that all those 4 DBs are encrypted ?

How to check if the database itself is encrypted or only the backups ?

Note :- I can see backups of key in a particular folder.

View 9 Replies View Related

Verify Multiple Database Backups Using Single Statement

Oct 25, 2007

Hi all,


I have taken more than one database backups and i have an idea that we can verify single backup using "restore verifyonly from disk='<path>'" statement.
Now my question is Could we verify all(More than one backup) backups with single statement?
Any suggestion would be helpful to me:-)


Thanks in advance,

View 3 Replies View Related

What Procedures Need To Be Done On A SQl Sever 2005 Database Before Commandline Backups Can Be Successfull

Jun 12, 2007

I have a sql sever 2005 express edition database but when i run my backup command from the command prompt, i get this error message .
The .Net SqlClient Data Provider has received a severity 16, state 11 error number 911 on line 1 of procedure on server KSS15SQLEXPRESS: Could not locate entry in sysdatabases for database 'DATABASE1'. No entry found with that name. Make sure that the name is entered correctly. The .Net SqlClient Data Provider has received a severity 16, state 1 error number 3013 on line 1 of procedure on server KSS15SQLEXPRESS: BACKUP DATABASE is terminating abnormally.
DATABASE one is the name of the database i want to backup. Now it looks there so many databases some of which i have no idea where they are. It looks like there some system stored procedures i have to run first before my database file can be located by my commands. It would be wonderfull if some one pointed me to an article that covers this initial part thoroughly.

View 3 Replies View Related

SQL Tools :: Lost All Objects Suddenly Only Remains Name Of Database No Backups Available

Nov 8, 2015

I recently installed a trial version of Sql12k on my laptop. Since 1st of November I've been creating tables without problems (15) and different schemas.I would like to emphasize that both mdf and ldf files are allocated in my USB3 portable disk. is that a problems for Sql12K? SSMS had stuck just for 20 sec or so. Then, system itself kill the task. A huge surprise when I restarted again... all the databases including the last one.. without objects. I mean, user databases...

Where are my logins? Where are my tables and schemas? Jesus christ.Sql logs inform the following (in all the moment my USB hardisk was properly connected)

11/08/2015 16:00:30,spid19s,Unknown,The operating system returned error 21(El dispositivo no está listo.) to SQL Server during a read at offset 0x000000 00140000 in file 'f:enriccopia_de_bbbb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency

[code]....

View 4 Replies View Related

Restoring Database With Backups Files(Data And Log) Having Different Creation Times....!

Nov 19, 2007

Hi,

I am cleaning database backup history as "Delete history if 4 months old" for backup file, and "Delete history if 1 week old" for Log file back.

lets say, I have disaster after 3 months, and I recover database with data and log backup files. Will I be able to recover with 3 month old backup file with just 1 week Log file backup. As I am keeing my Log backups only for 1 week.

Thanks,

View 5 Replies View Related

Pros And Cons Of Using Transaction Replication Doing Initialization From Database Backups

Jun 27, 2007



I am using transaction replication between a transaction and reporting database server. When I use a snapshot to initialize my subscribers, I currently get a lot of deadlocks during the snapshot creation. I am considering using a database backup instead. Can anyone tells me how to reduce the table locks that I am getting during snapshot creation or advice on using database backups?



View 1 Replies View Related

SQL Server Admin 2014 :: Database Backups And AlwaysOn Availability Groups

Oct 16, 2014

This is my first deployment of an always on availability group for SQL 2014 and I'm trying to get my custom backup procedure to handle all databases appropriately depending on the primary group. Basiscally I want the system databases and all databases that don't participate in the availability group to be backed up on both nodes and those that do participate backed up ONLY on the primary server. I've looked at the sys.fn_hadr_backup_is_preferred_replica funcation, but would like to only have to test for a single databases existance in the availability group. If the one database is in the group, only backup the system databases and those that don't participate, otherwise backup everydatabase. This would be the case for both full backups and transaction logs.

View 1 Replies View Related

Backups

Jan 14, 2007

 
Hi,
I'm trying to work out how to backup an ASP.NET 2.0 site which uses an SQL 2005 Express database located in the App_Data directory. It seems that the database file cannot be copied while in use so I guess that either leaves taking the site offline or maybe the database can be backed up to another location using a scheduled task?
Any suggestions?
Thanks,
Dale

View 9 Replies View Related

SQL 7 Backups

Mar 15, 2002

What backups options do you have if you do not have a tape drive connected to your SQL 7.0 Server ? Can you run a SQL Maintenance Plan and backup the databases to a remote server. If not, can ARCServe 2000 backup the SQL backups to another server with a tape drive ?

View 1 Replies View Related

Backups

Jun 27, 2001

I'm having trouble backing up two databases to tape. Should I be using a backup_device?

have a job defined with the following in it:

backup database test1
TO TAPE = '.Tape0'
with ...

go

backup database test2
TO TAPE = '.Tape0'
with ...

go

where should the init go?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved