Transact SQL :: Determine When Database Was Backed Up Without Using MSDB Tables?

Apr 22, 2015

I was wondering if there was another method to determine when a database was last backed up without using the backup history tables in msdb? whether using DBCC DBINFO, DBCC PAGE on a specific database page...etc.

Also, when restoring a database, is there a trace flag you can use to force restore details to be written to the error log?

View 4 Replies


Check If The Database Is Currently Being Backed Up

Sep 26, 2007

How can I check if the database is currently being backed up?

Seems like that should be a property, but it's not an attribute of the DATABASEPROPERTY function.


View 7 Replies View Related

Date Time Of A Backed Up Database

Apr 10, 2007

Pls tell me,can we store or determine the date/time of a database backed up or can we store date/time while taking back up of a database

View 2 Replies View Related

Loading Backed-up User Database In SQL Server 2k

Jul 20, 2005

Hi ,I have a file presumably backup of a User database from a SQLServer(2k) database. I want to load/restore that in a local SQL Serverto be able to see the table , views , etc present in the database.How do I do that ?Thanks,Arijit

View 4 Replies View Related

Does The Transaction Log On A Publisher SQL Server Get Truncated When The Database Is Backed Up, But The Distributor Is Down?

Jul 23, 2005

Hello All,The setting is SQL server 7, on Windows NT.If the distributor can not access the log on the publisher database ( butsubscribers are not deleted yet), will the full backup of the publisherdatabase truncate the transaction log? My guess is, it will not truncate thelog.Can any one confirm please?Thanks,Mokles

View 2 Replies View Related

Transact SQL :: How To Determine Index Create Date

Nov 7, 2012

Is it possible to determine an index create date in SQL Server 2008 R2?  The index in question is not a primary key.

View 8 Replies View Related

Transact SQL :: DATEADD - Determine Prior Month

Jul 8, 2015

My overall problem is to do some string manipulation and then DATEADD, but I still don't know why I getting what SQL is giving me.

To get the prior month, I use the following code;

RIGHT('00' + CAST(
CASE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
ELSE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
END AS varchar(2)), 2)
)--, -- Month
--CAST(CAST(LEFT(MAX(Eff_Period), 4) AS int) - 1 AS varchar(4)) -- Year

This correctly gives me the proper month number for the prior month, in this case, '06' since the current month is '07.'
But, for debugging, I wanted to test to make sure it would properly adjust for January - '01' - by replacing MAX(Eff_Period) with '201501.'

It did. Worked fine. Then, to make really sure, I put in '201502.'

Instead of the single record of '01,' it shot out 177,209 rows of all '02's!

I put back in the MAX(Eff_Period) instead of the date string constant and, worked fine. Swapped back in the date string - boom! - 177,209 rows again.

Like I said, I can use DATEADD - Now I'm really confused. I changed the date string to '201501' just to verify before posting this, and it spit out 177,209 rows of '01's! So, to keep from altering things and to maintain the edit history, I started up another query and C&P'd everything to it. Then, on the original, I backed up to an earlier version, one that worked. That listing is the same as above, but the commented out comma in line 9 and the commented CAST in the line after it - to get the year - were uncommented.

Now, it works with both '201501' and '201502.' But, if I replace the comments, basically removing the CAST with the year, it slams back with 177,209 rows!

View 6 Replies View Related

How Do We Determine Which User Database Tables Are Mostly Retrieved By User Or Modified By User?

May 22, 2008

Please give the T-SQL script for this ? Thanks


View 4 Replies View Related

Transact SQL :: Stored Procedure To Determine Which Databases To Restore?

Sep 27, 2015

I have 5 databases that the user will chose which ones to restore. I was thinking the variable with the 5 database names separated by commas. I was thinking about using the CONTAIN function but two of the databases have the same name except for a few letters at the end.

View 3 Replies View Related

Transact SQL :: How To Determine Remote Server Is Shutdown By Agent

Sep 28, 2015

I create a job name test connection by SQL AGent


- Have server1 and server2
- The job has 3 steps:
 + Step1: check server1 is running, next step2
               Server1 is shutdown or can not ping, next step3
 + Step2: do anything, for Example: run batch exe on server1
 + Step3: do anything, for Example: run batch exe on server2

Step1, i am using ping server1 command,

My problem is if server1 is shutdown, my command (ping server1) is also return true

and in the fact, job is run by follow Step1 -> Step2.

Expectation the job is run by follow step1 -> Step3

View 5 Replies View Related

