I had a peculiar problem yesterday with SQLserver2000 database restore from one server to the other. I copied the .BAK file ( 11.2 GB) from source server to target server. Target server already had the same name database. I use SEM to do restore the database using the disk file .BAK as source to restore. The NT drive space was 13.5 GB available after copying. Restore failed with an error that not enough disk space is available to restore and it needs 23 GB of space for expansion. What does it mean? The database went into "Loading" state and it got corrupted. I tried to drop the database and cleared the DBF and LDF files on the drive and again tried to restore from .BAK file but it failed again with the same error message that not enough space is available for expansion. Now I do not have the original database or am not able to build the new database.
Finally I did attach/detach methods where I could bring back the database in running state.
What is it complaining about not enough space? Why did it ask for almost 100% more space for some expansion in restore process?
Why it did not fail when doing attach/detach methods from other server.
The company for which I work did not have a DBA until I started a few weeks ago. Whoever installed SQL2K used the wrong CD so they have been running Personal Edition on their servers. I have installed a new SQL2K standard instance and have restored everything except the jobs and DTS packages. Can the msdb from the Personal edition be restored to the standard instance?
Is it possible to downgrade SQL from Enterprise to Standard Edition, or do you have to remove the previous installation (uninstall) and reinstall. Meaning you would also have to restore all user databases? Thanks.
I just upgraded to SQl Server 2000 from 7, and realized that all my reports created with existing scripts no longer work fine. The reports appear corrupt. Below is an example of a report script:
declare @vSubject varchar(30), @vMessage varchar(20) Begin
One of the forum user suggests that I need to post the following issue to this area relating on data corruption.
Here's where I am up to in detail: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74599
Summary: Basically, I made a daily automatic testing restore to TARGETSERVER and every database are ok except the master database. I do DBCC CHECKDB on master database in the SOURCESERVER .. and no error what so ever. But the result of restoring of the master backup does. This happens everday.
Let me start off by saying that under normal circumstance I would just restore from the last good backup. However in this case it appears as though the last good backup was sometime last August ... arg! After much yelling at the person responsible I've been attempting to get my blood pressure below 200 and see what data is recoverable.
First off, this was a RAID5 system that failed 1 drive. Secondly, before we got someone in there to replace said drive it failed a second drive and the system went down. We managed to massage the system back online but it appears that there is some corruption as a result which is no surprise.
I've done DB repairs in the past and it hasn't been too bad, but this time it is looking a little gnarly.
I've kicked everyone off the server and tried starting SQLServer several different ways.
I tried starting the service normally and then flagging the bad DB into single user mode with "ALTER DATABASE foo SET SINGLE_USER". I then did a select * from sysdatabases to make sure it took, which it did. I also tried starting the whole SQLServer in single user mode from the command line, "SQLServr -m".
I can run "DBCC CHECKDB('foo')" and I get a long ugly list of allocation errors. I posted it to a link as the 1349 lines returned is a little long: http://chrisnet.net/sqlbad/dbcc_checkdb.txt
But when I attempt to bite the bullet and destroy data in an attempt to put things back together with: "DBCC CHECKDB('foo', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS" I get: Server: Msg 7919, Level 16, State 2, Line 1 Repair statement not processed. Database needs to be in single user mode. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
But yet the database is in single user mode, according to everything I check check on. Is this just SQL's way of telling me the corruption is too severe to be repaired? No output is displayed in the shell cmd window like it is for a successful DBCC either (when running sqlservr -m).
Greetings all,This should be an easy task, and Im sure it is, but as many times as I havetried, I cant seem to get this to work properly.We changed ISPs recently from a shared host to a co-located server, and ourformer host was nice enough to send us a backup of our old SQL2000 database(about 5MB).I went into Enterprise Manager, created an empty database with the same nameand used the Restore Backup tool successfully. I took a look at the db andsaw all the Tables and data intact.Next I wanted to re-create the DSN to match the old one so that I wouldnthave to fiddle with any of the old connection strings in my asp pages.Thats where the fun began. The old DSN was created by our host via an emailrequest and I never got a look at the actual creation process of that DSN.I created one on our colo server with the same name, but it would only passthe connection tests when I used the Windows User Authentication rather thanSQL Server Authentication. No big deal I figured, and just went ahead andset it up using the same DSN name.Next I tried the main.asp page to test the DSN and lo and behold I got theODBC connection errors. I tinkered with the connection strings a bit andmanaged to get a wide variety of connection errors and fine-tuned to thepoint that it said "Unable to login with user 'SERVER169/nacog'". At thispoint, I went into Enterprise manager and added 'nacog' to the User list andthe connection string no longer produced errors. (By the way, my connectionstring simply contains "DSN=YAVAPAICONNECT;")My next step was to actually execute a SELECT statement which produced thefollowing error:Microsoft OLE DB Provider for ODBC Drivers error '80040e09'[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied onobject 'ADMINS', database 'YAVAPAICONNECT', owner 'dbo'./nacog/admin/main.asp, line 189Why was I not surprised?I went back to Enterpise Manager, saw the all the tables had 'dbo' as theowner, and tried to give 'nacog' all the permissions at the table level, butthe error persists.To preserve my sanity I stopped there, because I spun my wheels for hoursand days the last time this happened. and my eye has not stopped twitchingsince ;)I am quite sure that this has something to do with one or all of thefollowing:1) The way SQL2K was installed (it was installed by someone else)2) My creation of the db with the windows login, rather than SQL auth, priorto import3) My creation and handling of the DSNMy background is mainly in ASP programming, and I understand bits and piecesof this puzzle, but for the life of me I cannot piece this thing together.Can anyone help point me in the right direction or suggest a good tutorial?I would be very thankful to anyone who could help put me on the right track.Best Regards,Ben M.
I have seen this before. A 2000 restore fails, leaving the database thinking it is being restored but the restore job failed and errors when it is restarted. EM is clueless. I believe there is a proc to reset some flag. Can you share it with me???
Trying to keep out sysadmins & sa during/between database RESTORE
Configuration: WINNT Server Enterprise 4.0 w/SP5 SQL Server 7 Enterprise & SP1
2 SQL Servers: Production Server Standby server
I Backup (full backup) databases to disk on primary server (logical backup devices are physicaly located on a Standby server (dedicated gigabit NIC in each server for this process). Transaction logs are applied to the Standby server throughout the day.
Problem: How to keep out "sa" and sysadmins from a database while I'm restoring (or between restores) to a standby server? The database being restored cannot be in use during a restore. If a DBA forgets that this process is happening, the statement fails (RESTORE)for the database they happen to be in at the time of the restore.
Example restore statement: Standby Server - RESTORE DATABASE databasename FROM database_dd WITH DBO_ONLY, REPLACE, STANDBY = 'g:Mssql7FromPrimaryDatabaseName_undo.ldf'
I could restrict Domain sysadmin access and change sa password. I could also put the database in "Single user" mode, however this could become problem if my process disconnects and then someone else connects - then my process is locked out. What I'm really looking for is to lock out all activity for a database that is in "standby mode" except for RESTORE processes.
it is taken from SQL2K5 SP2 readme.txt. Anyone have idea what to do to implement this ? Our sp2 is failing. we suspect the above problem and researching it.we are running on default instance of SQL2K5 on win2003 ent sp2
"When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:
Explicitly denying the ALTER DATABASE statement.
A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.
If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP2, you must disable these restrictions and then re-run Setup."
I'm taking a database(read-only) backup from one server and restoring it on other server. As soon as restore is done it is bringing database into single-user read-only mode.
why it is bringing the database into single user mode ?
Hello,i am in great trouble. I want to revert back to original state ofdatabase before i performed restore database on my sql server 2KDatabase. Accidently i didn't take backup of my Database and i didrestore, so is there any way to get the original state back of myDatabase?Any suggestion will be highly appriciated.Regards,S. Domadia.
In a development server you take a backup and you want to move the entire database to a production server. The production server does not contain the database, users, or logins in master.
When you restore the backup to production in 7.0, the users get moved to DBO because the logins do not exist and then you cannot delete them.
We are having to drop the users from the database on development, back up the database and restore it to production, then recreate the users on production.
This is bogus and did not happen in 6.5 because of the aliases.
I am fairly new to VS 2005 and SQL Server CE. I have developed a Desktop Windows application using VB 2005 and the SQL Compact Edition Database. This application will sell to users via web download.
When they download a service patch, or updated version, I wish for them to retain the data in their present database. In other words, fill the new database with the old data. The new table structures will remain intact except for added columns at the end.
Question is how to save the old data and update the new database with it. Is there an easy way to do this, or do I need to write a module to save a database copy, and update the new database with content at install time?
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:DATAMYTEST.DAT_BAK' WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF', MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF', REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Sometime during the night last night some user account permissions were "lost". Am I right to think that restoring the master database would be the way to go? We have a 2 node 2012 cluster and I stop the cluster resource and start the db in single user mode from the active node. Somehow the sharepoint farm is still trying to connect so I can't get logged in single user. What method could I use to stop users from connecting when I don't have access to the sharepoint farm.
i have a development database that has updates and changes to a production database. rather than go through individually and alter all relevant tables and stored procedures, id like to back up the database on the development side and restore it on the production side as the production database. is there a way to restore the database on the production server but preserve all the security settings (ie logins and such)? i noticed on our development server, that if i try to restore the database with my development database, it overwrites the users and/or if the user is the same on both, it removes the login name for that user.
I was wondering how I could restore a bkp file into a database with a different name. For example, I made a backup of T1.mdf and i want to restore t1.bkp to T2.mdf.
I 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?
I had a major disk failure, and the only SQL Server files I was able to recover were master.dat, msdb.dat,msdblog.dat, two text files and two database devices. My question, is it possible in SQL 6.5 to restore a database from just the database device, and if so, how? Thank you.
I'm a newbie so please be gentle.In attempting to run a restore I get the following error message:"Exclusive access could not be obtained because the database is inuse." However, it doesn't appear that there are any connections to thedatabase. At the suggestion of another thread, I ran sp_who2 and thereare no connections to the database in question. I've been running thesame restore for months and all has been well, until yesterday....I wrote an .asp page (below) to test connectivity to this database.The vb code ran successfully and soon thereafter, my nightly restorefailed. Task manager does no show that the asp page is active.Lastly, I did add a user ("jbtest") as well as change the option to useeither Windows authentication or SQL Server authentication inEnterprise Manager.Any ideas would be greatly appreciated.Thanks.<%@ Language=VBScript %><%companyName = "agemni"Set cnn = Server.CreateObject("ADODB.Connection")cnn.ConnectionString = "DRIVER=SQLServer;SERVER=SHEELA-NA-GIG;UID=jbtest;PWD=test1;APP=MicrosoftDevelopment Environment;WSID=SHEELA-NA-GIG;DATABASE=" & companyName &";Trusted_Connection=No"cnn.ConnectionTimeout = 300cnn.CommandTimeout = 300cnn.OpenSet RS = Server.CreateObject("ADODB.Recordset")strSQL = "SELECT * FROM Preferences"RS.Open strSQL, cnnResponse.Write "connection established with the <b>" & RS("CompanyName") & "</b> database. They are a <b>" & RS("street") & "</b>customer. "RS.CloseSet RS = Nothing%>
I have a backup of an SQL Server database named "UserData.bak" now i want Restore this backup to my SQL Server.pls tell me whole process.Can i also restore it to MS-Access, if possible pls do let me know. Thanks
I want to add a new table to a database on a server. I assume the best way to do this is to backup db on sever, restore to local computer, add table, backup local, and restore to server. (If there is a better way, I am open). I can backup the database on the server and get the backup to my local computer. However, when I use SQL Server Management Studio Express to restore the .bak file to my computer, I get the following error: TITLE: Microsoft SQL Server Management Studio Express------------------------------ Restore failed for Server '\.pipe282F14E7-8852-4B sqlquery'. (Microsoft.SqlServer.Express.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 ------------------------------ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: RESTORE cannot process database 'C:USERSDEBORAHDOCUMENTSMY WEB SITESMHRRCSK3BETA1APP_DATAMHRRCDB.MDF' because it is in use by this session. It is recommended that the master database be used when performing this operation. (Microsoft.SqlServer.Express.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476 ------------------------------BUTTONS: OK------------------------------ So, I am not sure what to do next ... I have followed the links above to see there is no information. Any ideas? Thanks for helping this newbie.
The data in one of the tables in my datables got deleted. I want to restore my database to an earlier time, but don't know how. I'm not sure if I have any backups, I wasn't making any myself. All I have is the ldf file, but I don't know how to open it or what it contains.
On a test server I had deleted the .MDF to testdb, before deleting the .mdf I had backup the database inserted some records into a table and then backup the transaction log. I brought the server down and back up then did the following:
use master go
BACKUP LOG test TO TestRestore_Log2_Backup WITH NO_TRUNCATE GO RESTORE DATABASE test FROM Test_Backup WITH NORECOVERY GO RESTORE LOG test FROM TestRestore_Log1_Backup WITH NORECOVERY GO RESTORE LOG test FROM TestRestore_Log2_Backup WITH NORECOVERY
I receive all kinds of errors like: Primary file not available for database 'test' ( i'm sure that's because the .mdf is gone),One or more devices or files already exist use REPLACE, Which is fine but when I do I only get the full back and not the latest transaction log?
I was restoring a database from a backup I had made about a month ago and it gave me an error. I don't remember what it said exactly, but I know that it had to do w/ restoring the transaction log. Now, I can't access the database at all. When I try to access it through enterprise manager, it says "An error occurred while trying to access the database information." If I try through Query Analyzer, it says that the database is in the middle of a restore. I checked the sysprocesses table and there is nothing running against this database.
Is there anyway to recover this database, or am I going to have to recreate it from scratch. Please tell me I can recover the database!!!!
I have 2 servers SQl server 7 and 2000. I got a backup file from the customer for one of the database. So I created a new database using similar script as below create database TESTUP ON (NAME = TESTUPDATA, FILENAME='C:PROGRAM FILESMSSQL7DATATESTUPDATA.dat', SIZE=10GB) LOG ON (NAME = TESTUPLOG, FILENAME='C:PROGRAM FILESMSSQL7DATATESTUPLOG.dat', MAXSIZE=500MB) GO
sp_addlogin TESTUP, TESTUP, TESTUP GO
USE TESTUP GO
sp_adduser TESTUP GO
When I use enterprise manager to force restore that backup copy to this database I just created, even though I have setup maxsize for LOG as 500 MB it still gets all their logs 50 GB !!! I do not want their logs in the 1st place and then I tried to restrict them I could not do so. So I restored it as it was urgent and then tried to shrink the datafiles for LOGS but they did not shrunk much.
Is their any way to avoid getting the transaction logs during the restore ??? In other words can just get the datafiles and not the transaction logs ?
I have a database that was not backed up and needs to be restored after an os crash. SQL was stopped gracefully and the server was rebooted, the os crashed and was reloaded, now sql is installed and I need to know if there is a way to restore the .mdf and .ldf files to the sql server.
Hi, I need to restore a database from backup which is stored in the shared folder in the network neighbourhood. SO I created a temp folder in my E drive and copied the file from that location to the temp folder and when I tried to restore it, I get the following message: "the file F:mssqldataFindatafindb_pri.mdf" cannot be used by restore. consider using the with move option to identify a valid location for the file.
So I created the database in my e:mssqldata folder and tried to restore and I got this message: "the backup set holds a backup of a databse other than the existing "findb" database.Backup or restore operation termination abnormally.
Hello I did all the back up. Now i have problems to restore database. How do i restore database into a different server? Anyone can help me... Thanks in advance
