T-SQL (SS2K8) :: Getting Directory Information For Data / Log / TempDB / Backup
Nov 18, 2014Is there an Admin script that yields the following directories?
Data
Data Root
Log
TempDB
Shared Feature
Backup
ADVERTISEMENT
How To Change The Default Backup Data Directory In SQL Server 2000?
Dec 29, 2006Hi,
I had SQL Server 2000 installed like below:
Installation Directory = C:Program FilesMicrosoft SQL Server
Default data directory = D:
Default log directory = D:
I found out that the data is stored in D:Program FilesMicrosoft SQL ServerMSSQLdata and backup is done in D:Program FilesMicrosoft SQL ServerMSSQLackup
However, Microsoft says we should never put the ..ackup and ..data folders in the same partition.
My question is: How can I move the path of the backup directory?
I checked the registy but this is what I found:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServer
..BackupDirectory=C:Program FilesMicrosoft SQL ServerMSSQLackup (<-- note it is pointing to C: not D:)
..DefaultData=D:
Can somebody help please?
Integration Services :: How To Populate Whole Active Directory Information In A Table
Jul 24, 2015We have table where we want to populate the fields from Active Directory using Script Task in ssis 2008 .
CREATE TABLE [ZPTSMGR].[ActiveDirectoryRaw](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LoginName] [nvarchar](50) NULL,
[LastName] [nvarchar](100) NULL,
[MiddleName] [nvarchar](50) NULL,
[FirstName] [nvarchar](100) NULL,
[code]...
SQL 2012 :: Where Does Server Store Information About TempDB Configurations
May 29, 2015 Whenever SQL Server get restarted, tempdb gets recreated with its last configuration.
let me know where SQL Server store tempdb configurations? How does it know how many Tempdb files it needs to create on restart?
Access Control List (structure) Invalid - Updating Virtual Directory Information
Sep 26, 2005Hi,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 RelatedT-SQL (SS2K8) :: Checking User Active Directory Group
Jul 17, 2014Is it possible to check for Active Directory group.. ie see if the user running the Stored Proc, is in a specific Active Directory Group? Or if I set up Login's using Active Directory, can I get the Login that way... or will it give me the user's account?
View 6 Replies View RelatedT-SQL (SS2K8) :: Inserting FK Information Into Temp Table
Jan 7, 2015I need to insert FK information into a temp table using database name and table name as a parameter.
I've been trying different ways, even with global temp table, but still doesn't work. Below is the code showing what I am trying to achieve. I want to avoid global temp table if possible.
IF OBJECT_ID('tempdb..##FKs') IS NOT NULL DROP TABLE ##FKs
CREATE TABLE ##FKs (
[ForeginKeyName] [nvarchar](128) NULL,
[TableSchema] [sysname] NULL,
[TableName] [nvarchar](128) NULL,
[RelatedTableSchema] [sysname] NULL,
[Code] .....
T-SQL (SS2K8) :: Table Valued Function For Active Directory Group Membership
Jun 8, 2012For code reuse, I am trying to get a table valued function to return users of a given AD group name. I can easily get this with hard-coding the group name. But because OpenQuery wont accept parameters, I can't insert my group name there. And because functions can't call dynamic SQL, I can't do it via dynamic sql. I have seen people do it with CLR, but I rather not go that route. I can use a stored procedure + cursor and iterate through each group and store the results into real tables and create a cache, but I rather query Active Directory itself to save space, but I rather do the caching then the CLR. Any approach I am missing on how to do this?
The following works fine:
SELECT DISTINCT sAMAccountName
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName, sn
FROM ''LDAP://OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''
WHERE objectCategory=''Person'' AND objectClass=''USER'' AND memberOf=''CN=SomeGroupName,OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''') a
WHERE sn IS NOT NULL
The following gives me the error:
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
CREATE FUNCTION [dbo].queryADGroupMembers
(
@group nvarchar(255)
)
RETURNS @rtnTable TABLE
[Code] .....
T-SQL (SS2K8) :: Query With Several Joins Fills Up TempDB And Won't Finish
Aug 14, 2014I have this query I need for a report. Originally it was 4 queries to be used in Crystal Reports. Now I want to create the same report with SSRS and therefore I incorporated all queries in one in order not to use subreports [URL].....
Tempdb fills up to nearly 90 GB. I am running SQL Server on a local box, so I am sure there is no other traffic. Here is the query:
SELECT AdHaupt.NSprache_ID
,AdHaupt.mengentext AS mengentextHaupt
,AdHaupt.Einzelpreis
,AdHaupt.Anzeigebezeichnung
,AdHaupt.Gesamtpreis
[Code] ...
I ran it with TOP 10 as well, just to see if it will finish at all, but it never did (ran for an hour now).
I Cant Set Directory For Backup Database.
Nov 23, 2004after selecting the presetted directory .. i clicked ok and they said this," The file name specified is recongnized as a directory name. The file won't be able to be rewritten. Would you like to continue? "
then i clicked " Yes " .. - nothing happened ..
then i clicked " Yes " .. - nothing happened again ..
now i click ok again to close the "restore database" icon .. and they give me this.. i attached a screenshot .. pls have a look ..
i tried clicking "Manage Attachments" somewhere at the bottom of the page to upload .. but ragezone gave me a page telling me that they are sorry for any inconvenience caused .. so i cant attach it .. here is a link .. its kinda small .. but it says:
"Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQLBACKUP'. Device error or device off-line. See SQL Server error log files for more details. RESTORE DATABASE is terminating abnormally."
thank you for helping me .. thanks in advance ..
http://img.photobucket.com/albums/v81/xiaoboy/error.bmp
http://img.photobucket.com/albums/v...oy/untitled.bmp
PLEAS PLEASE HELP ..
Find Backup Directory
Jul 20, 2005Hello!MSQL2000:I want to make db-backup (maintenance) but haven't enough space on thediscs that are physically in the machine. I have enough on the network, buti can't choose an network-drive, wheni want to specify the backup directory.What to do?/Bjørn
View 3 Replies View RelatedChange Backup Default Directory
Aug 30, 2002WE HAVE RECENTLY MOVED THE MSSQLBACKUP FOLDER TO EMC DRIVES E:, BUT SQL IS STILL DEFAULTING BACKUPS TO THE ORIGINAL DEFAULT LOCATION WHICH WAS ON THE D: DRIVE. DOES ANYONE KNOW HOW TO CHANGE THIS? I KNOW THE ORIGINIAL LOCATION IS STORED IN THE REGISTRY, BUT I DON'T KNOW IF I CAN CHANGE THE REGISTRY OR IF THERE IS A DIFFERENT PLACE. CAN SOMEONE PLEASE HELP.
THANKS
How To Get The Drive Letter And The Backup Directory
Jan 16, 2008Hello,
I'm looking to get the drive letter and path for my backups programmatically. I am monitoring my sqlserver boxes remotely and need to get this information to determine if my backups will fit on the backup drive.
I'm looking for either a sql statement or a stored procedure to accomplish this.
For example...the backup script states:
EXECUTE master.dbo.xp_create_subdir N'H:MSSQLBackupmaster'
I need to know how this script gets H:MSSQLBackup
Thanks in advance.
Database Backup Directory Is Invalid
Apr 27, 2004Hi,
I'm using Database Maintenance Plans in Enterprise Manager and I'm trying to backup a database to a different server using a UNC (i.e. \servernameackupdirectory).
I have done this successfully with two SQL Servers but every time I try to set the third SQL Server to backup to a UNC I get this message:
The complete database backup directory '\servernameackupdirectory' is invalid.
The only differences that I can think of is that the server is Win2000 Advanced Server and the SQL Server is Enterprise Edn, where as the two that work are Standard Edn and are on Win2000 Servers (not Advanced).
Does anyone know why I am unable to do this on one SQL Server when I have had no trouble on the other two SQL Servers?
Any help would be greatly appreciated
Thanks,
Troy
Restrict Backup To One Directory Pr. User/database
Sep 30, 2004Hello
I have an MSSQL server where I would like to create a couple of databases, create one user pr. DB, and give them db_owner permissions.
Giving a user db_owner permissions allows the user to make a backup of the database, which is a good thing and I would like it to stay that way.
My problem is that I don't know how to restrict the user from saving the backup to any directory or filename that he wants to.
He can not get out of the directories that the user MSSQL is running under has access to. But he can overwrite another users backupfile or placing the file in the MSSQL-programfolder.
The users is using SQL Server authentication.
Can I restrict the backup procedure to save to only one file or in one directory?
What is the best practice regarding backup if you give your users db_owner permissions?
HOW TO FIND BACKUP FILE IN DEFAULT DIRECTORY
Dec 17, 2007
I am getting a backup file in default directory by using this script
Code Block
BACKUP DATABASE CPHONE
TO DISK = 'CPHONE.Bak' -- send to default pathaway
WITH FORMAT,
NAME = 'Full Backup of CPHONE'
Is there any way to find where is the default path of the SQL server, in order to show to the user from a VB program in which pathway the database back up is stored
6.5 - Deleting Backup Information
Mar 12, 1999Does anyone know a way of deleting old backup information? We'll shortly be in a position where SQL thinks we have got a certain number of backups whereas in fact the older ones will have been copied to tape & deleted. Apart from being misleading this must also be leaving redundant data in the system/control tables & I'd prefer not to have to look at increasing their size just because of this. Anyone got any tips? Thanks very much to the guys who have responded to my previous 2 questions, being new to SQL I certainly appreciate the help.
View 2 Replies View RelatedTEMPDB Shows Excessive Size After A Backup.
Apr 18, 2000I have got SQLv6.5 SP5a with SMS1.2 SP4 on seperate Alpha boxes. I have automated the backups so they are scheduled for after hours. SMS gets backed up first and TEMPDB shortly afterwards. However, since a back log in SMS MIFS has happened, the TEMPDB backup displays of 100,000pages backed up. When you back it up on its own, it only shows 170+ pages.
The SMS DB is 600MB in size, the Log is 210MB, Open objects is 5000, and TEMPDB is set 210MB on its own device.
Any ideas
SQL 2012 :: Script To Backup And Shrink TempDB
Oct 2, 2015I need a script to Backup & Shrink tempdb.
namesize
tempdev1024
templog64
tempdev21024
tempdev31024
tempdev41024
tempdev51024
tempdev61024
tempdev71024
tempdev81024
I can't believe how many tempdb's there are?
Backup And Restore Operations Are Not Allowed On Database Tempdb
Jan 29, 2008Hi all
SQL 2005 SP2. Upgraded from 2000 and migrated maintenance plan. I am now getting an error when running the maintenance plan for all System databases:
failed with the following error: "Backup and restore operations are not allowed on database tempdb.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Someone else has the same problem and reported it here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=303848&wa=wsignin1.0
Any thoughts on this?
Tony
MVP - Directory Services
SQL 2012 :: Can Hacker Obtain Database Information From Unencrypted Differential Backup?
Sep 24, 2015We are getting a security audit for the company I work for and got this question recently, and while my answer would be "everything is possible", I know that DIFFs alone can't restore a SQL database.
Having said that (and don't want to read hypothetical comments) how can a hacker read confidential information from an unencrypted DIFF backup? Let's say he steals the DIFF backup alone.
Extract Outlook Contacts Data(on Public Directory) Directly In Data Flow
Jun 13, 2006Hi everyone,
I have to extract, dayly a list of contacts on a exchange server in a table on our EDW on sql server 2005. Is it possible to get the information directly from a dataflow or i will have to developpe a script task ?
Need help desperatly !!!
Using Provider For Microsoft Directory Services For Querying Active Directory
Apr 6, 2007Has anyone used this successfully from an OLEDB source component, or even from the Execute SQL Task? I've seen some examples of using a script component, but nothing that uses it through a connection manager.
View 6 Replies View RelatedT-SQL (SS2K8) :: Delete Table Records With Backup?
May 2, 2014using below query
DELETE FROM Table_name
WHERE Date_column < GETDATE() - 30
am able to delete old records morethan 30 days, but i want to results to be saved in file.
before deleting i want to a craete a file and save the to be deleted records.
T-SQL (SS2K8) :: Backup Database Stored Procedure
May 16, 2014I am trying to modify a script that to back databsae in t-sql.I put it in a stored procedure with one paramenter dbname.I would like if a dbname is passed when calling the sproc, it will only backup this database, if no paramenter is entered, the dbname is null, then backup all user databases- no system databases.But I have difficulty to define this two situations in code.
Below is the sproc:
CREATE PROCEDURE [dbo].[BackupDB]
@dbname varchar(50)
AS
BEGIN
[code]...
T-SQL (SS2K8) :: Rebuild Index After Transaction Log Backup
Aug 27, 2015I try to create script which Rebuild one table index after transaction log backup.
In my script first i check or backup is complete in 10 minutes if i find record i do it index rebuild if i don't find i need go back and check again.
The problem is that query finish if don't find a backup record, How i can loop that script that he checking until find the record and then start rebuild index.
Use DB
GO
DECLARE @database_name NVARCHAR(50)
SELECT @database_name='DB'`
START:
WHILE EXISTS
(SELECT msdb.dbo.backupset.database_name,
[Code] .....
Get The Data Directory
Sep 19, 2006Hi,
I am searching for how getting the data directory where default mdf files are based.
(C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData)
My goal is to deploy mdf file in this folder during installation.
Thanks
T-SQL (SS2K8) :: Unable To Restore Backup File To Different Database
May 28, 2014I created a dummy database (TomsTest) and then I try to restore my other database "DEV" to it.Normally, you have to use the Move for both the mdf and ldf files to tell it to restore to another database.But the message I am getting is: The file 'D:SQLDataTomsTest.mdf' cannot be overwritten. It is being used by database 'TomsTest'.
USE [master]
RESTORE DATABASE [DEV] FROM DISK = N'D:SQLBackupDEV_backup_2014_05_28.bak' WITH FILE = 1,
MOVE N'Dev' TO N'D:SQLDataTomsTest.mdf',
MOVE N'DEV_log' TO N'D:SQLDataTomsTest_log.ldf', NOUNLOAD, REPLACE, STATS = 5
Of course it is in use, that's always the case. Even if I put it in single use mode, it still doesn't work.I tried it on another machine and had the same problem. Not sure why. If I call it the same name, it works fine but I don't want to overwrite the original database, I just need to get some data from the backup.
T-SQL (SS2K8) :: Automatic Restore Of Backup Files With Different Filename
May 26, 2015I 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 RelatedHow To Set Default Data && Log Directory
Mar 10, 2005I tried to add volume to default data & log directory of "new data default location" of database setting at SQL server properties, but it never be saved, when I get in properties again, the setting is disappear.
Does somebody could advise me how to save it?
Thank you
Ting
T-SQL (SS2K8) :: Get Current DB Backup Setup List In Pivot Style?
Dec 1, 2014I need to list the current DB Backup Set up list in PIVOT STYLE.
I need following way:
Database_NameFULL - DDIFF - ILOG - L
DB1DL
DB2D
modelDL
DB3DIL
msdbD
Get Data From Active Directory To MS SQL Server
Jun 5, 2007I look for and try to get data from Active Directory to MSSQL Server, but have same error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT name
FROM 'LDAP://office.experter.group'
WHERE objectCategory = 'Person' AND objectClass = 'user'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
for the code:
select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://office.experter.group''
WHERE objectCategory = ''Person'' AND objectClass = ''user'''
)
I create linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',
@srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject',
@datasrc=N'office.experter.group'
I've changed security setting, format of LDAP. Please advise me
SQL Server Default Data Directory
Jun 2, 2006I would like to know how to change the default data directory in SQL Server Express. I have a partitioned hard disk with SQL Installed on the C: drive. I would like to move the data to d: drive. I have just installed SQL and do not have any databases. I have installed SQL with advanced services.
Thanks in advance for your response.