Transact SQL :: Calculate Size Of All Tables In A Database

Nov 16, 2015

I need to build a query to calculate the size of all tables in a database ...

View 5 Replies View Related

Transact SQL :: Transfer Tables With Data From One Database To Another

Jul 4, 2015

Transfer tables with Data from one database to another one on a same server? 

View 10 Replies View Related

Transact SQL :: Query To Determine Overlapping Date Ranges (by Category)

May 12, 2015

Given the data below, I have a couple needs:

1) Query to determine if any date ranges overlap (regardless of category, e.g., row ids 6 & 7 below)

2) Query to determine if any date ranges of the same category overlap

declare @t1 table (id int primary key, category int, start_date datetime, end_date datetime)
insert @t1 select 1, 1, '1/1/2015 12:00:00 AM', '1/15/2015 12:59:59 PM'
insert @t1 select 2, 1, '1/16/2015 12:00:00 AM', '1/31/2015 12:59:59 PM'
insert @t1 select 3, 1, '2/1/2015 12:00:00 AM', '2/15/2015 12:59:59 PM'
insert @t1 select 4, 1, '2/16/2015 12:00:00 AM', '2/28/2015 12:59:59 PM'
insert @t1 select 5, 1, '3/1/2015 12:00:00 AM', '3/15/2015 12:59:59 PM'


View 7 Replies View Related

DTS Tables In MSDB

Apr 3, 2003


Can anyone tell me what tables are used to store DTS defs? When you create a DTS job, where does it get saved?


View 1 Replies View Related

Transact SQL :: Tables And Database Not Detecting Auto While Coding

Aug 27, 2015

When I type Select statemet like below, Tables names are not populate auto. I need to type full lenght instead of selection in list.

Ex: I have few tables and database in my schema and need to select while coding.

select * from (Table Name) When i type first char in the table space it will not populate list of tables in my sql studio. 

View 12 Replies View Related

Transact SQL :: Database Size Does Not Decrease After Dropping All The Tables

Oct 15, 2015

I have a database consisting of two main tables and 12 sub tables.

This was leading to increase in database size. So we thought of storing the sub tables data in the main tables in form of xml  in a column of varchar(2000) type.

So we created a new database that only had 2 tables and we stored the data of the sub tables in the new column of the main table.

Surprisingly we saw that the database size increased rather than decreasing .

View 9 Replies View Related

Transact SQL :: Move All Tables In Database From One To Another File  group

Oct 15, 2015

I have to move  all the tables in a database from one file group to another file  group.All my tables have millions of records and the indexes are in correct file group but not the tables. How much time will it take to complete the whole process ?

View 13 Replies View Related


Nov 30, 2000

Is there any way to get the DTS Wizard to see the system tables (sysjobs, sysjobschedules, etc..) in the MSDB or Master database?

View 2 Replies View Related

To Determine Tables Are Null.

Apr 30, 2008


There are many tables in a database. Most of them are null(unpopulated). Perhaps there are hundreds of tables.
Can you please provide me a simple way?


View 5 Replies View Related

Determine Tables That Are Simple Recovery

Oct 14, 2005

I need a sql statment to return a list of tables for a given databasewhere the Recovery Model option is Simple.TIARob

View 5 Replies View Related

Transact SQL :: Replicating / Synching Data Between Two Tables On Same Database With Live Transactions

Oct 7, 2015

Client is running X- version of application and corresponding database size is huge. Now client's vendor is releasing Y-version of same application with many database schema changes (like new tables added, new columns added, renamed existing columns and etc) To upgrade to the Y-version, vendor is suggesting to my client that down the system and do the upgrade for application/database to Y-version. We are sure that this process will take days together to upgrade to the Y-version. My client is not ready to down the system for that long. So we are trying to find the solution with minimal down time.The approach we are thinking is, 

1) Create the replicated database to another server (server2) from production server(server1) using golden gate with X-version

2) Create new tables/schema updated tables from Y-version database on same server1. Here for  Updated schema tables we are planning to use the name <table_name_Y_version> as the same table name exists in X-version.

3)With above 2 steps, golden gate replicate the changes from production to server1 and server1 will have the new Y-version table schema (with different concatenate name ' _Y_version'). BTW , there is no affect for the production

4) At this stage we are planning to find best approach, to fill the '<table_name>_Y_version' from X-version tables. two challenges here a) all data needs to be moved to Y-version tables b) they have to sync data in real time.

we thought of going to

a) ssis package to pump the data to Y-version tables, but real time data will not sync.

