Need help .
There are 3 log files attached to a SQL Database . I would like to remove one of them as it was created by Previous DBA for temp use (Don'r ask me why?) If I run DBCC ShrinkFile with EMPTYFILE , would it let me drop that file or is there any command to do it? OR is it not possible at all
When you create a dump device, then add backups with the NOINIT arg, the space taken up on the device grows, is there a way of removing individual backups from the device without (i guess) purging the whole lot by running WITH INIT?
I can get a list of the files by running the RESTORE WITH HEADERONLY, but I can't see how to remove individual ones.
I cannot install SQL SErver 2005 because of files still on my hard disk from beta version. These include sqlmgmprovider.dll and svrenumapi.dll. The uninstall program is no longer available. How do I get rid of whatever remains of the old beta installation so that the released version of SQL Server 2005 will install?
View 9 Replies View RelatedI'm converting DBs from SQL7 to SQL2K; the previous owner & server issues had created secondary data and/or log files. The size of the databases, and the configuration of the new server, do not warrant those secondary files. In restoring the databases from SQL7 to SQL2K, either by detach/copy/attach or backup/restore, is there a method for deleting the secondary files without, of course, losing any data?!
TIA,
Al
I was trying an edition upgrade from SQL 2005 standard x64 to SQL 2005 enterprise x64 using the "setup SKUUPGRADE=1" from the command line. The SO is W2003 server R2 enterprise and I have a SQL clustered environment with 2 nodes (active/passive).
I select to upgrade the SQL module and I mark the checkbox for the cluster installation. The installation seems to work properly untill setup is doing the task "removing backup files". Then 2 "unexpected error" message appears as follows. The 2 above windows appear when you click on the help icon.
Link to screen capture
The following is the end of the SQL setup log file:
________________________________________________________________________
Property(S): OutOfDiskSpace = 0
Property(S): OutOfNoRbDiskSpace = 0
Property(S): PrimaryVolumeSpaceAvailable = 0
Property(S): PrimaryVolumeSpaceRequired = 0
Property(S): PrimaryVolumeSpaceRemaining = 0
Property(S): RSVirtualDirectoryManager = Reports
Property(S): RSVirtualDirectoryServer = ReportServer
Property(S): SqlActionManaged = 3
Property(S): SqlStateManaged = 2
Property(S): Sql_sqlSqlUpgradeSequence = 387709900
Property(S): SOURCEDIR = I:Setup
Property(S): SourcedirProduct = {79F1B65E-8FC0-4D03-954D-F9E71C85AEC7}
Property(S): HotfixScript = 1
Property(S): DebugClsid.B2156BC9_6CE8_422F_A957_E99A65256B1D = {A42D1020-AA4C-401D-B7BD-26C333C0D627}
Property(S): SQLBROWSERACCOUNT = HEREISTHEDOMAINheretheclusteraccount
Property(S): ProductToBeRegistered = 1
MSI (s) (84:F8) [00:32:10:598]: Product: Microsoft SQL Server 2005 (64-bit) - Update 'Hotfix 3024 for SQL Server Database Services 2005 (64-bit) ENU (KB000000)' could not be installed. Error code 1603. Additional information is available in the log file C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0024_SV00010-2_SQL.log.
MSI (s) (84:F8) [00:32:10:613]: Note: 1: 1708
MSI (s) (84:F8) [00:32:10:613]: Product: Microsoft SQL Server 2005 (64-bit) -- Installation failed.
MSI (s) (84:F8) [00:32:10:613]: Attempting to delete file C:WINDOWSInstaller75d5c.msp
MSI (s) (84:F8) [00:32:10:613]: Unable to delete the file. LastError = 32
MSI (s) (84:F8) [00:32:10:645]: Cleaning up uninstalled install packages, if any exist
MSI (s) (84:F8) [00:32:10:645]: MainEngineThread is returning 1603
MSI (s) (84:CC) [00:32:10:754]: Destroying RemoteAPI object.
MSI (s) (84:74) [00:32:10:754]: Custom Action Manager thread ending.
=== Logging stopped: 18/12/2007 0:32:10 ===
MSI (c) (34:B4) [00:32:10:832]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (c) (34:B4) [00:32:10:832]: MainEngineThread is returning 1603
=== Verbose logging stopped: 18/12/2007 0:32:10 ===
_______________________________________________________________________
Does anyone have any clue or idea about the possible problem?
Hi everyone,
I'm trying to integrate SQLEXPRESS in a custom application ("the app"). On installation, a new instance ("myNewInstance") is created, and a new DB ("myDB") is created on that instance.
Upon de-installation of "the app", I remove the new instance. To that end, I'm calling the SQLEXPRESS setup like this:
Code Snippet
setup.exe /qb REMOVE=SQL_Engine INSTANCENAME=myNewInstance
The Problem: While the service and the instance are removed, the instance's data directory is not, and the DB Datafiles are still there. Now if I reinstall the app (re-creating myNewInstance), it uses the same directory structure, and when I try to re-create the DB, I get the following error message:
Code Snippet
Msg 5170, Level 16, State 1, Line 1
Cannot create file '[...]MSSQL.2MSSQLDATAmyDB.mdf' because it already exists. Change the file path or the file name, and retry the operation.
So here's the Question: is there
a) any way to tell setup.exe to completely remove the datafiles when uninstalling the instance, or
b) any way to tell TSQL to overwrite the old datafiles if they exist?
Thanks in advance,
Thorsten
HELP!
For the past several weeks, I have been trying to install SQL Server 2005 on a Win XP SP2 PC. Regardless of the options I choose (SQL Engine + Client Tools, Client Tools only, etc.) when the installation gets to the end of the Client Tools setup and the setup status displays "Removing Backup Files", my PC appears to hang.
For a period of time, there is a fair amount of HD activity which gives me the impression that files are in fact being deleted, but then there is no HD activity and the installation appears to hang. Finally I give up and kill the setup process and reboot.
After reboot, it appears that the client tools have been installed (the icons appear and I can open the Management Studio, but when I try to connect to an existing instance of SQL, that hangs. This occurs for either Windows or SQL authentication.
Has anyone experienced this behavior and determined the root cause? What EXACTLY is happening during the "Removing Backup Files" phase of the installation?
Thanks for any help in advance,
Marc Mueller
I have a virtual machine that previously had SQL Server 2005 installed. Since I changed the computer name, I have to reinstall SQL. I removed all the SQL components and am trying to reinstall the database and reporting components. The installation is failing on the "Removing temporary files" step. According to the setup progress screen, the following components have been installed:
OWC11
SQL Server Bakcward-Compatibility Files
SQL Server Database Services
Reporting Services
Visual Studio Integrated Development
SQL Server Books Online
SQLXML4
Workstation Components, Books Online - this step is still in "configuring components" status.
The virtual machine is running Windows 2003 Server SP1 with all current patches, using just under 2 GB of memory (1920 MB of RAM), the host is Windows XP SP2 with all current patches, with 4 GB of memory.
I had no problem doing this procedure on a different virtual machine, so I'm not sure what the problem is here.
I would like to remove data from a sqlserver database but keep the structure the the db. How would I do this?
View 9 Replies View RelatedI inherited an existing database application about 6 months ago. I'vefinished converting it to use SQL Server 2000 as the backend (MS Access2002 frontend w/ Userforms+VBA code pointing to the backend).In its current state the application makes use of a ton of embedded SQLstatements and I'm thinking about the best way to clean this up. Itseems like the best way would probably to encapsulate each of the SQLstatements into its own stored proc and then have the MS Accessapplication call the sp. However, this would result in a very largenumber of stored procedures.Anyone have any links or suggestions regarding best practices onremoving embedded SQL scattered throughout a DB app? Thanks.
View 1 Replies View RelatedHi there,
I have a large (420GB) database that has never had data archived off before. I taken a backup to a test server and run a script supplied by the product vendor which has removed a large ammount of old data no longer required.
I have checked within enterprise manager that this data has now gone, however the actual file itself has not shrunk in size. Is there a further step I need to take to get back the space.
Kind Regards
When I create a table in sql server database name appears in prefix of table.
My database name: Digitall
My table name: Digitall.News
How can I remove Digitall prefix from my tables?
We have transaction replication set up on one of our servers whose replicated DB is used for reporting purpose:
Now, the replicated database "D" of size 350 GB has mainly grown huge as compared to Published DB (200 GB) because of the index sizes at subscribed DB. Therefore:
I have found that Database D has one table of data size 15 GB, with rows 8349533 and index size 28 GB the biggest in the database. And adding have total of 109 Non clustered indexes:
On using SP_blitzindex i have found approx 50 NC indexes unused with below usage:
Reads: 0 Writes:273,243 and total size of indexes being 18 GB
Last User Seek
Last User Scan
Last User Lookup all '0'Â
0 singleton lookups; 0 scans/seeks; 0 deletes; 0 updates;Â
except for few 3 or 4 where updates are more than 4000.
Is this sufficient enough to delete all the above 49 unused NC indexes? And Can i create Missing index on Subscriber database?
When adding/removing tables in SSMS 2016 CTP2, I keep tripping into:
Index was outside the bounds of the Array
When editing a SQL Server 2014 Database.
If I use the SSMS for SQL 2014, the error is not happening. Possibly a reintroduced bug. Tried to use the Connect site to submit, but it says I am not enabled to use this area.
Hi all,
I downloaded and ran AdventureWorks.msi into my SQL Server Management Studio Express (SSMSE) one year ago.But I did not know how to attach it to my SSMSE then. Last week, I deleted it from the "Add or Remove" of Control Panel and I downloaded the new AdventureWork.msi and installed it my SSMSE. Today, I tried to use the Database Explorer of VB 2005 Express for the first Stored Procedure programming. I clicked on AdventureWorks.mdf and I got the following error: One or more files do not match the primary of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupt and should be restored from a backup. Cannot open user default database. Login failed. Login failed for user 'CENADe1enxshc'. Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data_log.ldf' does not match the primary file. It may be from a different database of the log may have been rebuilt previously. Please help and advise me how to correct this problem.
Thanks,
Scott Chang
Hi all (newbie @ asp.net)(oldie @ ASP 3)What is the purpose of using an attached MDF database files in the App_Data folder on a web site as to importing it into the SQL server directly or creating it on the SQL server. Does a mdf database attached file purely use the SQL server as a connection interface.Is it something similiar to DSN(ODBC) Connections for ms access databases.
View 2 Replies View RelatedHi, all experts here,
Thank you very much for your kind attention.
I am trying to modify the files path (primary file, log file) of databases, but it looks like I am not able to mofidy their files path directly from the database property dialogue? Would please any experts here give me some ideas on what else can I try to figure it out? Thanks a lot in advance and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi all, i encountered this problem.. whenever i tried to create publications it always prompt me this error msg "sql server replications does not support nicknames,such "." or "(local)". i know i need to delete the registration and register a new server with a different name.. i just want to double confirm whether deleting the registration will affect the databases or not ? :
View 1 Replies View RelatedI have a client that has POS software called Restaurant Pro Express (RPE) from [URL] ...
Their old POS computer had a hardware failure, but I was able to attach the hard-drive to another computer and recover the data. RPE uses a MSSQL database system. However, my client doesn't seem to make backups very often
- the last one is dated January 5, 2015. I was able to copy the C:Program FilesMicrosoft SQL Server folder over which contained the instance as well as all the data files - and has up-to-date information. The instance in the recovered Microsoft SQL Server folder was called MSSQL.1. I installed the RPE software on their new computer, and it too now has an instance called MSSQL10_50.PCAMERICA. The new computer is using MSSQL 2008 R2, while I believe the old computer would have been using MSSQL 2005.
I am no DBA expert, especially when it comes to MSSQL. Is is possible to 'restore' the database from the 'raw' .mdf and .ldf files of the old computer to the new computer / database instance? If so, how should I proceed?
Hi,
I need to restore a database to different server.
Orignal .mdf is 16 G, Now on the new serever I have 16 G adn 12 G of drive space available.
Is there a way i can split the data of 1 mdf over to 2 or 3 data files on the new server ?
Thanks in advance.
:confused:
HiWhat is the easiest option for Exporting ALL of my SQL Server tables into a CSV file (either separate CSV files for each table or one big file with all the table columns and data) ???I just want to Backup my SQL Server database like we backup MySQL database using phpMyAdmin. Unfortunately, my SQL hosting company does not allow backups for free.Thanks for help
View 1 Replies View RelatedHi, I'm relatively new to SQL and ASP.NET and I'm after some assistance to what I hope is an easily achievable goal.
I need to upload some files (.pdf, .doc, .jpg - whatever) to a directory on my webserver. Once this has been done, I'd like to write some data to an SQL Database Table that will contain information about these files in a datagrid (or something similar) along with a link to download them.
E.g I upload car.jpg which is located at /images/secure/car.jpg and is a photo of car. When a user logs in, they will be shown a datagrid which pulls information from said SQL database and in this datagrid will be something like:
Filename Description Link
car.jpg A pic of a car Download
If anyone is able to help me out with regards to linking to a file from an SQL dB it would be greatly appreciated, and if you require any further information just let me know :)
Ben.
has anyone got any opinions on the pros/cons of storing mp3 files in the database instead of the file system ?
How could this be done ? Would it be a binary data type ?
Hi all,
Can someone please brief me on how to save 'pdf' files(there are about 15 files) into a SQL database and retrieve/open it from a datagrid?
I have about 15 pdf files on my webserver and need to save it in the SQL database, so every pdf file saved in the database will have a primary key and the file will be saved either as 'ntext' or 'binary' type...I just have this rough idea.
If someone has a ready code in VB.net then nothing better( i know thats a shortcut :) but it will help me like a tutorial. C# code will do too, I can then convert the code to Vb.net
Thanks a ton.
I was following an example from the book "ASP.net unleashed" in Chapter 14, where it describes how to upload a file directly into the database.
I got it to insert the file into my database as described, and with changes created a master and detail form to select the file and display it.. (which I assume had worked).
Except, when attempting to access the file it gives two different errors. For a ppt file it gives:
The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.
An invalid character was found in text content. Error processing resource 'http://localhost:3870/SeminarOrganiser/view_file...
and for a simple txt file:
The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.
Invalid at the top level of the document. Error processing resource 'http://localhost:3870/SeminarOrganiser/view_file.aspx?...SELECT suggestEvent.suggestID, suggestEvent.uID, users.uFirstName, users.uLastName, users.uEmail, suggestEvent.suggestTitle,...From these two errors, I cannot really make sense of what has gone wrong or even why XML has even been mentioned. Looking around on the net has not allowed me any further progress.In any case, would anyone be able to explain to me what might have gone wrong?Alternatively, any links that may help would be greatly appreciated.Thank you very much in advance.
Hi,
I am developing a tool that takes snapshot of a particular database.
By snapshot I mean, attaching the .LDF & .MDF files.
Would you please let me know how to access these files & take a backup of the same.
And also how to restore them back. (using c# programming)
Thanks,
Archana A.A.
Hi,
can we keep a file like word file or html file in the sql server database?
if yes, then can we search any thing in these stored file?
regards
mihir
Usually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who's properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?
View 11 Replies View RelatedHi,
I've got a database called Webmine which is running with multi users.
I want to duplicate it to generate archive.
My problem is that I can't duplicate the MDF file (and the ldf file optionally) without detach the webmine db and reattach after the duplication.
Detach this database is a problem durring this processing of duplication because we can have users always working on Webmine.
Is there a way to proceed without detach Webmine ? Or perhaps it's stupid or dangerous to duplicate a DB if users are always working (that's my position but here, everybody does not agree with this position so can anybody certify
I've got or not reason ?
Thanks to your answers
Best regards
Axel
I am but a lowly DBA unworthy of this task...
I have a large (200+ GB) database with many (100+) files. Please don't ask me why I did it this way; I inherited this database -- really, it wasn't my idea.
My predecessor also seemd to think that backups were unnecessary; there have been no backups of this database -- ever.
While we cast about for a good long term solution, I am trying various short-term options. One I want to explore is to back the database up in chunks -- ie, by backing up individual files. I created a test database with five files (there is only one filegroup on the production server). Here is the DDL:
-- =============================================
-- Create database on mulitple file groups
-- =============================================
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'MultiFile')
DROP DATABASE MultiFile
GO
CREATE DATABASE MultiFile
ON PRIMARY
( NAME = MultiFile,
FILENAME = N'e:MSSQLDataMultiFile.mdf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = MultiFile2,
FILENAME = N'e:MSSQLDataMultiFile2.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = MultiFile3,
FILENAME = N'e:MSSQLDataMultiFile3.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = MultiFile4,
FILENAME = N'e:MSSQLDataMultiFile4.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = MultiFile5,
FILENAME = N'e:MSSQLDataMultiFile5.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
LOG ON
( NAME = MultiFile_Log,
FILENAME = N'e:MSSQLDataMultiFile_Log.ldf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
GO
I have tried the following backup script:
BACKUP DATABASE MultiFile
FILE = 'MultiFile',
FILE = 'MultiFile2'
TO Backup01
WITH
INIT
BACKUP DATABASE MultiFile
FILE = 'MultiFile3',
FILE = 'MultiFile4'
TO Backup02
WITH
INIT
BACKUP DATABASE MultiFile
FILE = 'MultiFile5'
TO Backup03
WITH
INIT
And here is the restore script:
RESTORE DATABASE MultiFile2
FILE = 'MultiFile',
FILE = 'MultiFile2',
FILE = 'MultiFile3',
FILE = 'MultiFile4',
FILE = 'MultiFile5'
FROM Backup01, Backup02, Backup03
WITH MOVE 'MultiFile' TO 'E:MSSQLDataaMultfile.mdf',
MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile2.mdf',
MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile3.mdf',
MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile4.mdf',
MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile5.mdf',
MOVE 'MultiFile_log' TO 'E:MSSQLaMultFile_Log.ldf'
However, running the Restore script generates the following error:
Server: Msg 3259, Level 16, State 1, Line 1
The volume on device 'Backup02' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I'm not sure what to make of this. What do I need to alter in either the backup script or the restore script to make this work?
I am trying this because my objectives are to:
1. Limit the amount of work that the server is performing during any one given backup session. The idea that I have is to backup the database in chunks using a rolling 3-5 day window.
2. The database must be up and operational 7x24x365 (except for one 4 hour window each month)
3. This is not the long-term solution; but I need something to tide us over until we can purchase additional storage capacity.
I appreciate any thoughts and or guidance you can provide.
Regards,
hmscott
Sorry for the ultra-beginner question, but I've just recently started playing with SQL 2005 Express Edition for a task I've been assigned to at work.
I'm building an ASP.NET 2.0 web site that needs to connect to a SQL database to pull information. I installed SQL Express 2005 and installed the SQL Server Management Studio and I've managed to build a small database with one table.
I noticed that by default, the mdf and ldf files are located in C:Program FilesMS SQL ServerMSSQLDATA (or something along those lines). The website I'm working on is temporarily stored in C:WebSite. I need to move the database files over to C:WebSiteApp_Data so I can access them easily with ASP.NET and VS 2005 Express.
I can copy and paste the mdf and ldf files, but then I can't figure out how to point the SQL Server Management Studio to the new location.
I am obviously just a complete newbie at all of this. It's pretty sad that I can't figure out how to simply move the database from one directory to another on the same server... it makes me worry about when I'll need to move it to the new webserver!
Could anyone point me in the right direction at least?
Thanks in advance!
Hi, I am trying to get Database name in Trace files, I can see DBID and I can get DB name through ID, but can I get DB name in trace files also.
View 4 Replies View RelatedI have a database file LEAR_Index(yes, it hold index data) from a havehave recently removed a bunch of data.It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink thefile to lean 30-40Gb.I´ve been trying this:dbcc shrinkfile('LEAR_Index',40000)But to no apparent avail; the file did not shrink.I´ve tried using enterprise manager for this but it consistenlycrashes when performing this operation.#Any thoughs, idear as to what i might be doing wrong?
View 6 Replies View Related