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?
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.
Thanks
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 RelatedHi ,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 RelatedHello 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 RelatedIs 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 RelatedMy 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;
SELECT
(
SELECT
RIGHT('00' + CAST(
CASE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
WHEN 0 THEN 12
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
FROM
Current_Membership;
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!
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
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 RelatedI create a job name test connection by SQL AGent
Background:
- 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
I need to build a query to calculate the size of all tables in a database ...
View 5 Replies View RelatedTransfer tables with Data from one database to another one on a same server?
View 10 Replies View RelatedGiven 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'
[code]....
Hello,
Can anyone tell me what tables are used to store DTS defs? When you create a DTS job, where does it get saved?
Thanks!
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.
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 .
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 RelatedIs 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 RelatedHi,
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?
Thanks
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 RelatedClient 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.
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 ....
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 RelatedI 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.
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.
-joe
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.
Ziggy
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.
Thanks,
Madhu
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 RelatedFriends -
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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
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)
------------------------------
BUTTONS:
OK
Please advise on this.
cheers :)
Satish
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.
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.
Vinod
Even you learn 1%, Learn it with 100% confidence.
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