Query To List Out Directory Path From Which Database Restored Recently
Dec 3, 2013I need a SQL query which lists out directory path from which the database has been restored recently ( i.e backup path ).....
View 2 RepliesI need a SQL query which lists out directory path from which the database has been restored recently ( i.e backup path ).....
View 2 RepliesIn a multi-user environment, I would like to get a list of Idsgenerated, similar to:declare @LastId intselect @LastId = Max(Id) From TableManiaINSERT INTO TableMania (ColumnA, ColumnB)SELECT ColumnA, ColumnB From OtherTable Where ColumnC > 15--get entries just addedSELECT * FROM TableMania WHERE Id > @LastIdThe above works fine, except I'm assuming it will not work in amulti-user environment. Is there any way to get the set of Ids thatwere just added in the previous statement (similar to @@IDENTITY)without doing all of this in a serializable transaction or making atemp table of every single Id before the insert statement?
View 41 Replies View Relatedhow do I list contents of file directory in the SQL Query Analyzer
View 1 Replies View Related
Hello,
How do I clear the Recently Used Server list in SQL Server Management Studio? I am talking about the "Server Name" field that is part of the "Connect To Server" drop down when you initially open SQL Server Management Studio.
Thanks.
Hello all, i have a little problem...
...Is there any method for getting the path of the SYSTEM32 directory from a stored procedure?
I need it for register an assembly that is in this directory or the server.
Thanks.
Alejandro F.
I'm trying to use a WMI Event Watcher Task as a directory watcher. I know it is available from Konesans at http://www.sqlis.com/23.aspx but they don't feel obliged to make this open source yet. I definitely appreciate the contribution of the binary to the community.
I'm very interested in understanding how the event handler gets the path of the file that caused the event. I added a Script Task to the Event Handler tab and it fires as expected when I create a new file in the directory. I can retrieve the value of the SourceName which is just the name of my Event Watcher Task. I can't find documentation on an object model that is exposed through SSIS that would reveal the name or path of the file, although it's clear this information is available in direct WMI queries from code or a WSH script.
My only guess is that you need to do a workaround in Script (or a custom module) by querying the directory for the most recent file (or list of files compared to a list of processed files) rather than a synchronous feed of the path directly into the SSIS event handler. Ideally, you'd like to get one event per new file created, although this might not be deterministic with the way the OS fires these events and SSIS responds to them. So at the end of the day, querying the directory for recently created files may be the only reliable way of getting the (list of) new files.
I know that this can take hours and hours to figure out since you really need to understand how WMI interacts with SSIS but I would be indebted if you can shed some light on this for me and the community.
Thanks,
Norm
I have a sql compact table that stores a series of directory paths like so:
C:F1BackupadministratorCDatakit.zip__--1-07272007124351.F1O
C:F1BackupadministratorCDataDeploySMD_FSWsetup.exe__--1-11292007122443.F1O
C:F1BackupadministratorCDataDeploySMD_FSWSMD_FSW.application__--1-11292007122443.F1O
Is there a way in sql that I can get just the directory name so the results would look like this:
C:F1BackupadministratorCData
C:F1BackupadministratorCDataDeploy
C:F1BackupadministratorCDataDeploy
Also, is there a way to just get the file names so the results would look like this:
kit.zip__--1-07272007124351.F1O
setup.exe__--1-11292007122443.F1O
SMD_FSW.application__--1-11292007122443.F1O
I've seen some examples with Sql server but they use the reverse function which is not available in sql compact.
Any ideas would be greatly appreciated.
I am trying to create hierarchyID for a directory file path.
View 1 Replies View RelatedHi,
I am adding a file to a filegroup on a remote system, and i don't know the direcotry structure of that machine, how can i provide the path for FILENAME param. :eek:
ALTER DATABASE TESTDB ADD FILE (NAME = N'TESTFILE', FILENAME = N'physicalfilepath.ndf') TO FILEGROUP TESTFILEGROUP
Warm Regards,
Mushq
Overall goal: Write a Bulk Insert statement using the UNC path of a filetable directory.
Issue: When using the UNC path of the filetable directory in a Bulk Insert Statement, receiving "Operating system error code 50(The request is not supported.)" Looking for confirmation as to whether this is truly not supported.
Environment: SQL Server 2012 Standard. Windows Server 2008 R2 Standard
There was a 2008 SSRS service running on server "abc".
I have a .Net UI that uses the wsdl at [URL] ....
They deployed SSRS 2012 service on a new server "xyz".
DNS points abc to xyz. Nslookup looks right shows both xyz and abc alias to the same IP.
when I try to go to [URL] .... it redirects to xyz as expected for the host name but the HTTP GET /ReportServer is appended with GET /Reports/ReportServer which doesn't exist and I get an 404 error.
I have no admin rights to see anything and I have no idea if it was an inplace upgrade or a new server. I assume it's new since the actual server name changed in nslookup. Why would it append the default virtual directory when it makes the hop to server "xyz"?
We have a hierarchical table of some 2-3k of rows that grows slowly at only 3-5 rows a month, and is never likely to be above 5k, holding file/directory names with an IdParent int value pointing at that items immediate parent or NULL if root
Below is a sample:
Id Name IdParent
1 C: NULL
2 D: NULL
3 ProgramFiles 1
4 DataFiles 2
5 Excel.EXE 3
6 MyDataList.CSV 4
iterative code required to achieve the result I am looking for is best handled in the Application layer, but in this case the design brief is that we must get our results back as a View. Speed of execution and code clarity are secondary 'icing on the cake' issues.
I have tried recursive CTE's and XML FOR solutions but without success, and while I feel the latter is probably the best all rounder the searches I have made and the examples I have read do not seem to solve this particular issue.
Even to find the starting point of the search tree (i.e. the .EXE and the .CSV items) was difficult because the final slash is optional in some of the rows (.e. row 4)
The end result of my view on the above data should return:
Id FilePath
5 C:ProgramFilesExcel.EXE
6 D:DataFilesMyDataList.CSV (note the adding of the missing delimiter between DataFiles and MyDataList)
We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message
System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
Please note we left the DataBaseA in the old SQL2000 server.
Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup
Many Thanks
Is there an XP/SP out there that will return a list of files residingin a specified directory?I'm looking for something simlar toExecute master..xp_subdirs N'C:'But instead of it returning a list of subdirs I want it to return alist of files in that directory.Jeff
View 5 Replies View RelatedI neet save user login form active direcotry to databases. How I can make that?
View 1 Replies View RelatedI have a scenario where I need to move a series of files from within a directory of many files. The files follow no nameing convention and are more of less random. However the file names never change from week to week. I tried various different options in a 'file system task', no go.
Any ideas on how to move only a list of files?
or
can I load only specific files into a 'Foreach Loop container' from a certain directory. I tried delimiting file names in the file source, that did not work.
Either way can work,
Thanks
I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Thanks
Prashanth
In my SQL Server, I see the below message in the Application Event Viewer
"18265 :
Log backed up: Database: HSD, creation date(time): 2007/01/06(05:05:05), first LSN: 1439:495055:1, last LSN: 1439:496675:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:MSSQLBACKUPHSDHSD_tlog_200707141300.TRN'})."
When I save the application event viewer and open it in another server, I do not see the above message, instead I get the following message:
" Can't find or load message DLL. Message DLL must be in path or in current directory."
Any thoughts to overcome this problem is appreciated.
Thanks
Santhosh
Is there anyway,can we find the list of servers by querying at active directory?
View 3 Replies View Relatedhi all,
our datbase was restore but the publications were all gone.
however replication monitor shows publication and subscription.
worst. can create the publication with the same name because
it raises an error saying that it can't drop publication becuase
there is a subscription in it.
how can i remove the publication when the are already gone in the publication
folder but exist in the replication monitor
thanks for the help
Hi,I've just installed SQL server and then IIS and SQLCE tools.I created a virtual directory and was trying to update the NTFSpermissions from SQLServer Connectivity management when i got thefollowing error - Access Control List (structure) invalid.Has anyone come across this and if so, what did u do to fix it.ThanksLyn
View 2 Replies View RelatedCan some one please tell me whats a best way ot get a list of all the tables that are present in a particular database.
Thanks
I'm running SQL Server 2012 Express. I need to query a database and return the list of tables in the database. How to construct this query?
View 4 Replies View RelatedIs there a way to restore a 2000 database to 2005?
will the restore process convert automatically to 2005?
Hello!
I am trying to find out name of the backup file database was restored from (I am restoring from DISK), something similar to physical_device_name in backupmediafamily. restorehistory doesn't have this information.
Any advice is greatly appreciated.
Thanks,
Igor
Hi,
I have recently restored a backup of a SQL Server 2000 Database (from my production server €“ which is a shared hosting service) on my dev machine so I can do some testing. The issue I am having is with the permission of objects. Some of the objects were created with the dbo user and some with another login (myLogin). The ones that were created with dbo work fine on my dev machine. The others do not work unless I prefix them with €œmyLogin€?€¦ I get an €œInvalid object name€? if I don€™t prefix them. This is problematic because there are hundreds of stored procs and tables that I would need to modify in order to use them on my dev machine.
Can anyone tell me how to create a new user with the necessary permissions so I can execute my procs and access tables without having to prefix them?
Hope this makes sense €“ please let me know if it doesn€™t.
Thanks,
Mike
Hi
I have an sql server 2005+sp2 instance and a db on it.
I have create a database master key (specifying a password so it WILL NOT get encrypted by the server master key) on my database and backed it up
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test'
go
BACKUP MASTER KEY TO FILE = 'c:Genesis_DBMasterKey.key' ENCRYPTION BY PASSWORD = 'test'
Then I have created a asymmetic key without specifying a password therefore the key will get encrypted bt the database master key.
CREATE ASYMMETRIC KEY card WITH ALGORITHM = RSA_512
And then for test I run
DECLARE @Encryptvalasym varbinary(MAX)
SET @Encryptvalasym = EncryptByAsymKey(AsymKey_ID('card'), 'EncryptedData')
SELECT @Encryptvalasym
SELECT CONVERT(varchar(max),DecryptByAsymKey(AsymKey_ID('card'),@Encryptvalasym) )
And the result is OK.
Problem: as a recovey plan if I restore the database on the same or other instance (althogh the asymmetic key is part of backup) the decrypt doesn€™t work.
I have tried to create a bank database restore tha databse master key and recreate the asymmetric key, but I€™m afraid I€™m still not having any luck with it, as it does not decrypt.
My main issue is that is any case if I have to recover the database , how do I have to make sure that encryption/Dycription will work?
Thanks
I only have the ldf file and the bak file.
My bak file was current to 3/2/2007
My ldf file was current to 3/25/2007
I do not have the mdf file.
How to I apply the transaction from the ldf file to my resotred database?
Dear All,
I have a user with DBOWNER,DBCREATOR role and access is set to deny on viewing any other databases. Now when I connect using this user and try to restore the database. after restore, I can not open it or access it using the same user.
what is causing this.
Thanks,
Is there any workaround on this? I need to migrate my data in sql server 2005 into sql server 2000. Thanks in advance.
View 2 Replies View RelatedHi I took a Full Backup on Production Server and Restored the Same Database on Testing Server, i am able to see all the Tables Stored Procedure and View except Logins, i am not able to see any logins, there are total 650 Logins under the Database how can i see the logins and how to restore the logins, while i am trying to create any logins it is saying Error Occured login is already existing in the Database..
Bye,
Madhavi
Hi All,I have to a few backups restored from a set of backup files. Thebackup files have been removed from the drive because of some storageconstrains...For some data comparison reasons, I need to figure out the actual dateof these backup files that is used to restore these databases. Isthere any system table I can query to figure the actual backup timesof these databases...Any help will be much appreciated.. Thanks in advance.- Aravin
View 1 Replies View RelatedThanks in advance for help.
Could someone please help me for SWL backup restoration and db by user
I restored SQL 7.0 database to SQL 2000. ( by creating empty db on SQL 2000 and restored from SQL 7 backup) -- restore ok..
I need to use same SQL user which is admin for DB on SQL 7, for SQL 2000 also. ( DB user is sql user not domain user)
I put mixed authentication mode ( windows and SQL) in SQL 2000 enterprises manager security tab setting.
I can see DB user is available in DB user list on restored DB but can not access DB when I try to access from query analyser
I tried to create new login with same name as it was in SQL 7 and tried to give full admin access on SQL 2000 enterprise manager but I get error 21002:[ SQL DMO] User 'user' already exists.
Kind Regards
Sunod