Creating Job To Do Daily Backup
Jun 28, 2006
I have a client that is using the free MSDE database engine. There is no graphical interface that I know of to manage the day-to-day tasks. I need to do a daily backup to a disk file. I want to start the backup at 7:40pm. My client then will copy that disk file to tape duing his system-wide backup at midnight. I setup a batch file and executed it in osql but the job is not working. No disk file has been created in the last 3 weeks. I don't have a clue what I am missing. Can someone please look at this and tell me how to fix it??
Thanks in Advance!
Debbie Erickson
USE master
EXEC msdb..sp_delete_jobserver
@job_name='PetDB Backup',
@server_name='Server06'
GO
USE master
EXEC msdb..sp_delete_jobschedule
@job_name='PetDB Backup',
@name = 'ScheduledBackup'
GO
USE master
EXEC msdb..sp_delete_jobstep
@job_name='PetDB Backup',
@step_id=0
GO
USE master
EXEC msdb..sp_delete_job
@job_name='PetDB Backup'
GO
USE master
EXEC msdb..sp_add_job
@job_name='PetDB Backup',
@enabled=1,
@description='Backup Petdb'
GO
USE master
EXEC msdb..sp_add_jobstep
@job_name='PetDB Backup',
@step_name='Nightly petdb maint',
@subsystem='TSQL',
@command='BACKUP DATABASE Petdb TO DISK = ''E:PetlicData BackupPetdb.bak''',
@database_name='petdb'
GO
USE master
EXEC msdb..sp_add_jobschedule
@job_name='PetDB Backup',
@name = 'ScheduledBackup',
@freq_type=4,
@freq_interval=1,
@active_start_time='194000'
GO
USE master
EXEC msdb..sp_add_jobserver
@job_name='PetDB Backup',
@server_name='Server06'
GO
View 3 Replies
ADVERTISEMENT
Nov 6, 2005
I want to be able to create a duplicate database and update / refresh it daily automatically. This database copy would be used for testing purposes so I don't want it to write back to the original database. Is replication of some kind the answer?
View 4 Replies
View Related
Dec 2, 2006
Hi All,
Is anyone can show me how to setup sql server to do backup daily automatically?
TIA
View 2 Replies
View Related
Jul 29, 2015
I need to schedule daily backup at 6:00am. how to do this. I'm on Sql Server 2008r2
View 1 Replies
View Related
Jan 7, 2008
Hello all.
Let me start by saying that I am more acquainted with a LAMP set up than using Windows/ASP/MSSQL.
I have recently taken over managing a friends website after he had bad experiences with his last web dev and have also had to transfer to a different host as well. The previous dev sent all the site files and database but the database was in MS Access file format [.mdb].
The new hosts required it as an MSSQL backup file [.bak] and I spent several hours of my weekend downloading and installing SQLExpress, reading up on migrations and backups before actually performing one. I uploaded it to the site, as instructed by the new hosts, who would then install it on the SQL server for me.
I received this reply back from them and need some assistance deciphering it. I tried searching for a bit yesterday but found nothing.
Anyone know what this means?
"Apologies for replying late.We have tried to restored backup file dmt.bak , but we could not restored it due to incompatibility of disk structure version,While we tried to restored it we have received following error'
"The backed-up database has on-disk structure version 611.The server support version 539 and can't restored or upgrade this database "
Please provide correct ,bak file with correct version , so we go ahead and restored it for you.".
Thanks for any help anyone can give.
./chris
View 3 Replies
View Related
Dec 6, 2007
Hi,
I have my MSSQL database developed in Visual Web Developer 2005 Express Edition. I am trying to create a backup file of this databse (.bak). Is SQL Server 2005 Studio Express the program that will let me do this?
Also in SQL Server, how can I attach to my database that I have made in Web Developer 2005? if I go to attach it doesn't list anything.
Thanks for any help.
View 1 Replies
View Related
Feb 16, 2004
Hi genius minds,
Suppose I have database backup file.
Can I create a new database(different than the database whose backup it is) and restore the backup into newly created database.
I'm having a backup file of some database, I want to create database from it. Suggest any way to do it?
Thx
View 4 Replies
View Related
Dec 19, 2000
Hi,
How can we create a new D/B from a D/B Backup file (DataBase.Bak).
Thanks,
Princy
View 3 Replies
View Related
Oct 16, 2000
Hi,
I am new to SqlServer (I am using 7.0) and I have just recieved a database.bak file from one of our clients and I want to create a new D/B from this back up file ,I have already installed the MS SqlServer. Can you tell me how to do this.
Thanks,
Princy
View 1 Replies
View Related
Oct 23, 2006
is there a way to create one backup from multiple databases ?
View 3 Replies
View Related
Mar 19, 2008
HI guys.
i am trying to create a SP to create a job to take a differential backup. but i m hving a problem . can i any one help me.
what i m doing , is to create job with the same name as the database name ...
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name= N '' + @DatabaseName + ' Differential Backup',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@database_name=N '' + @DatabaseName + ',
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'Phil', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Differential Backup',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N 'BACKUP DATABASE '' + @DatabaseName + ' TO database_Differential WITH DIFFERENTIAL,INIT',
@database_name=N'master',
@flags=0
i m getting this error msg
Msg 102, Level 15, State 1, Procedure CreateDifferentialBackup, Line 23
Incorrect syntax near ''.
View 4 Replies
View Related
Apr 20, 2005
I'm building an ASP.NET application that uses SQL DMO to automate database dumps. This will be used by our DBAs to simplify the transfer of databases between server environments.
As part of the process, I need to create a backup of an online database and then restore with a different name to run cleanup scripts against it to remove unneeded audit data, etc. I've tried setting objSqlBackup.BackupSetName to accomplish this, but it appears the backup files retain the original database name internally. When attempting to restore the backup, this results in an error saying that the database already exists.
Does anyone have a code sample or link that explains how an alternate name can be specified? In SQL Server Enterprise Manager, there is a field for specifying an alternate name when creating a backup. Given that SQL DMO is supposed to offer the same functionality, there must be a way...
FYI, The full process is as follows: - Create backup of selected database - Restore database with a different name (this is where I need help) - Run cleanup scripts to clear out unnecessary audit data, etc. - Create backup of "cleaned" database - Create zip file of database backup
Thanks in advance for any help you can provide!
View 1 Replies
View Related
Mar 30, 2006
Hi,
I have created a certificate and now have taken a backup of this file by the following code:
backup certificate EncrProdCode to file = 'C:BackupCertEncrBackup.cer'
with private key(file = 'C:BackupCertEncrPrivKeyBackup.pvk',
encryption by password = 'EncrPrivKeyBackuppwd')
Now i want to create a certificate IN DIFFERENT DATABASE from this file. i m using the following database:
create certificate EncrFromFile from file = 'C:BackupCertEncrBackup.cer'
with private key(file = 'C:BackupCertEncrPrivKeyBackup.pvk',
decryption by password = 'EncrPrivKeyBackuppwd')
But is showing error:
Msg 15232, Level 16, State 1, Line 1
A certificate with name 'EncrFromFile' already exists or this certificate already has been added to the database.
No certificate with this name exists. why i m getting this error. and how can use the same certificate in other database. Is there any other way to create a certificate from an existing certificate file.
Thanks
Gaurav
View 4 Replies
View Related
Nov 9, 2006
Hi,
I want to create two databases by restoring from a
single backup file in sql server. I am using 2005-sqlexpress .Is it possible?
Thanx in advance..
View 3 Replies
View Related
Nov 13, 2007
Forgive me if I'm asking a simple question, but I'm new to database administration
I'm trying to migrate a database from a server running Windows 2000 Server and SQL Server 2000 to a new machine running Windows Server 2003 and SQL Server 2005. I moved a copy of a recent database backup file to the new server, and created a new database with Restore From Backup. The tables seem to have restored fine, but my views are non-existent. Has anyone seen this problem before, or does anyone know of something I may have skipped?
View 3 Replies
View Related
Jul 23, 2014
I am attempting to create a Test db from a full backup of the production db. With 2012, I cannot do it the the way i had done it in previous versions (and now i understand why because of Logical names).
The Test db runs in the same instance as Prod db.
I attempted to run this but come up with errors. This is what i executed:
RESTORE DATABASE TEST FROM DISK = 'E:<path>FULL.BAK'
WITH REPLACE, RECOVERY,
MOVE 'PROD' TO 'E:<path>TEST.MDF';
The errors are all cannot execute due to PROD is in use.
View 9 Replies
View Related
Jun 19, 2015
Having a lot of problems with backup device creating backups with a new transaction log for each day. This is causing the backups to grow way to fast. Seems to be random with our clients. Created new device backups but getting same problem. A manual backup selecting overwrite all existing backup sets will fix it. But starts the cycle all over again.
View 9 Replies
View Related
Nov 13, 2007
Hi All,
any suggestion on creating date time stamped files names for backup file.
I want to create new file for daily backups with date time stamp so i can use Maintenance plans to clean older files in each 4 month cycle.
thanks,
View 8 Replies
View Related
Jul 20, 2005
Coming from Oracle background:How do you carry thiese admin tasks in SQL 2000 Environment :Check instance availabilityCheck listener availabilityCheck alert log files for error messagesClean up old log files before log destination gets filledAnalyze tables and indexes for better performanceCheck tablespace usageFind out invalid objectsMonitor users and transactionsThanks for help.
View 1 Replies
View Related
Dec 31, 2007
Ok this may have been answered somewhere but to be honestly I don't even know what I need so its hard for me to search for it. Here is what I have going on lets see if anyone out there can answer this one. I have a 20 year old system that is tied to some lab equipment that we use for coal testing. The equipment runs back to an old win98 PC which is running a DOS program that has long since been abandoned by the manufactures. I can't upgrade the program because it will mean upgrading the hardware which is somewhere in the neighborhood of $100k at least. The program saves everything in what I believe is a dBase IV file (.dbf) and using Excel I can easily see the data inside. What I am trying to do, if I can figure out a way other than writing my own custom app, is to do a daily export/import of the data from the dbf file into a more usable database, then create some spiffy forms to mess with the data. Each day the dbf file is wiped out by the ancient program and a new one is created. My database experience is somewhat limited I have at best a rudimentary knowledge of SQL but I should be able to follow along even if I have to do a little research. Mainly what I am looking for is a way to import the dbf file into an already existing database (new or existing table) automatically via a script or something. I want to retain all the data from past days with out editing the the original DBF file. Can this be done or am I just asking the impossible?
Thanks for your time,
Donavan
View 8 Replies
View Related
Jun 6, 2007
Just wonder what do you dba do in your daily tasks. I usually have meetings where I have to say what Ive been doing during the week.
=============================
http://www.sqlserverstudy.com
View 1 Replies
View Related
Nov 14, 2007
Hi: I am having a problem with the Syntax. I am trying to get the daily value for a contract_nbr. The selection below shows that I am selecting by Case statement when day = 2 (2nd Day) and the month is a parameter provider by the user. For this particular example, I am using 2 (February).
Thanks for the help !!!!
[code]
Set @Req_Month = '2'
SELECT Distinct a.contract_nbr,
Case when Day(c.Beg_eff_date) = 2 and month(c.Beg_eff_date)= @Req_Month
Then c.rcpt_nom_vol-c.rcpt_fuel-rcpt_act_vol
Else 0
End As Day_2
from TIES_Gathering.dbo.contract a
Inner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date)
and (c.rcpt_dlvry_ind ='R')
Group by a.contract_nbr
[code]
View 6 Replies
View Related
Mar 19, 2008
Hello All,
What should i have to check as a DBA at every new day. Is any one having scripts for the checklist then please send me that scripts.
Thanks
Prashant Hirani
View 8 Replies
View Related
Aug 10, 2006
In practice, I find encryption kind of messy to use -- opening, closing keys, use of certificates, all the while trying not to give away the password in cleartext. If our .NET programmers are to use it efficently in high-level code we need a function call, say "EncryptValue(<input value>)", that simply returns the encrypted value. Is it possible to write such a function? (And of course, we need the mate "DecryptValue(<encrypted value>)".
TIA,
Barkingdog
View 9 Replies
View Related
Dec 8, 2007
How can I return daily running totals for each day:
TABLE:
date: # of downloads
1/1/2007 100
1/1/2007 12
1/1/2007 8
1/2/2007 100
1/2/2007 20
1/2/2007 20
1/3/2007 40
example of what I want:
RESULTS:
date number of downloads total
1/1/2007 120 120
1/2/2007 140 260
1/3/2007 40 300
I want to return a running total value for each seperate day.
View 4 Replies
View Related
Jan 31, 2008
Hi there
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
Thanks
Jane
View 3 Replies
View Related
May 9, 2008
I have a database with multiple items each day. I'm looking to extract monthly information from this data. So I'm thinking that I would like to have a drop down list with each available month in the list. How would I extract which months (for multiple years) have data from a datatype = smalldatetime?
I'm using c#
Thanks!
View 9 Replies
View Related
Apr 3, 2000
I have to import data into 2 tables on a daily basis.
The data is provided as a flatfile.
In order to fullfill this task the tables have to be truncated first.
Are there any possibilities to do this job automatically with dts, or do I
have to write an Interface
in VB or something like that?
Thanx
Michael F.
info@sunguard-explorers.de
View 2 Replies
View Related
Jan 28, 2015
I am not a SQL Server expert, normally work with a few other databases. We are running SQL Serer 2008 R2. I need to determine by day how much log space is being used. This is needed so I can size the file system appropriately to handle an outage of our backup system. The goal would be to have the log file system large enough to handle 24 hours worth of logs. I have found statements for current log usage, but not one for daily total logs generated.
View 1 Replies
View Related
Jan 9, 2004
Hello DBA's:
I need to extract a particular file from our AS 400 system on a daily basis and do some processing on it. Also I want to do the daily processing only on those records that have been added/updated since the initial load.
Here is the approach and possible implementation.
Approach
I have the DB2 source data containing 10 columns (Col1 to Col5 together form the key) and the rest are attributes. I am interested only in the key and two attributes. So I load my table with only Col1 to Col7 ( 5 for key and the two attributes). I then do my processing on this table.
Here is the implementation given by a member of dbforums -
You'll then have to deal with 3 potential actions,
INSERT: New records on the file.
DELETES: Records that don't exists.
UPDATES: Records that are on the file, but attributes have changed.
You had given me this code template.
CREATE TABLE myTable99 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2))
CREATE TABLE myTable00 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable00_pk PRIMARY KEY (Col1, Col2))
GO
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL
SELECT '1','2','d','e','f' UNION ALL
SELECT '1','3','g','h','i' UNION ALL
SELECT '1','4','j','k','l'
--DELETED
INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL--NO CHANGE
SELECT '1','2','x','y','z' UNION ALL-- UPDATE (My comment - Instead of an update I want to insert a new record)
SELECT '1','3','g','h','i' UNION ALL--NO CHANGE
SELECT '2','3','a','b','c'--INSERT
GO
SELECT * FROM myTable99
SELECT * FROM myTable00
GO
--DO DELETES FIRST (My comment - Before deleting, I want to copy the rows that I am going to delete on a separate table to maintain history. Then I want to delete from a). I don't get the logic. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =. why the where clause condition)
DELETE FROM a
FROM myTable99 a
LEFT JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL
-- INSERT (My comment - I don't get the logic of the where. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =)
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
FROM myTable00 a
LEFT JOIN myTable99 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL
-- UPDATE
UPDATE a
SET Col3 = b.Col3
, Col4 = b.Col4
, Col5 = b.Col5
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
AND ( a.Col3 <> b.Col3
OR a.Col4 <> b.Col4
OR a.Col5 <> b.Col5)
GO
------------
Can anybody look at My comments and answer them or revise this code template if need be?
Brett Kaiser - I sent you an e-mail on this. Can you respond to it when time permits.
Thanks
View 9 Replies
View Related
Apr 21, 2004
Hello -
How can I truncate log files of all Databases daily automatically.
Thanks
View 4 Replies
View Related
May 12, 2004
Ladys, Gentlement, I have table that grows anywhere from 200,000 to 1,000,000 records perday. Besides that I need to keep at least 6 months historical data from this same table. The transaction log was purged after each batch when testing data monthly. I'm looking for some way of deleting just one day's data if it meets a criteria. It must remain within the 6 months period of historical data. This is what I've come up with so far"
select * FROM dbo.Temp_table WHERE datediff(day, DATE_TIME, getdate()) >= 180
If it meets this criteria I can change the select to a delete? Please Let me know what you think
View 10 Replies
View Related
Jun 10, 2008
I am trying to Invoice all of the records in my 'Orders' table. After each of the records has been invoiced I would like SQL to flag that record as having been completed so that when I run my query the next day it will ignore those having been completed already. Any feedback would be greatly appreciated.
This is the query I wrote to invoice one Order at a time by specifying each Order_Num seperately. As you can tell...I'm a n00b. Thanks all.
select convert(varchar, getdate(), 107) as Date
select order_num as 'Invoice No.' from orders
where order_num = '20009'
select c.cust_name as Customer, c.cust_address as 'Street Address',
c.cust_city as City, c.cust_state as State, c.cust_zip as 'Zip Code'
from customers as c, orders as o
where c.cust_id = o.cust_id and order_num = '20009'
select oi.order_item as 'Line Item', oi.quantity as QTY, p.prod_name as 'Product Name',
oi.item_price as 'Sale Price', oi.quantity*oi.item_price as Total
from orderitems as oi, products as p
where oi.prod_id = p.prod_id and order_num = '20009'
order by oi.order_item
select sum(quantity*item_price) as Subtotal, sum(quantity*item_price*.089) as 'Tax 8.9%', sum(quantity*item_price)+ sum(quantity*item_price*.089) as Total
from orderitems
where order_num = '20009'
View 9 Replies
View Related