I'm requesting of our dba that he create a database with recovery simple for my peer and I to start using. I'm asking him to give us db owner on this db so we can create schemas, tables, views, procs, do table inserts, deletes etc etc. what sql permission (if any) would allow my peer and I to do a backup once in a while to the default sql directory for backups? And for that matter a restore from there.
View 7 RepliesThe space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
View 3 Replies View Relatedwhere in sql server 2005 ( and 2000 for that matter) do i find the path to the location where backups are placed (.bak files)? is there sql i can use to find this out
View 6 Replies View RelatedMy environment looks like:
- Windows 2008R2 SP2 VM (VMware 6)
- SQL 2012 SP2 Std.
- NetApp iSCSI LUNs, Snapmanager for SQL
So I have created a test database and configured the logs to grow to max. 120MB in size.
To check the allocated and free space I right-clicked the database -> tasks -> shrink file
I see currently allocated: 5,00MB and 2,50MB free space for the MDF Database
I see currently allocated: 1,00MB and 0,63MB free space for the LDF Logfile
Next I use a data generator to fill the database with random data.
After adding around 500k rows I check the size again:
I see currently allocated: 17,00MB and 0,44MB free space for the MDF Database
I see currently allocated: 61,94MB and 0,27MB free space for the LDF Logfile
Next I take a full backup incl. truncating log files. After that I check the size again:
I see currently allocated: 17,00MB and 0,44MB free space for the MDF Database
I see currently allocated: 61,94MB and 57,24MB free space for the LDF Logfile
So now my question is where are those 56,97MB? I imagined they should now be added to the MDF file but they seem to be just gone. I did this procedure 2x more time and the MDF stays the same size while the LDF is almost empty after backups. Then I thought maybe its in the memory of the server so I rebooted it. But still the MDF has the same size... Is this normal? How it should work?
I 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
how to restore database backups with different recovery fork. I have 1-full backup 2-diff backups and 10-tran backups. My prod database in mirror, so after error, switched to mirror with "allow_data_loss" option. And now I have full and diff backup with one recovery fork GUID and other backups with another GUID.So the question is, how to restore all this backups if in middle of restoration will be different recovery fork.Tryed to restore log backups with new fork guid and got error:This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time.
View 2 Replies View RelatedWE 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
I 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.
I went ahead and installed RS even though it was clear Microsoft install was confused (re: preivous post). Now I get to experience the thrill of configuration hell.
Report Server Virtual Directory had a green check. Unfortunately the Name block says <Not Set>. When I try to create a New... default directory I get the error message: "The virtual directory could not be created. The previously set virtual directory will still be used."
Does anyone know where the <Not Set> virtual directory is stored on the computer?
addl info:
ReportServicesConfigUI.WMIProvider.WMIProviderException: The virtual directory specified already exists. Specify a different name.
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateVirtualDirectory(String virtualDirectory, String path)
(Vista Ultimate)
I am trying to run a query on my sql server and get the following error message:
"An error occurred while executing batch. Error message is: The directory name is invalid."
how do I fix?
Hi, I've installed SQL 2005 and failed to change the default datalocation during installation. Now I want to change it. Can I do thiswithout having to uninstall and reinstall? And furthermore, is there asystem stored procedure I can use to move existing data and log files(master, msdb, AdventureWorks, etc.)? I seem to remember doing justthis with SQL 2000 some time back.Thanks,Eric
View 2 Replies View RelatedI've created a number of very large databases in Ms Access that I would like to upsize to SQL Server Express. I changed the default data directory location to point to a separate drive with over 400GB of free space on my home pc. However, everytime I try to upsize or link databases using the MS Access Upsizing Wizard the data is saved in the Data directory on my current hard drive and is not saved in the default location I specified. If I create a new database in SQL Express it is saved to the new location. Any ideas on how to force upsizing or uplinked data to go to the new default data location?
View 1 Replies View RelatedI want to change the default directory for my stored procedures. It is extremely inconvenient for me that they are placed in documents and settings... I cannot find a place in the Tools where I can change it.
Thanks.
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
Hi folks,
Can you please tell me how should I change the default root directory (used for db storage) in SQL 2005 Std. Edition. ?
Currently its configured to:
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
and I want to change the drive letter here.
Thanks in advance.
Cyk
I am trying to find a solution to a thorny problem I have encountered...
I am a newbie in Sql Server 2000, but have quite a bit of experience in databases, both on PC and mainframe.
I am currently writing a program, which needs, among other things, to create Sql Server databases, to be located NOT in the default Mssql2000 directory, but in the user's home directory.
I am using an ADO connection and trying to execute it, as follows:
The text of the relevant part of the program goes like this:
Dim strCreateDatabase As String
Dim MyCn As ADODB.Connection
Set MyCn = New ADODB.Connection
With MyCn
.ConnectionTimeout = 25
.Provider = "sqloledb"
.Properties("Data Source").Value = "MOP"
.Properties("Initial Catalog").Value = ""
.Properties("Integrated Security").Value = "SSPI"
.Open
End With
strCreateDatabase = "Create Database Rimon on (" _
& "Name = Rimon, Filename = '\mopusersMR_DiazsalpmsdataRimon_Data.mdf', " _
& "Size = 20, MaxSize = 100, FileGrowth = 5) " _
& "Log on (Name = 'Rimon', Filename = 'c:usersMR_DiazsalpmsdataRimon_Log.ldf', " _
& "Size = 2, MaxSize = 10, FileGrowth = 1)"
MyCn.Execute strCreateDatabase
Both the name of the Sql Server and the Win2K server is MOP.
All the directories exist, and I have full access to them.
When I try to run this command, I get the following message:
File
'\mopusersMR_DiazsalpmsdataRimon_Data.mdf' is on a network device not supported for database files.
When I omit this location, it works just fine.
If I run it on the server, using another directory, it works just fine.
Do you have any idea what I am doing wrong?
Thanks in advance for any help!
Rimon Rafiah
Hi all,
I have some problem about Reporting Service integrated for SharePoint 2007.
At first, I created 1 user named "User1" under "Domain1". User1 has role "SharePointAdmin" Group and Local Admin Group.
Then I created application pool named "RSAppPool". Its identy was "Domain1User1".
After that, I install Reporting Service and SP2.
Then I config Reporting Service to Integrated Sharepoint Mode below.
Report Server Directory Setting: "ReportServer" in "Default Web Site".
Window Service Identity:
Service Account : "Domain1User1" (Windows Accout)
Web Service Identity:
Report Server: "RSAppPool" (that created above)
Report Manager: Empty
Database Setup:
Database name: Database1 (Server Mode: SharePoint Integrated)
Credential Type : Windows Credentials
Account Name: Domain1User1
After configuration, I reset IIS and test browse to http://servername/ReportServer/ , then there is a Windows Login dialog.
I enter "Domain1User1" and User1's password. But User1 cannot right to access to "ReportServer" virtual directory.
This is error message
You are not authorized to view this page
You do not have permission to view this directory or page using the credentials that you supplied.
HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials.
Internet Information Services (IIS)
I'm not sure. Where is I config wrong? ....
Please help me.
Thank you very much.
Hi,
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?
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"?
I am trying to change the default trace value from 2 to 6, to incorporate rollover and server shutdown. I am using this code to create a new trace (with id: 2) with the value of 6 and use that to overwrite the default trace (with id: 1):
DECLARE @new_trace_id INT;
EXECUTE master.dbo.sp_trace_create
@someinteger = @new_trace_id OUTPUT,
@someinteger = 6,
@someinteger = N'C: raceTestTrace';
Then I disable the default trace. I am then using this code to overwrite the default trace (with id: 1) and replace with new value default trace (with id: 2):
-- get trace status
SELECT * FROM ::fn_trace_getinfo(NULL)
-- stop trace
EXEC sp_trace_setstatus @traceid = 1
, @status = 0
go
-- delete trace
EXEC sp_trace_setstatus @traceid = 1
, @status = 2
Then I enable the default trace. It works perfectly (default trace with id 1 is showing value 6) until I restart. Upon restart no default traces are enabled, once I run the script to enable the default trace then the values for the default trace (with id: 1) are back, and my value for rollover/server restart is back to 2.
I am a novice in DB learning.
A few months ago, I installed the ms sql server 2005, and use it to learn the book SQL Server 2005 for dummies. It worked just fine. But after i finished the book, i uninstalled the program and deleted the express database engine. Perhaps it was an incomplete installation, yesterday when i tried to reinstall the sql server 2005, i found some configuration tools still in the directory. So i used windows to remove those tools. After that, i reinstall the sql server 2005, and changed some values in the register. The reinstallation goes well. But after it was done. I started the management studio, there's no default database engine for me to connect! All I could find in Internet is how to connect to a database engine without information about how to create one. Can anyone tell me why and help me out?
i am a novice.
why we will go to install named instance as we having default instance already installed. Is there any advantage of named instance.
View 6 Replies View RelatedIn SQL Server 2012 Instance properties ,database default location is not showing system drive E: which was my backup drive. How can I add it again?
View 8 Replies View RelatedMy Integrity job started failing recently with the following error. Msg 701, Level 17, State 123, Line 1 There is insufficient system memory in resource pool 'default' to run this query. Process Exit Code 1. The step failed.
select @@ version
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64)
Jul 9 2014 16:04:25
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
System is having 4GB RAM and SQL is using most of it. It has 2 processors.
I have compile my application into a Smart Device CAB and also including SQLCE CAB file for SQL.
I have users who choose to install SQLCE into Storage Cards instead and causes the application not to function, when asked which destination to install.
I've done some search and found out about "wceload.exe /noaskdest" but I'm using NSIS to compile into a single .exe file, and its calling "CEAppMgr.exe"
How can I force SQLCE to install into its default directory without asking user?
Thanks!
I am new to sql sever management studio express, but a long time query analyzer user. This is a very basic question.
I want to change the default directory in sql server management studio express so that when I go to save a query, it is already pointed to the correct one. Where do I change that?
Thanks,
Nanci
Has 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 Related@pvColumnName VARCHAR(100) = Default,
However, I am unable to determine what is the value for Default. Is it '' ?
Default is not permitted as a constant - below fails to parse:
WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)
Hi,
Does anyone have a workaround or know of a fix to this problem:
Default value set to 'date pick' from date currently within field by setting value equal to that field . ie if date is 01/01/2010 date picker opens in Jan 2010 - works ok.
However, once published to Sharepoint and run through browser the Date Picker ignores the default value and the date picker opens for today. ie April 2008.
Any words of wisdom gratefully recieved,
Howard Stiles
How can I modify the for each loop to go through all the folders under parent folder?
ParentFolder
SubFolder1
SubFolder2
SubFolder3
If I already have SQL2005 installed, can I create SQL express databases for distribution in my app, or do I need to install SQLExpress to run side-by-side?
I actually had SQLExpress originally but upsized it to the full version. Now I want to be able to create portable DBs with my application.
And if this is possible, how do I go about creating the DB?
Thanks!
Hi all,
Now I want get AD value(e.g file path),how can I get this value from AD?
Thanks
Hie,
Someone can tell me haw can i do in order to migrate my server sql to active directory.
What is the step