I only have the ldf file and the bak file.
My bak file was current to 3/2/2007
My ldf file was current to 3/25/2007
I do not have the mdf file.
How to I apply the transaction from the ldf file to my resotred database?
Hello!
I am trying to find out name of the backup file database was restored from (I am restoring from DISK), something similar to physical_device_name in backupmediafamily. restorehistory doesn't have this information.
Any advice is greatly appreciated.
Thanks,
Igor
We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message
System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
Please note we left the DataBaseA in the old SQL2000 server.
Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup
Many Thanks
Hey Guys,
I have a .dmp backup file from an oracle database. I want to restore it into an sql server database. Can this be done?
I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Thanks
Prashanth
hi all,
our datbase was restore but the publications were all gone.
however replication monitor shows publication and subscription.
worst. can create the publication with the same name because
it raises an error saying that it can't drop publication becuase
there is a subscription in it.
how can i remove the publication when the are already gone in the publication
folder but exist in the replication monitor
thanks for the help
Is there a way to restore a 2000 database to 2005?
will the restore process convert automatically to 2005?
Hi,
I have recently restored a backup of a SQL Server 2000 Database (from my production server €“ which is a shared hosting service) on my dev machine so I can do some testing. The issue I am having is with the permission of objects. Some of the objects were created with the dbo user and some with another login (myLogin). The ones that were created with dbo work fine on my dev machine. The others do not work unless I prefix them with €œmyLogin€?€¦ I get an €œInvalid object name€? if I don€™t prefix them. This is problematic because there are hundreds of stored procs and tables that I would need to modify in order to use them on my dev machine.
Can anyone tell me how to create a new user with the necessary permissions so I can execute my procs and access tables without having to prefix them?
Hope this makes sense €“ please let me know if it doesn€™t.
Thanks,
Mike
Hi
I have an sql server 2005+sp2 instance and a db on it.
I have create a database master key (specifying a password so it WILL NOT get encrypted by the server master key) on my database and backed it up
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test'
go
BACKUP MASTER KEY TO FILE = 'c:Genesis_DBMasterKey.key' ENCRYPTION BY PASSWORD = 'test'
Then I have created a asymmetic key without specifying a password therefore the key will get encrypted bt the database master key.
CREATE ASYMMETRIC KEY card WITH ALGORITHM = RSA_512
And then for test I run
DECLARE @Encryptvalasym varbinary(MAX)
SET @Encryptvalasym = EncryptByAsymKey(AsymKey_ID('card'), 'EncryptedData')
SELECT @Encryptvalasym
SELECT CONVERT(varchar(max),DecryptByAsymKey(AsymKey_ID('card'),@Encryptvalasym) )
And the result is OK.
Problem: as a recovey plan if I restore the database on the same or other instance (althogh the asymmetic key is part of backup) the decrypt doesn€™t work.
I have tried to create a bank database restore tha databse master key and recreate the asymmetric key, but I€™m afraid I€™m still not having any luck with it, as it does not decrypt.
My main issue is that is any case if I have to recover the database , how do I have to make sure that encryption/Dycription will work?
Thanks
Dear All,
I have a user with DBOWNER,DBCREATOR role and access is set to deny on viewing any other databases. Now when I connect using this user and try to restore the database. after restore, I can not open it or access it using the same user.
what is causing this.
Thanks,
Is there any workaround on this? I need to migrate my data in sql server 2005 into sql server 2000. Thanks in advance.
View 2 Replies View RelatedHi I took a Full Backup on Production Server and Restored the Same Database on Testing Server, i am able to see all the Tables Stored Procedure and View except Logins, i am not able to see any logins, there are total 650 Logins under the Database how can i see the logins and how to restore the logins, while i am trying to create any logins it is saying Error Occured login is already existing in the Database..
Bye,
Madhavi
Hi All,I have to a few backups restored from a set of backup files. Thebackup files have been removed from the drive because of some storageconstrains...For some data comparison reasons, I need to figure out the actual dateof these backup files that is used to restore these databases. Isthere any system table I can query to figure the actual backup timesof these databases...Any help will be much appreciated.. Thanks in advance.- Aravin
View 1 Replies View RelatedThanks in advance for help.
Could someone please help me for SWL backup restoration and db by user
I restored SQL 7.0 database to SQL 2000. ( by creating empty db on SQL 2000 and restored from SQL 7 backup) -- restore ok..
I need to use same SQL user which is admin for DB on SQL 7, for SQL 2000 also. ( DB user is sql user not domain user)
I put mixed authentication mode ( windows and SQL) in SQL 2000 enterprises manager security tab setting.
I can see DB user is available in DB user list on restored DB but can not access DB when I try to access from query analyser
I tried to create new login with same name as it was in SQL 7 and tried to give full admin access on SQL 2000 enterprise manager but I get error 21002:[ SQL DMO] User 'user' already exists.
Kind Regards
Sunod
I restored a backup of a replication database named "distribution" to a non-replicated development instance (ss2000). Well, when I try to drop the database I am told I can not because it is used for replication. It isn't. But you now how Microsoft is . . . . So the question is: how do I drop the database without rebuilding the server, or jumping through firery hoops????
Thanks!
Scorched
I have a SQL 2k database backup and I restore it in sql server 2005.
Is this now a SQL Server 2005 database or a 2K database in a 2005 server.
Means do I need to convert databases somehow when I migrated from 2000 to 2005. Is the above mentioned way a way to migrate... or are ther some conversation tools.
Any help / links / ressources would be Great...
Felix
We have a log shipping database running on SQL 2000 ENT SP4, the secondary DB is in standby/read only mode . we have a job that runs every day to take it offline, make a copy of the files and attach them as a new database for reporting purposes.
We're planning to upgrade to SQL 2005 however, when we copy the Standby/read only database files and attach using "CREATE DAABASE FOR ATTACH" we get :
msg 1284
Cannot attach a database that was being restored.
this works in SQL 2000 but not SQL 2005, is there a way to force attach the DB ?
Thanks.
Hello,
View 10 Replies View RelatedHiI implement a manual standby by backing up Transaction log files everyhourand database nightly and restoring these on a standby server.Every thing works fine but the Transaction log restore fail with errorlistedI am sure the database backup finishes before the transaction logbackupsThanksTYThe log in this backup set terminates at LSN 22927000000763100001,which is too early to apply to the database. A more recent log backupthat includes LSN 22927000000773700001 can be restored. [SQLSTATE42000] (Error 4326). The step failed.
View 1 Replies View RelatedI need a SQL query which lists out directory path from which the database has been restored recently ( i.e backup path ).....
View 2 Replies View RelatedHi guys
I need to apply a sql script file on Sql server 2000 by .net 2.0 program. but on the current running machine, there is not Osql.exe file. Do you guys know how to execute the sql file without the command? Is there any way provided in .net library ?
Thanks for you response!
We are using log shipping and we would like to remove all transfered and applied journal (in the primary box).
We have the intentionto use a trigger like this :
CREATE TRIGGER del_log
ON log_shipping_plan_history
AFTER INSERT
as
declare
@lastfile nvarchar(256)
SELECT @lastfile=i.last_file
FROM log_shipping_plan_history e INNER JOIN inserted i ON e.sequence_id = i.sequence_id
where i.activity=1
begin
if IF (@lastfile <> NULL)
...
... remove file (using xp_cmd for example...)
...
end
but the problem is that we have only the last file transfered and applied that will be removed
(some time, more that 1 file are applied in one shot ...
see num_files column in log_shipping_plan_history).
Any solution to remove all the files generated before the last one given by the query ?
Any other solutions (sql wizard gives the possiblity to to remove file after a laps of time 1hour, 1day...).
I am looking for the table that contains all the journal files (that we can see when we try to retore a db) ?
Thanks
Hi... I'm reading the MS Press 70-442 Self-Paced Training kit, and I'm having problems with this example.
I'd like help getting it to work correctly, or understand why it is isn't working the way I planned.
On page 67, the lab is about the APPLY operator (CROSS APPLY and OUTER APPLY). I first have to input a sample table-valued function into the AdventureWorks database:
Code Block
CREATE FUNCTION fnGetAvgCost(@ProdID int)
RETURNS @RetTable TABLE (AvgCost money)
AS
BEGIN
WITH Product(stdcost)
AS
(
SELECT avg(standardcost) as AvgCost
FROM Production.ProductCostHistory
WHERE ProductID = @ProdID
)
INSERT INTO @RetTable
SELECT * FROM Product
RETURN
END
and then run a sample T-SQL statement
Code Block
SELECT p.Name, p.ProductNumber,
Convert(varchar, cost.AvgCost,1) AS 'Average Cost'
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) AS cost
WHERE cost.AvgCost IS NOT NULL
ORDER BY cost.AvgCost desc
My problem is with the WHERE clause... According to page 56, CROSS APPLY returns only rows from the outer table that produces a result set, so why do I need to explicitly filter NULL values?
When I remove the WHERE clause, the query retrieves lots of NULL AvgCost values.
Again, according to page 56, it is the OUTER APPLY that returns all rows that return a result set and will include NULL values in the columns that are returned from the table-valued function.
So, in short, I don't see the difference between CROSS APPLY and OUTER APPLY, using this example, when I remove the WHERE clause?
(Please refrain from introducing another example into this question.)
i have restored site using stsadm, site get successfully restored.
all user of the site able to access the site but the unable to access from same machine on which it is restored.
i got the error
The file exists. (Exception from HRESULT: 0x80070050)
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
I am interested in applying 7.0 log files to 2000 database, that has been previously restored from a 7.0 version database. Assuming that the original database is one in the same, is this possible?
View 1 Replies View Related
Here is the scenario:
- I have one production database and one test database.
- In the test database i have done a few changes on the structure - mostly adding new fields in the tables.
- Now i want to apply the table changes to my production database.
Does anyone have any suggestions on how i should do that?
I mean, of course i could just manually check every field and update the changes - but that could take a while -
i guess there are some better ways this could be done, like:
- backup data
- delete tables
- recreate tables using structure of test database
- reinsert data
Is this the correct way? Are there any easy ways to do this?
The changes i made should not make problems regarding data that is already stored (like changing a string field to int)
If I have a database backup from sunday, and a failure occurs monday... Can the backup .mdf and .ldf files be attached, and the backup log after the point of failure be applied to them?
The problem I am having is it looks like you can only restore from a .bak file, and then apply the log at the point of failure. IT doesn't look like you can restore the .ldf/.mdf files, and then apply the backup log from the point of failure.
Can someone please help? I'm in desparate need of fixing this !
Thanks,dp
I need to translate a user€™s regional setting into one of our own language codes before I send it through as a filter to the model query. If our language codes were the same, the filter would look like this in the report filter -
Language Code = GetUserCulture()
Which translates to this in the database query (for us english) -
table.language_code = 'EN-us'
And of course I need it to look like this -
table.language_code = 'ENG'
I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement). I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this
Language Code = dbo.ConvertFcnIWrote(GetUserCulture())
Or how I would access the custom assembly in the filter expression.
Do you have a recommended implementation for this situation?
Thanks,
Toni Fielder
yes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me
View 4 Replies View RelatedNoticed weird activity today with pull merge.
When your snapshot is set to be delivered via ftp and compressed in a cab file. If you add a new article to your publication and re-run the snapshot the agent will be unable to pull the snapshot down as it for some reason doesnt think its compressed. It is failing to find the scripts it needs inside the cab file despite the cab file existing in the correct location.
Here is the error.
2007-07-19 09:57:29.855 Snapshot files will be downloaded via ftp
2007-07-19 09:57:29.886 Connecting to ftp site 'SQL3'
2007-07-19 09:57:29.933 The schema script 'empActive_127.sch' could not be propagated to the subscriber.
2007-07-19 09:57:29.933 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'empActive_127.sch' could not be propagated to the subscriber.
2007-07-19 09:57:29.933 Category:AGENT
Source: SQL2SQL2005
Number: 20033
Message: The process could not retrieve file 'SQL3_CCUSA_ATLAS_SYSTEM TABLES/20070719055712/empActive_127.sch' from the FTP site 'SQL3'.
2007-07-19 09:57:29.949 CategoryS
Source:
Number: 12003
Message: 200 Type set to I.
200 PORT command successful.
550 SQL3_CCUSA_ATLAS_SYSTEM TABLES/20070719055712/empActive_127.sch: The system cannot find the file specified.
550 SQL3_CCUSA_ATLAS_SYSTEM TABLES/20070719055712/empActive_127.sch: The system cannot find
I know allot of folks are having this problem and I tried lots of things but nothing works. I understand the problem is coping the SQL Express on another server is the problem - I just not sure what to do?
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
This is the last statement on the Stack Trace:
SqlException (0x80131904): An attempt to attach an auto-named database for file e:wwwdata81d0493fwwwApp_DataTestDatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735091
I checked my server forum and they said I had to name a database:
Example: Database=(unique name);
But this didn't work either.
I just tried a simple web project that has only one database and one table in SQL Express with one sqldatasource and one datagrid. It works fine on my pc but when I use the copy function in Visio Studio 2005 Pro - I can't run the site on the remote server: www.myjewelrydirect.com
I tried coping the database manually. I tried disconnecting the database before I copy it. Below is my connection statement:
<connectionStrings>
<add name="TestDB" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|TestDatabase.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
With all the comments in these forums - this must be a bug.
I have been working on this problem for over 2 weeks - HELP!
Hi,
Can someone help me, I have this DB that needs to be restored. I have the full backup and the differential backup.
When I'm trying to restore my differential backup it prompts me this error "System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)".
Then I try to create a test DB and restore my full backup there, it was succesfully restored and now try to restore my differential backup since I need the newest backup I received that error. I have already used the option leave the database non-operational but still the same.
Thanks.
Russell