I have a production SQLServer 6.5 on Nt 3.51 SP4 that had problems two weeks ago, an I had to restore the backup of the previous night.
we are testing our disaster recovery procedures on another server, identical to the production one.
We have installed NT 3.51 SP 4 and SQLServer 6.5 in the same order and with the same configuration of the production server, but i can't restore on the test server the backup files that i have succesfully resotred on the production server.
we make the SQLServer back up on file ( and then we backup those files on tape with NT backup) so I don't think is a problem of HW Tape Bios or Compression because i have tried to resotre directly the files without taking them from a tape.
1) I have created a new database , without data, with the same devices, in megabyte, that I have on the production server. than from enterprise manager I have started the restore from file, and after two seconds everything stops, and I can't even shutdown the task but i MUST turn the server off using the power button.
when I start the server again there are no specific errors in the event log nor in the SQLServer error log. The db i was trying to restore is marked "loading" and it is not available.
This same procedure works perfectly on the production server.
2) I have then created another db on the test server, and I have succesfully tranferred ( but not resotred) the produciont db onto the test one, using the transfer menu on SQLEnterprise manager.
3) another strange thing is that I have tryed to expand the tempdb, which is only 2 mega by default, but whenever i try to expand it, using enterprise manager, only the LOG area of tempdb sucessfully expands, not the data area. I don't know if this behaviour is related to the unsuccesfull restore or if it is another problem.
i have checked the sort order and character set of the 2 servers and they are the same.
Hi guys! When it comes to writing sql statements, I do ok. But I just know the basics on sql server dba stuff.I need to create a sql server login account that has the ability to backup and restore databases, BUT NOTHING ELSE.No ability to change anything other than via the restore process. I figured out how to do the backup portion of my requirement, but not the restore part.Any ideas? Thanks in advance!
I have a database running on sqlserver 2000. This database (let's call it TestDatabase) has 2 filegroups called 'PRIMARY' and 'SECONDARY', and is bulk-logged. The 'SECONDARY' filegroup is set to readonly since no data gets changed there by the applications, it is read-only data. Sometimes this read-only data needs an update, so I need to update the data in the 'SECONDARY' filegroup by running some long data-operations. These data-operations are executed and checked on a another database (which is a backup of TestDatabase) called TestDatabaseDemo. When everything is correct in TestDatabaseDemo we can copy all objects from the 'SECONDARY'-filegroup of TestDatabaseDemo to the 'SECONDARY'-filegroup of TestDatabase using DTS. This takes a long time and I want to try if I could speed up things by using the following strategy:
Take a full backup of TestDatabase
Restore full backup as TestDatabaseDemo
Run long-running data-operations on TestDatabaseDemo
Take a backup of TestDatabaseDemo
Restore only the 'SECONDARY'-filegroup from TestDatabaseDemo to TestDatabase, and keeping the 'PRIMARY' filegroup from TestDatabase as it was at that moment. Suppose we arrived at step 5, I am executing the following commands:
Code Snippet --make full backup of TestDatabase backup database TestDatabase to DISK='G: emp estdatabase.bak' with init --make full backup of TestDatabaseDemo backup database TestDatabaseDemo to DISK='G: emp estdatabasedemo.bak' with init
backup log TestDatabase to DISK='g: emp estdatabase.log'
--restore secondary filegroup from TestDatabasedemo-backup restore database TestDatabase FILEGROUP='SECONDARY' FROM DISK='G: emp estdatabasedemo.bak' with move 'TestDatabase_Data_Secondary' to 'G: empTestDatabase_Data_secondary.ndf', NORECOVERY --restore primary filegroup from Testdatabase-backup restore database TestDatabase FILEGROUP='PRIMARY' FROM DISK='G: emp estdatabase.bak' with move 'TestDatabase_Data' to 'G: empTestDatabase_Data.mdf', NORECOVERY --restore log and try to get db onlin restore log TestDatabase FROM DISK='g: emp estdatabase.log' with recovery
I get the following error: The log in this backup set terminates at LSN 6000000021500001, which is too early to apply to the database. A more recent log backup that includes LSN 6000000022400003 can be restored.
When trying to execute "RESTORE DATABASE TestDatabase WITH RECOVERY; " as last statement I get error: The database cannot be recovered because the files have been restored to inconsistent points in time.
How i can restore the read-only filegroup correctly?
Hopefully someone out there will be able to help me with this question. I'm trying to setup my C# program to use the Backup and Restore classes to perform backups and restores on an SQL Express 2005 database.
From the documentation I've read... it seems that when you go to restore the database, it's a good idea to backup the current transaction log. But when I do this using the Backup.SqlBackup method I get an error saying that no database backup is detected so a log backup can't occur. I'm not sure if this has something to do with the fact that I moved my backup files from the default SQL Express/Server Backup folder to a different location. Shouldn't it know I've already performed a backup though?
And does anyone know how to set a parameter to backup to a different file location than the default? I've been doing a backup then a file move. Works the same, but it would be nice to have everything together in the Backup object.
I have an sqlserver 2005 Enterprise edition (Evaluation version) installed on one of my servers.It got expired after 180days and now i want to transfer the database that was residing on that Evaluation version sqlserver to a new Development server.
Do we have any way to restore the database from a already expired Sqlserver trail version to a new Development server. I dont want make an upgrade. Please suggest me.
I am trying to restore a database backed up using SQL Server 2005 Express Edition to a server using MSDE. I get an error 3205 "Too many backup devices specified...64 max..."
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLAlamoDB.mdf'. (Microsoft.SqlServer.Express.Smo)
--------------------------------
Hi,
I got this error while trying to restore my database (which was running on SQL Server 2005 Express Edition) onto another machine's SQL Server 2005 Express Edition.
Is there a way to transfer data from a SqlServer db to a SqlServer Express db. I tried to use the backup file of SqlServer, but this file is not valid for SqlServer Express. Or there any alternatives?
When using AquaData or JDBC (inet tds driver), when doing an insert using SqlServer 2005, I get error "String or binary data would be truncated" when the data is actually OK. There are no triggers, etc. that would confuse the situation. It works fine in SqlServer 2000.
Create and populate table: create table maxtable ( tablename varchar (18) not null, [...] )
Try to insert into test3: insert into test3 (name, tbname) select i.name, o.name from dbo.sysindexes i, sysobjects o, maxtable m where i.indid > 0 and i.indid < 255 and i.id = o.id and i.indid = 1 and o.name = lower(m.tablename)
And I get the error "String or binary data would be truncated." The values being selected for i.name and o.name have maximum length of 18. There are other rows in sysindexes and sysobjects with longer values, but they are not being selected.
The error does not occur with SQL Server Management Studio, and does not occur using SqlServer 2000.
OK so there is some data in an Oracle DB that I need to query and analyze. Unfortunately, the criteria for selecting/grouping the data is stored in a MS Sql Server DB. This cannot be changed. SqlServerGroup Name ID# Item ConditionAAA123 1 a 1AAA123 2 a 1AAA123 3 a 1AAA123 4 a 2AAA123 5 a 2AAA123 1 b 3AAA123 2 b 4AAA123 3 b 3AAA123 4 b 4AAA123 5 b 3BBB123 1 a 1BBB123 2 a 1BBB123 3 a 2BBB123 4 a 2BBB123 5 a 2 OracleGroup Name ID# ValueAAA123 1 50%AAA123 2 55%AAA123 3 60%AAA123 4 80%AAA123 5 70%BBB123 1 35%BBB123 2 45%BBB123 3 50%BBB123 4 50%BBB123 5 80% I need to be able to get this:Group Name Item Condition ValueAAA123 a 1 55%AAA123 a 2 75%AAA123 b 3 60%AAA123 b 4 67.5%BBB123 a 1 40%BBB123 a 2 60% Any idea how I can get the data from these two DBs to talk to each other? Thanks.
can anybody tell me that: 1)How can i retrieve a binary field (image field) that stored in SqlServer2000 with Vb.Net and save it again in my hard disk that i have again that file? 2)can SqlServer itself convert image field to file with its store procedures? 3)can sqlserver run an exe file on local computer from its storeprocedure?
I have a development machine that I have prototyped with access. I don't know how to get the access data into Sql Server on the Internet. Can you help me? I'm not sure where to start. Thank you for any help.
Here is a structure of my table create table events( event_id integer primary key identity(1,1), event_name varchar(200), event_date datetime,) Note: The event_id is a automacally and we don't need to insert record in this field. for example i have inserted 10 records in a table then i deleted all records manually from sql server but when again I save the record the event_id field shows started from 11 even the table was empty, although it should be start from 1?
Can i fatch the data with to different sqlserver.i want a query i.e select query fatch the data this different database and diff.table.i.e. sqlserver to sqlserver and oracle to sqlserver
Hello sir,I have installed .net1.1version on windows2003 operating system.I have also installed sqlserver2000 on the same system.My problem is that when i am trying to get data from sqlserver from asp.net program i am getting error as 'access denied to user NT AUTHORITY/NETWORK USER'.I request you to kindly suggest me with an appropriate solution.Thanking you.Please email me on:-aanandkumar786@yahoo.com
Hi, How to expor data from Filemaker to Sqlserver? From the Filemaker, I am exporting data into Tab File(values are separated by tab). How to put data from that file into Sqlserver tables?
Hello i have one prob . i m using sql server 2000 . and i have write a store procedure to fetch data from multiple table using cursor . this query exculate in sql server . but i can't fetch this data from in our page . how to fetch data from using multiple table .plz help thnx
I have a products table in a Sql Server 2005 database. One of the fields contains the products attributes in xml. The most commonly used attribute is color. An element of color is inventory. This is the only way I could think of to maintain inventory numbers of each color of each product using someone else's (opensource) code. An example of an attribute field would be <?xml version="1.0"?> <ArrayOfAttribute xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Attribute> <Name>Color</Name> <Description>Shown in Black</Description> <Selections> <AttributeSelection> <FormattedValue>483 - Black</FormattedValue> <Value>483 - Black</Value> <Inventory>25</Inventory> </AttributeSelection> <AttributeSelection> <FormattedValue>484 - Lt. Tan</FormattedValue> <Value>484 - Lt. Tan</Value> <Inventory>15</Inventory> </AttributeSelection> <AttributeSelection> <FormattedValue>485 - Pink</FormattedValue> <Value>485 - Pink</Value> <Inventory>17</Inventory> </AttributeSelection> </Selections> <SelectionType>SingleSelection</SelectionType> </Attribute> </ArrayOfAttribute> How can I read this into the gridview control so that it makes sense to the user?Diane
I need to import data from excel file to SQLserver. What is the best way to do this? Please give as much explanations as possible (code example would be very-very helpful).
I'm working with sqlserver express 2005, and I have a lot of doubts about what sqlserver data types use. More exactly my doubt is about text types... nchar, nvarchar, varchar... what are the differences???
I have two problem : + The first, This is table store all items in bookshop system tblItems: IDItem Identity(Auto number) Namebook nvarchar2 Price nvarchar2 Chapters nvarchar2 Weight nvarchar2 (weight of book) ..... I design data type for Chapter,or Price,Weight is nvarchar2 ? <-----I wrong ? (I want to refer to principle of design the database) + The second ,When i design Price is the int datatype ! The default value is 0 ( I don't want to have this value ,i want to it is a empty field ) I really sorry because i ask too much ! Because i am a new programming ! Any Help or Advice would like appreciately ! Thanks u !
I want to import data from an excel sheet to SqlServer.... I use a linked server... I execute the following code:
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:MyExcel.xls',NULL, 'Excel 5.0' GO
sp_addlinkedsrvlogin N'ExcelSource', false, sa, N'ADMIN', NULL GO
SELECT * FROM ExcelSource...Sheet1$ GO
and I get the error:
Server: Msg 7314, Level 16, State 1, Line 2 OLE DB provider 'ExcelSource' does not contain table 'Sheet1$'. The table either does not exist or the current user does not have permissions on that table. OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='ExcelSource', TableName='Sheet1$'].
When I execute the command:
select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:ook1.xls',Sheet1$)
The question is why the datatype length is returned as -1, what happened to 'max'? is there any other way to retrieve the actual data length (ie., 'max') from the table definition?
I am extracting data from an SQLServer database to load into Teradata using DTS. The performance is abysmal. The same data in a text file loads quickly via multiload. I can move the data to other DBMSs via DTS quickly as well. Is there some way for me to improve the elapsed time/performance while using DTS? If not, what is the best way to move data from SQLServer into Teradata?
Dear friends, In SQLServer, it seems to make data stored in Unicode, you need to go to each column type and change it from varchar to nvarchar.
Apart from this, is there a direct way of Setting Unicode for the whole database at once, which would set all coulns to the unicode equivalent. It seems there is such an option in Oracle