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?
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
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
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;
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!
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.
- 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 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 ?
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
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
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.
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.
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.
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.
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 . (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 . (Microsoft SQL Native Client)
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.
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