Here is a senario that I am confused about
if I backedup a table on Monday 5 pm. this table has 3 rows of data.
On Tuesday, I inserted 3 more rows and backed it at 5 pm ( total rows now are 6)
On Wedesday, I deleted the first 3 rows accidently.
Which is more efficient : to restore the data from Monday ( in order to restore the first 3 rows)
OR
Restore the data from Tuesday( I will choose not to append to the available data )
This is a small sample, but if I am dealing with 40 GB of data. I want to know the best way to restore the lost data.
We have a database on our server in the office, which I have installed to test an application in delevelopment. I've got it all working and tested OK.
I've continued working on it at home. I've added some fields to one of the tables. The database in office has a data in which I whish to transfer to my test database - with the new fields added.
I have tried the backup and restore procedure, which works great, but it overwrites the entire database, I just want the data from all tables in one procedure each way, one to backup and one to restore.
I need to move a database from one server to a new server. Right now, I only have database file on CD which is generated using the backup feature of MS SQL server.
What I did was I copied the file from CD to the harddisk of the new server. Then I used the restore database, restore from disk, where I specified the location of the backup database file. When I began to restore, the error message I got was: The file 'e:MSSQL7dataGTCSQL_data.mdf cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file. Backup or restore operation terminating abnormally.
The error message in the error.log of SQL server is: BackupFileDesc::VerifyCreatability: Operating system error 3(The system cannot find the path specified.) during the creation/opening of physical device e:MSSQL7DATAGTCIntranetSQL_dat.mdf.
What did I do wrong? How to use the WITH MOVE option? This is the first time I use MS SQL server. So please give me detailed instruction if posible.
We are running 2014 enterprise. Have there been any features added to sql server that ease the challenge of restoring only data? I often find myself creating separate databases so that a restore of a db's data doesnt also restore procs and functions and vice versa.Â
I got a .bak file from my friendI want to restore the data base in my system(presently I don’t have any database in my system).But while I try to restore itI am getting an error“System.Data.SqlClient.SqlError: Directory lookup for the file "D:Microsoft SQL Server 2005mydatabase.mdf" failed with the operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Smo)�I have installed Microsoft sql server in a different directory not in D: where he is installedI can successfully restore the back up files of data base on my own systemBut not the backup files created from others systems
Hi all,I have installed sql server express edition and using this with asp.net website application. It was working fine,I am using .mdf file as database in app_code folder of asp.net website application, but now there is one issue which is with a particular table records.In this table some of the columns has got the same value for all records in the table. so, now i want my previous(original) data as backup, but unable to do so. I am using third party tools for recovery. Can anyone please guide me to resolve this issue.Thanks in advance
Hi, I restored the data files .MDF , .LDF files from my nt backup. Now i want to apply these file to a database. How i have to apply? Do i need to create new database and attach these files or how i have to do? I have no idea, how i have to work on it? pls suggest me. Thank you everyone.
I have been restoring database backup from server1 to Server2 and both database name is same till today. But I had an error today. I verified path, location and access everything is there but no luck to resolve.
I did a full backup of a db from one server(Express2005) and trying to restore it to a different instance of SQL2005 on the same development machine. (Also had some fulltext columns if that means anything)
Many failures but finally got it to report all was successful except the icon in Object Explorer shows (Restoring...) with no indication of any real activity going on. It's a tiny database with hardly any data in it.
Just not sure what the heck is going on there. It also won't let me into the database until this the (Restoring...) goes away.
I'm using SQL Server 2000 and need to restore a large database onto a different node. The problem is the original database has a 74 gb first datafile and the node where I need to restore it doesn't have a single drive that big. I'm trying to use a backup of the original database and restore it into an existing database on another node and am using the move options to put the files in the right places.
Is there a way I run the restore to split the 74 gb datafile across drives on my target node?
I have backup of data from SQL Server 7.0 and now when i'm trying to restore it into SQL Express 2005, I'm getting following error......
-------------------- Msg 3154, Level 16, State 2, Line 1 The backup set holds a backup of a database other than the existing 'GOSLDW' database. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. --------------------
Here is SQL i'm using to restore database,
RESTORE DATABASE GOSLDW FROM DISK = 'C:sqlserverDataGOSLDW' WITH MOVE 'GOSLDW' TO 'C:sqlserverDataGOSLDW.mdf', MOVE 'GOSLDW' TO 'C:sqlserverDataGOSLDW.ldf' GO
Why i'm getting this error? Am i missing anything here?
I am restoring a database with 10yrs worth of data which have monthly partitions but i would like to keep only 5yrs of data after the restore is done, what is the best/faster approach to delete the 5yrs data without deleting the partitions as that may cause the db in accessible.
I am cleaning database backup history as "Delete history if 4 months old" for backup file, and "Delete history if 1 week old" for Log file back.
lets say, I have disaster after 3 months, and I recover database with data and log backup files. Will I be able to recover with 3 month old backup file with just 1 week Log file backup. As I am keeing my Log backups only for 1 week.
I need to recover some data in a table but i'm not 100% sure the right way to do this safely.
I'll need to query the two tables to compare the before and after but how do i go about restoring/attaching the backup database to SQL without causing conflicts?
If I restore, I assume this would just overwrite which is obviously the worst thing that can happen. if i attach the backup, how does this affect the current live DB? how do i make sure that it's not getting accessed and mistaken for the live DB?
I use MSsql server 2000 and analysis service for create cube.But when I drilldown many Dimension on Analysis manager it can not show all level. If I drilldown some level it can show data but when I drilldown many level so it have many row (I test about 60,000) it can not show result. How can I show all result in Analysis manager or other tool ? Thank you :D
Hi,I can't seem to connect to SQL 2005. When I open Configuration Manager,these is no information in any of the items in the tree.I checked that WMI was configured correctly at:http://msdn2.microsoft.com/en-us/library/ms188690.aspxI can't see any instances at all here.However, when I use Management Studio I CAN connect to an instance of SAL2000 and see databases I have previously defined.I'm running WIN 2003 with SQL 2005 standard and login as Administrator. SQLbooks on line don't seem to have anything to say about this.How can I get SQL Server Management Studio to 'see' the SQL 2005 databaseengine so I can create tables? I *think* its running because there is the'sqlservr.exe' process running, and during installation there is no issues.I had SQL2000 server running fine on the windows 2003 box. I then installedSQL 2005 Express. They both worked together fine. I then uninstalled SQL2005 Express and installed SQL 2005 Server. But when I open SQL ServerManagement Studio, I can only connect to the SQL 2000 engine. In the Objectexplorer, it says v8.0.2039 (which I think is SQL 2000 Server, because I cansee the existing SQL 2000 databases).Any help is greatly appreciated...Jack.
For seting up the connection should we use Connection Manager or New Connection From Data Source?
The advantage of using Data Source is that we have the connection avalilable at project level but I've studied it somewhere that Data Source is used for SSAS not SSIS.
But I don't have enough explanation to give it to my team, that why I am not using Data Source.
We are working on VSS, do we have to reset the connection managers if want to test our packages on some other machine(else than the one on which it was developed)?
I am trying to implement a dump and load settings activity into a DTS package. I am modeling it after an existing DTS package with the activity already in place. It also has some conversion tasks included. I've got the data transformed to the correct format, I need to know how to load it into everest. Can anyone tell me how to load it or where the documentation is online?
I don't really understand the difference between the "data source" and the "connection manager" in a SSIS solution.
I tried to create a data source (if I understood correctly, I will ba able to share that connection between all packages in the solution), then I create a new connection manager inside a package. But when I make a modification in the data source, nothing change in the connection manager ?!!?
What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ? If it is I dont't understand the avantage of making a new data source ....
Does anyone out there know a way to export data from the results pane in the Analysis Manager to Excel, or even text? I had thought this would be simple and intuitive to accomplish (after all, it is reasonable to think they would assume you would need to display the results of your analysis), but have found it to be neither. Your help is much appreciated.
I'm trying to use enterprise manager (v 8.0) to generate a sql script. I know I can select the tables I want to script and then select "Generate SQL Script".
My problem is that I can't seem to figure out a way to get the INSERT statements for all the data in the tables into that same script. I can get the table CREATES, ALTERS, etc.
Is there a way to include the INSERTs for data as well???
I'm running a report using MDX. In preview, the report displays correctly, but after deploying to Report Manager, where there were once numbers there is now '#Error'. The string fields display correctly, its just the numeric ones that are not working.
Firstly the disclaimer: im a total SSIS bunny. Ok, with that out of the way... What i want to do is have data read from an MSMQ message (which will be a simple XML message) and have it transformed and inserted into a simple table. Sound simple, but i cant work out how to do it.
I started a new Integration Services Project and dropped a Data Flow task on the Control flow tab, then double-click it to go to the data flow tab. I have added a MSMQ connection manager which points at my private queue and i can hit the test button and it sees it tests ok. How do i wire that into my data flow?
On my dataflow tab i have a SQL Server Destination (set to the local SQl server instance) and ive been trying a DataReader source and try setting the Advanced Editor "Connection Manager" column on IDBConnection row to point to my MSMQ connection manager. I get some error "cannot aquire a managed connection from the run-time connection manager". How do i get a Data Flow source which reads the XML message off my MSMQ connection manager??
On the Control Flow tab you can add a Message Queue Task and i can bind it to my MSMQ connection manager and set it up as a recieve task etc, but how do i use that?
I'm a report developer who has created some reportsd against sybase data on another server. I am developing on a 64 bit Server 2003 box using SQL 2005 Reporting services. I hace created 32 bit ODBC data dsns using the supplied utitlity (odbcad32.exe). As I am creating the reports I can pull data using the data sources without a problem, however when I deploy my datasource and report to Report manager and run I get the following:
An error has occurred during report processing.
Cannot create a connection to data source 'Chicago_Reporting'.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
This has been driving me crazy for 2 days now. Any help would be very much appreciated.
I have a task that uses an Excel connection manager to read in an Excel file. In the file there are 6 date columns. The date columns can have either a date or the letters NA. If I look at the External Column properties from the advanced tab, 4 of the date columns have a datatype of DT_DATE. The other 2 date columns have a datatype of DT_WSTR. The 4 columns with DT_DATE, my data looks okay as when it is read by the connection manager. The issue is that the 2 columns that are DT_WSTR, all dates are turned into NULLS, while the NA's are just fine. I have tried to change the datatypes in the advanced tab to DT_DATE, but it won't work.
I don't have any idea what to do to get this working correctly. Can anyone PLEASE provide some insight and suggestions or a solution?
A user was created with a limited privilege under the USERS group. Once this user loged in the Report Manager he is acting like an Admin and Content Manager, though he is not given even a browser role.
What do u think that this guy is acting like a Super User evenif he is restricted to a browser role on the Report Manager ????????????
I am running on mssql 6.5, sp4. We have been trying to use EM transfer manager to move one test database on one server to another database on another server. We are dealing with 135 tables on this database. The transfer works up until about the 80th atble and then just dies but the scheduled task says it failed and check error log. The transfer creates the tables on the destination database but only loads the data until this one table.
WE use all of the options in EM Transfer manager which are st as defaults.
THere is no one on the source or destination databases locking this table.
Other smaller databases were successfully transferred from one database on one server to the other database on the other server without any problems today and yesterday.
I am exporting data to database server from local server through Enterprise Mgr. Data is successfully transferred along with table structures. Only problem remains with the fields which are set IDENTITY.
Even I tried to find out ALTER TABLE statement so that I can alter the tables using Query Analyzer but I found that setting IDENTITY through query is not permitted after SQL Server 6.5. Why so?
Is the any way to set IDENTITY fields without Enterprise Manager?
I am using SSRS 2005 for reporting. I am not able to see the "New Data-driven Subscription" button in Report Manager. But I am able to see "New Subscription".
I am testing an application that uses mysql that works 100% fine with Microsoft Windows XP on Windows Vista Premium Home Edition.
Using Vista's ODBC Data Source Administrator I have set up and successfuly tested the DSN Connection to the Database.
However, when I call the set up DSN with the application I get [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
I am using the mysql-connector-odbc-3.51.12-win32.
I have tried editing the registry DSN files - the problem still persists.
I have an issue when using Report Manager for viewing reports that use a certain Oracle data source. When viewing the reports in Report Designer the reports display without problems. But when trying to display the reports with Report Manager, after deploying the reports to the Report Server, I now get the following error message:
blablabla....ORA-12705: invalid or unknown NLS parameter value specified
For testing purposes I am running Report Designer, Report Server and Report Manager locally on a PC with Windows XP. I am using Reporting Services 2005, IIS 6, Visual Studio 2005, an Oracle 9.x client and an Oracle 9.x (not 100% sure) database that is hosted on a remote server.
Earlier I got an error message that stated "ORA-12154: TNS: could not resolve the connect identifier specified". Access rights to some Oracle folders for the Network service account did the trick here but then I got the "NLS parameter" error message. What I have tried to do is removing the NLS_LANG register variable and also modified the value of the variable so that it matches the value of the NLS_LANG variable on the server that hosts the Oracle database, but none of them have worked. Any ideas?
Maybe this is an issue that is more appropriate in an Oracle forum but what the heck..the problem could reside in Reporting Services..
I changed one data source settings in my SSIS project to use Windows Authentication instead of SQL Authentication. This data source is used to create connection managers in all SSIS packages. Soon after the data source was changed, when I opened any package using the connection manager that is based on this changed data source, I got an alert message box like the one given below showing different connection strings in data source and package connection manager. That's understandable (why can't VS 2005 do this silently or give an option to let this happen silently? that would be nice.).
Here is the problem. Even after both data source and connection manager are synchronized, still the same message box keeps on coming when ever I open any affected package (about 20 packages!). It is very annoying as you can see that old and new connection strings ARE IDENTICAL, still VS 2005 is not able to recognize this. I installed VS2005 SP1 even before I ran into this problem. I asked my friends to open this project and check the behavior on his machine. Same thing. Is this a known bug? How to fix this problem? Thanks for your help.
=====
Message box "Synchronize Connection Strings" (VS 2005 status bar shows "Performing post package load operations"):
This package contains at least one connection which is based on a datasource. The connection string for conenctions and data sources listed below are currently not identical. Connection strings of connections will be updated to reflect those on the datasource.
Connection: SqlServer.NET.HBI_DW.hbiuser
Data source: SqlServer.NET.HBI_DW.hbiuser
Old Connection String: Data Source=SDAL1060DB;Initial Catalog=HBI_DW;Integrated Security=True;
New Connection String: Data Source=SDAL1060DB;Initial Catalog=HBI_DW;Integrated Security=True;