b) trigger based technique, previous experience said, lot of load

c) thinking about sql replication.

View 5 Replies View Related

T-SQL (SS2K8) :: Query To Predict Future Job Schedules From MSDB Tables

May 8, 2014

I want to query my msdb job and jobschedule related tables to generate a list of runtimes for each of these jobs for the next day or any future date. This query should output JobID, Run_Date(YYYYMMDD), and Run_Time(HHMMSS).

If I have 3 jobs with...

Job#1 scheduled to run once every 4 hours between 6 AM and 10 PM
Job# 2 scheduled to run every 15 minutes between 11 AM and 1 PM
Job# 3 scheduled to run every minute between 4 PM and 4:15 PM

my output should look as below ....

View 1 Replies View Related

An Error Occurred While Trying To Access The Database Information. The Msdb Database Could Not Be Opened.

Jun 21, 2007

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.

View 3 Replies View Related

Service Broker Is Disabled In MSDB Or MSDB Failed To Start

Nov 21, 2005

Whenever I start my SQL Express 2005 database, I get the following in the logs :

View 10 Replies View Related

Help MSDB Database

May 2, 2001

I have a new server setup running sql2000 as default instance. I wanted
to move all the jobs and dts packages from another server which is running
multiple instances of sql server. I backed up and restored the msdb database
to the new server and now I can't edit any jobs. I get this error?
can't edit or delete the job that originated from MSX server.
How do I fix this problem. Any help will be appreciated.

View 1 Replies View Related

How To Determine Last LSN For Database

Feb 13, 2008

What is the most reliable way to determine the last LSN of a database? I've looked in sys.database_files to no avail. I've also looked in msdb.dbo.backupset which is accurate but only based on backups already performed not the current state of the database.


View 5 Replies View Related

Recovering MSDB Database

May 31, 2000

1) How do I reinstall a fresh copy of the MSDB database (since my current
one is corrupt). Is there any script available?

2) What is the purpose of the MSDB database? Backups and jobs/ Wizards?

Thanks for your help.

View 2 Replies View Related

Msdb Database Problem

Mar 14, 2000

Hi Everyone,
Couple of days back my server was down because of hardware problem and it was rectefied and sql server 6.5 started yesterday afternoon and the backup schedules was run in demand yesterday but it failed, and some of the schedules which are suppose to take backup in the night also failed today, it was also not showing anything in the schedule history, and when I run the schedule on demand today again it failed, so I created a newdevice and scheduled a newtask and I run in demand and the history shows that:-
schedule history (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)).
I checked the sysbackuphistory it shows three days back history, can any body give me the solution, what are the things to be checked, please give me the solution.
waiting for the immediate reply.


View 1 Replies View Related

Update MSDB Database

Jul 28, 2005

I have to rename some Originating_Server fields in the msdb database. I'm pretty sure I have to run a script that makes the msdb database editable but I can't find it anywhere. Am I wrong?

View 3 Replies View Related

Not Able To Connect MSDB Database

Jun 19, 2008

Friends -

I am not able to connect to MSDB database under SQL Server 2005 Integration services. i am getting the following error.

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click:


The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSrvr)


Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)



Please advise on this.

cheers :)

View 4 Replies View Related

Issue With Msdb Database

Aug 17, 2006

Hi all

when i try to restore a database called ddaa, i am getting the following message after the restore is completed.. and the database icon in the enterprize manager is having ddaa(loading).now i am not able to access that database..

the message i am getting is..

database msdb cannot be opened due to anaccessible files or insufficient memory or disk space. could not insert a backup/restore history detail record in the msdb database.this may indicate a problem with the msdb database.

how to resolve this issue..

Thanks in advance.

View 3 Replies View Related

Msdb Database In Suspect

Dec 14, 2007

Dear Experts,
in one of our servers, the MSDB database is in suspect mode, and i've used the command sp_resetstatus
and i restarted the service also but still it is in suspect mode.

how can i change that to normal state?
it is asking to check the errorlog.

Even you learn 1%, Learn it with 100% confidence.

View 6 Replies View Related

Suspect Msdb Database

Jul 23, 2005

My msdb database is marked as suspect. This means that my maintenance plandoesn't work. I'm a novice when it comes to sql server so now I'm wonderingif anyone could give a hint on how to fix the suspect msdb database? Doesthis database contain any vital information? What could be the reason thatthis database become suspect?Appreciate any suggestions in this matter.Thanks!--mvhHallgeir

View 2 Replies View Related

Copyrights 2005-15, All rights reserved