While checking the SQL server error logs, I notice that the pubs and msdb database are automatically being backed up, even though no job is set up to do so....in addition, its backing up to a directory that I cannot find on our network.....does anybody have an idea of whats going on ?
the path its backingup to is:
(FILE=1, TYPE=PIPE: {'.pipedbasql70dbagent0s0'}).
Thank you in advance
Why can I no longer backup a database to a named pipe in SQL Server 2005? Named pipes are much easier to work with than VDI (and VDI is covered in obscurities). I really want the ability to dump a database to a named pipe reimplementation if it€™s been removed. I hate to think of the cost of redesigning our backup applications. Can I force a PIPE dump device into the sysdevices table? Any suggestions? (and DO NOT tell me to use VDI, as it is absurdly cumbersome and loaded with unnecessary dependencies)
BACKUP DATABASE <name> TO PIPE=\.pipeMyPipeName
how it is possible to take differential backup of database automaticaly in sql server 2k?
plz someone help me out.
thanks
Rahul Arora
07 Batch
NCCE Israna,
######################
IMPOSSIBLE = I+M+POSSIBLE
Hi,How to automate database backup (MSDE server v8.0)? Is some free toolwhich can help on this or can I use some stored procedure? Plan:Complete - 1 per weekDifferential - 1 per day--*Best regards,*Klaudiusz Bryja
View 3 Replies View RelatedWhat is the best way to restore a database from a folder of backups (including full, diff and log backups) without using the backup history in msdb?
I have a restore process that restores all backups on a regular schedule in order to fully verify their integrity. To do this, I use the backup history in msdb on each server that I'm monitoring. I had a thought the other day that I would be in trouble if I lost msdb. Then my backup history would only be as good as the last backup of msdb.
What I'd like to do is read a folder of backup files and generate a restore script up to a specified time. Would I use RESTORE HEADERONLY to do this? If so, would I use PowerShell to traverse each file in the folder?
I have placed the pubs.mdf in the same folder as my asp page and have changed the connection string to : Dim ConnectionString As String = "server=(local);database=pubs.mdf;trusted_connection=true"
However, when I run this page, I get this message:
Server Error in '/' Application.
SQL Server does not exist or access denied.
What does the ConnectionString need to be for this to work?
Canning
http://msdn2.microsoft.com/en-us/library/aa238305(SQL.80).aspx
how can i List the title name, year of order, state and total quantity for all sales?
Some of solutions are below but not for the above question.
-- list the titles by total sales price.
select
title,
sum(s.qty * t.price)
from titles t
join sales s on (s.title_id = t.title_id)
group by title
order by 2 desc
-- list the titles by total sales qty
select
t.title,
sum(s.qty)
from titles t
join sales s on (s.title_id = t.title_id)
group by t.title
order by 2 desc
-- list the stores and titles by sales price.
select
st.stor_name,
t.title,
sum(s.qty * t.price)
from titles t
join sales s on (s.title_id = t.title_id)
join stores st on (s.stor_id = st.stor_id)
group by
st.stor_name,
t.title
order by 1,3 desc
This is how i List the titles and author names, in general sql, but i have no idea using cursor. So, can anyone help me to create a cursor that loops through the authors table.
select
au_lname,
au_fname,
title
from
authors a
join titleauthor ta on (a.au_id=ta.au_id)
join titles t on (ta.title_id=t.title_id)
below is the ddl for the three tables.
CREATE TABLE [dbo].[titleauthor](
[au_id] [dbo].[id] NOT NULL,
[title_id] [dbo].[tid] NOT NULL,
[au_ord] [tinyint] NULL,
[royaltyper] [int] NULL,
CONSTRAINT [UPKCL_taind] PRIMARY KEY CLUSTERED
(
[au_id] ASC,
[title_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[titleauthor] WITH CHECK ADD FOREIGN KEY([au_id])
REFERENCES [dbo].[authors] ([au_id])
ALTER TABLE [dbo].[titleauthor] WITH CHECK ADD FOREIGN KEY([title_id])
REFERENCES [dbo].[titles] ([title_id])
CREATE TABLE [dbo].[titles](
[title_id] [dbo].[tid] NOT NULL,
[title] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[type] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('UNDECIDED'),
[pub_id] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[price] [money] NULL,
[advance] [money] NULL,
[royalty] [int] NULL,
[ytd_sales] [int] NULL,
[notes] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pubdate] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [UPKCL_titleidind] PRIMARY KEY CLUSTERED
(
[title_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[titles] WITH CHECK ADD FOREIGN KEY([pub_id])
REFERENCES [dbo].[publishers] ([pub_id])
CREATE TABLE [dbo].[authors](
[au_id] [dbo].[id] NOT NULL,
[au_lname] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[au_fname] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[phone] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('UNKNOWN'),
[address] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[city] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[state] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[contract] [bit] NOT NULL,
CONSTRAINT [UPKCL_auidind] PRIMARY KEY CLUSTERED
(
[au_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[authors] WITH CHECK ADD CHECK (([au_id] like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'))
GO
ALTER TABLE [dbo].[authors] WITH CHECK ADD CHECK (([zip] like '[0-9][0-9][0-9][0-9][0-9]'))
Need help.After I installed MSDE. I opened a command prompt and navigate to C:Program FilesMicrosoft Visual studio .NET 2003SDKv1.1SamplesSetupAnd then I typed in: oSpl -S(local)VSdotNET -E -i instpubs.sqlI got the message:[DBNETLIB]SQL Server does not exist or acdcess denied.[DENETLIB]ConnectionOpen (Connect()).How can I fix it?The server 2000 is running and it's mixed login authentication.OS: windows server 2003Thank you,William
View 5 Replies View RelatedHello:I am in the middle of an asp.net 1.1 book and I ran into a problem that I can not seem to find an answer for.I am able to build an asp.net web form in Visual Studio.net and I am able to connect to my local SQL Server that is running on the same computer through Visual Studio, but when I go into build and preview my page in the browser, I get the "Error reading the database. [DBNETLIB][ConnectionOpen(Connect()).] SQL Server does not exist or access denied." Has any one been able to connect to pubs locally, or can any one give me a suggestion to what I can try? Any help would be greatly appreciated.
View 5 Replies View RelatedHi all,
I have a stand-alone Windows XP Pro PC that has SQL Server Express (SSE) and Visual Web Developer Express (VWDE) programs. The Microsoft "pubs" Database is installed in the SQL Server Management Studio Express (SSMSE). I created a website in my VWDE program. I need the .mdf file of the pubs Database in the App_Data folder of website of my VWDE project. I think that User Instance in my SSE is established. I have studied Xcopy Deployment (SQL Server Express) and User Instance for a quite a while and I still do not know where and how to use Xcopy to get the mdf file of the pubs database into the App_Data folder of the website of my VWDE project. Please help and give me the detailed key steps/instructions about where and how to get the .mdf file of the pubs database into the App_Data folder of the website of my VWDE project via Xcopy.
Thanks in advance,
Scott Chang
Hi,
We are working on creating an automatic backup tool for our web application. Our goal is to run a script that "zips" the virtual host/application directory. We have the script to zip the application directory, but is there a way to run a SQL Backup and then zip the output easily? This is where we are struggling.
Any Suggestions are appreciated
Brent
I have a problem with a PIPE "|" delimited flat file. I have a column "Description" in which we get a string in which we have PIPE "|" as data. How we can skip this and load it as a data into the column Description.
View 7 Replies View RelatedThanks to every participants, I am oracle dba and rightnow looking for some tips which are avaialble in Oracle in MSSQL Server 2000.
1. How I can schedule job in MSSQL Server 2000.
2. How I can schedule database backup for no. of co's.
Thanks : ;)
I have only started using SQL Server 2005 Express Edition recently.I also installed Management Studio Express version to manage the database. While testing the database I had came across some question and they are:
Can I upgrade from SQL Server 2005 Express to Workgroup/Standard/Enterprise without loosing the database and its contents.
While I backed up the database, I was permitted to backup only on to my local drive even though I have administrative rights on the system. It would be easy if I could backup to a network drive directly.My question is:Is it possible to backup directly to a network drive rather than backing the database to a local drive and then "copy and paste" it to a network drive.
Windows Scheduler: I have scheduled a daily backup of the database by midnight. I have also written the script to run the backup. But every time the scheduler starts the backup, it asks for the database password. This halts the backup until I come back in the morning and manually enter the password. My question is: is it possible to run an automatic database backup using Windows Scheduler?
I understand that there are 2 types of backups: full and differential backups. My question is: Is it possible to dynamically allocate different names automatically to consecutive backups so that the previous backups are not over written
Thanks for your patience and time.
regards,
Berly Sam
I have SQL Server 2008 R2 Express Edition on a server.I need to backup my database (small db) on a daily basis.Since Express Edition does not allow automatic backups what can I use to do this backup?
View 9 Replies View RelatedIs there any way to have an automatic backup of a database in SQL Server Express?
View 5 Replies View RelatedI am facing the Database recovery problems sicne I have lost my MSDB db , only i have the Complete db backup and transactional backup.
How do i can recover the database since i don't have the MSDB database
Can I tell from the MSDB tables if a particular db backup was successful? SS2000.
Thanks,
Michael
I'm getting "Executed as user: SPIESQLService. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." on the TRN backup portion of the maintenance plan for the msdb and model databases. On review of files created it's clear that the msdb trn log backup is failing, but there's no other error to indicate the underlying problem.
Suggestions?
TIA,
Al
Our sysadmin accidentally uninstalled SQL Server started to panic and reinstalled. Thankfully the data/transaction files to our important databases were still present and I simply reattached them but our DTS packages our gone.
However, we've done weekly backups of the msdb database. How do I get the DTS jobs out of these backups?
In one off my production box, we are notable to take a backups of MSDB . When i look at the error, it is failing locate allocation unit ID.. complete error as below
Msg 2533,Sev 16,State 1, Line 36 : Table error : Page (1:111720 ) allocated to object id 110623437, index ID 1, Partition ID 72057594043432960, alloc unit ID 72057594044874752 (type-inrow data) was not seen. This page is invalid or may have an incorrect alloc unit ID in its header.[SQLSTATE 42000]
Due this failure, we are unable to take the backup of MSDB database and our integrity check and reindex jobs also failured with the same.Also, I could see events of I/O issues with underlaying hard dirve with following name
DeviceHarddisk0DR0,has a bad block.
1. I dont no what could happen if restart my server, Question is: Does it recognize MSDB during server statup.
Hello, we are running MSSQL 2005 express on a windows 2003 machine. I have looked in SQL Server Management Studio for close to an hour and could not find a way to setup automatic backups of databases. Where is this feature, and short tutorial would be great. Thanks!
View 1 Replies View RelatedI have a full backup followed by transaction log every Monday, Wednesday and Friday, how can i restore this file using sql agent to automate restoration of backup files with different file-name.
View 6 Replies View RelatedI have been researching on how to cleanup four backup system files located in the MSDB database. I currently have approximately 1 million rows in each of these tables which is making my MSDB database to be 4 gig in size. I cannot find any instructions on how to gracefully clean these tables up or what to do to keep the number of rows down in these tables. The tables are backupfile, backupmediafamily, backupmediaset, and backupset. Thanks.
View 1 Replies View RelatedI have inherited a SQLS erver 2000 instance where the client neverimplemented a backup startegy for the "master" and "msdb" databases.MSDB is now showing errors and my only option for a restore is from atape backup of the server.Any thoughts..
View 1 Replies View Related
Hello,
We have a self-written procedure to restore transaction logs on a standby server (Sql Server 2000 sp4)
We do this by joining 2 msdb tables, to find out which backups have been performed: backupmediafamily and backupset.
Sometimes backups are NOT registred in table msdb..backupmediafamily.
Underneath 2 examples.
OK- underneath queries shows the time the backup was created in the physical_device_name; joined with media_set_id
99% shows these correct data
select media_set_id, physical_device_name from backupmediafamily where media_set_id = 258716
media_set_id physical_device_name
258716 H:Mssql2000MSSQLBACKUPPAPPS10_tlog_200701010030.TRN
select media_set_id, backup_start_date , backup_finish_date, database_name from backupset where media_set_id = 258716
media_set_id backup_start_date backup_finish_date database_name
258716 2007-01-01 00:30:08.000 2007-01-01 00:30:08.000 PAPPS10
NOT ok
select media_set_id, physical_device_name from backupmediafamily where media_set_id = 376467
media_set_id backup_start_date backup_finish_date database_name
376467 2007-02-17 09:00:14.000 2007-02-17 09:00:14.000 PDBTIME
376467 2007-10-31 19:15:05.000 2007-10-31 19:15:05.000 PJAVA
select media_set_id, backup_start_date , backup_finish_date, database_name from backupset where media_set_id = 376467
media_set id physical_device_name
376467 H:Mssql2000MSSQLBACKUPPDBTIME_tlog_200702170900.TRN
!!! No record for backup transaction log database PJAVA
select media_set_id, physical_device_name from backupmediafamily where physical_device_name like '%PJAVA%2007103119%'
media_set_id physical_device_name
1115347 H:Mssql2000MSSQLBACKUPPJAVA_tlog_200710311900.TRN
1115432 H:Mssql2000MSSQLBACKUPPJAVA_tlog_200710311930.TRN
1115475 H:Mssql2000MSSQLBACKUPPJAVA_tlog_200710311945.TRN
Missing record for backup made on 2007-10-31 19:15 hours !!!!
Hope somebody can help me.
Best regards,
hvdtol
Hello all,
I need help concerning a differential backup on a MSDB database.
I received this message when I tried to backup it
Log from Windows
Event Type: Error
Event Source: SQLVDI
Event Category: None
Event ID: 1
Date: 8/1/2007
Time: 2:03:37 PM
User: N/A
Computer: XXX
Description:
SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=9028. Thread=5928. Client. Instance=. VD=GlobalData Protector_(DEFAULT)_msdb_14_00_21.
Log from DataProtector
Normal] From: XXX "(DEFAULT)" Time: XXX
SQL statement:
BACKUP DATABASE [msdb] TO
VIRTUAL_DEVICE = "Data Protector_(DEFAULT)_msdb_06_00_14"
WITH NAME = 'Data Protector: 2007/08/01 0064', DIFFERENTIAL, BLOCKSIZE = 4096, MAXTRANSFERSIZE = 65536;
[Warning] From: XXX "(DEFAULT)" Time: XXX
Error has occurred while executing a SQL statement.
Error message: '<Microsoft SQL-DMO (ODBC SQLState: 42000):bdb>
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a differential backup for database "msdb", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.'
Ive tried to do a full followed straight after by a diff but doent help.
Thank you for your help
Hi,
While backing up our database, I am getting the following message:
Could not insert a backup or restore history/detail record in msdb.dbo.sysbackuphistory or sysrestorehistory. This may indicate a problem with the MSDB database. DUMP/LOAD was still successful. (Message 3009)
Does anybody have any resolution for the same ?
Thanks,
Rajan
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
I'am doing functionality test on DTS packages and saving my DTS packages to meta data services instead of saving them as local packages. We would like to see what information would be provided by saving them this way, but when we try to open the meta data browser (the 3rd icon under DTS) we get the following error:
An error occurred while trying to access the database information. The msdb database could not be opened.
Whenever I start my SQL Express 2005 database, I get the following in the logs :
View 10 Replies View Related
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