Hi,
Does a database restoration perform any
update-statistics/defragmentation by default. We observed a marked
improvement in performance when we restored the a database from an high
end machine to a low machine.
Could someone shed some light on this.
Regards,
Thyagarajan Delli.
Hello
I'm working on an application which is mainly used to look up compressors and their data which are held in documents.
This is a simplified representation of the tables of importance:
Document
--------
Id (int, identity) (PK)
Number (nvarchar(50))
Compressor
----------
DocumentId (int) (PK, FK)
Id (int, identity) (PK)
Name (nvarchar(50))
CompressorData
--------------
CompressorId (int) (PK, FK))
Id (int, identity) (PK)
Value (nvarchar(50))
The tables are linked as follows:
Document -> Compressor -> CompressorData
Non clustered indexes are created on Document.Number and Compresor.Name because these fields are used for querying.
At certain points corrections will be released on compressors which
will result in:
- Creating new documents with new document numbers (note that a non clusterd index exists on Document.Number).
- Copying affected compressors of existing documents into the new documents (note that a non clusterd index exists on Compressor.Name).
- Copying the data of the affected compressors into the new documents.
This can result in creating ten's of new documents and copying hundreds compressors and thousands compressor data records.
My question:
Will the users still be able to query for compressors while corrections are released (thinking about indexes which need to be modified) or will their be so many locks held that the database becomes unusable?
Thanks in advance,
Alain
i am restoring newdatabase(test) with the backup from other server.
The problem is the restoring process is hanging and its not restoring the
database .when i stopped and refreshed the databases the database is not showing up
could some body Give patch for this problem
Thanks
Prasad
pls tell me how to solve the database in use problem while restoring database using query.
View 2 Replies View RelatedHi,
I have a backup (.bak) of one 'AdventureWorks' database with me and need to restore it to the AdventureWorks database I have created in my SQL Server 2005 database. I go by the normal process --> restore from device and then select the path of the .bak file in my hard drive but it is giving the error "The backup set holds a backup of a database other than the existing database AdventureWorks".
Obviously I have got the backup file from a different source and would like to replicate the same database in my own machine.
Can anyone please help me find out why is this not working?
Regards.
Hi,its newbie question.I have one production server(A) ,one backup server(B),additionalserver(C).my database backup are moved from A to C in regular basis.My problem is that when ever I have to restore my database in BackupServer i.e B,I have to first copy that particular Backup File on to my Backup Serverfrom Cthen i have to perform my restoration process.Even if I mapped the drive in which my bakup file is lying ,I amnot able to see that drive in SQL Entriprise Manager and when i amrestoring through Query Analyzerit shows the following error -Server: Msg 3201, Level 16, State 2, Line 1Cannot open backup device 'G:pharmacypharmacy_db_200505210330.BAK'.Device error or device off-line. See the SQL Server error log for moredetails.Server: Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.What i think is it a waste a time ,first copy the bak file in localdrive and perform the restoration process.SO please can any one guide me how should i do the network restorationif possible or put me to right path for doing it so.I am using SQL SERVER 2000,OS WINDOWS 2000 Adv SERVER.cheersT.V
View 3 Replies View Related Hi Experts,
I am setting up new production box. I have 2 separate boxes with SQL server 2000 Standard Edition with sp4.
As SQL server standard edition don't have logshipping in it, I created manual jobs to ship logs every 10 mins and Full Backup once in a day from Server A to Server B.
Database on server A have multiple filegroups. When I try restoring database backup on Server B which already has the database on standby, I get an error.
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'I:sqldatapsi_dbPSI_DB_TESTDATA.mdf' may be incorrect.
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'I:sqldatapsi_dbPSI_DB_ITEMDATA.mdf' may be incorrect.
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'I:sqldatapsi_dbPSI_DB_OTHERDATA.mdf' may be incorrect.
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'I:sqldatapsi_dbPSI_DB_ANALYSISDATA.mdf' may be incorrect.
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Server: Msg 5105, Level 16, State 9, Line 1
.
.
.
.
.
.
.
This error continues for all the files I created on server A
But the path I:sqldatapsi_db exists.
Restoration works fine when I don€™t have any database on Server B.
I am using the "replace" keyword while doing the restoration
Please help, this is little urgent.
Thanks in advance.
Regards
Sachin
When I tried to restore backup of publication database, time taken for restoration is too long [ 4 hours]. The actual database restore takes only 20 minutes but the stored procedures used for cleanup replication takes the remaining time. Is the issue related to my environment or the cleanup replication stored procedure?
View 7 Replies View RelatedIs anyone here aware of anything else that has been changed in regard to SQLCLR via sp1 (excluding this http://support.microsoft.com/kb/910414/)?
View 1 Replies View RelatedWe take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
View 5 Replies View RelatedI need to know if it is possible, and if so, the effects of restoring databases from a server running SQL Enterprise Edition to a server running SQL Standard Edition. There will be an application database as well as the "master" database to be restored.
Any suggestions?
If I encrypt the SQL Procedures in my database will that cause any sideeffects?Will there be performance degradation?Is it good to encrypt them or they can easily be unencrypted?Thank you
View 3 Replies View RelatedIn SQL7, if you add a column to a table, is it necessary to recompile all
stored procs that reference that table?
Under 6.5, this caused problems at our site unless the Procs were 'refreshed'.
Is this only necessary where select * is used?
Thanks
hi, if I install sql server 7.0 and created a user database named myDB that has some data. and I make a back up as a complete database.. Then I uninstall sql server,yet I am still having the backup in another drive. Then I install sql server 7.0. Can I use the restore method to restore myDB with no problems.
Ahmed
I like this forum but recently I have noticed they are running ads with sound effects.
I like a lot of programmers I know listen to music through their computer with headphones on all day. Whenever one of those adds fire off it about scares the BeJesus of me and comes through louder than the music. I am playing with my settings but I each time I find one that kills the sound effects it kills the music too.
Starting to make me mad.
Hi,
I was wondering if one could manage the color of the data series in line and bar charts much like what you could do in Excel?
Even changing the default order of the colors would be nice, just so different charts actually look...well, different.
Thanks!
Please excuse my lack of understanding as I am not a SQL DBA.
I have just received a database backup: "myDB_20005034400.bak" and have been
asked to put a copy on my server. I am told that this was made from the Enterprise Manager's DB Maintenance Utility. The backup Administrator has left the company with the server in a crashed condition.
How can I find out what "myDB_20005034400.bak" pertains to? (No msdb, etc. available). I am RESTORING TO A NEW SERVER.
MAIN: HOW DO I RESTORE THIS FILE TO A NEW DATABASE; ie TestDB (TestDB.mdf;
TestDB.ldf)?
Thanks a trillion.
Shelly
Hi,
Please give the retoration procedure:
I have a production server and I want test it on the other machine to check whether my backups are working or not?(Please give me step by step so that I can do easily)
Your answer will be highly appreciated.
Thanks,
dindu
Hi !
when i am trying to restore MSDB database from a disk backup it is giving an error saying
"Database in use.System administrator must have exclusive use of the databse to run the restore operation".
But i am not using msdb anywhere.when i see the processes under current activities i see an application named sqlagent-alert engine using the database.even after killing this process it reppaears and stops the restore operation.
so i stopped sql server agent and did the restore operation.It worked.
so is it necessary to stop sql server agent while restoring msdb?may i know the logical reason for it?
Hi Gurus,
While one of my DB through job i got following error message.
Failed to uncompressing data block. RESTORE DATABASE is terminating abnormally. Read on "VDI_2A04E518-0B70-41F8-BA22-48A63F9AC65C_0" failed: 1223(The operation was canceled by the user.) [SQLSTATE HY000] (Error 52008). The step failed.
Can someone help in this.
Thanks,
ServerTeam
Hi Gurus,
Every time when i want to restore database Iam going with SqlLight restoration scripts.
Here iam unable to get estimation time while restoration time.
How can i get time to complete restoration when database is in middle of restoration.
Please advice...
Thanks,
ServerTeam
VijayaKumar writes "We have SQL Server 7.0 & that machine was crahesd out. We took the .ndf & .ldf files and restore the DB in onther machine.
In that machine I am able to Login but during the time of retrival (select query or stored procedure) it gives the following error....
Server: Msg 823, Level 24, State 1, Line 1
I/O error (bad page ID) detected during read of BUF pointer = 0x1174f100, page ptr = 0x217f8000, pageid = (0x1:0x55b1), dbid = 12, status = 0x801, file = E:MYSTS_CWS_DB_PRIMARY.ndf.
Connection Broken"
Problem in restoring new database with the backup file stored in tape drive
tape doesnnot contain sql server backup set
Hello all,
I operate a website which runs on Server 2003 and SQL 2005 Express. Unfortunately my server experienced a hard drive failure, however fortunately I have a full backup using 2003's NT Backup.
My new server is slightly different specification wise to my last server - but my question is, what are my options for restoring the database and how would I go about this? I do administor 2003 servers, but I'm not a database administrator.
My initial thoughts are to update Windows and its components (such as IE) and SQL 2005 to the same equivalent versions, and to perform a full restore, but what if it breaks Windows?
My second thought is to somehow extract the database from my backup file, but then I wouldn't know where to start how to re-import everything.
All comments would be welcome! Many thanks, Mike
Hi all,
Please find the following details and give me appropriate solution.
Full backup: everyday :00:30 hrs
Differential backup: everyday 20:00 hrs
Transction log backup: every 30 mins
Database size is 150+ GB
Lastday one of our disk got crashed which had contains data and log file of the database at 22:25. Now my client wants all of the data till 22:25.
I can recover data till 22:00 but how can I recover last 25 minutes data.
Please help ASAP
I am working on a site redesign which attaches to an MS SQL backend. The hosting company sent me a backup copy of the DB. I am trying to restore the db into my SQL Server but keep getting an error. I created an empty DB and then backed up that DB. I then replaced the .bak from my db with the .bak file he sent to me in a zip document. I then tried to do a DB restore through enterprise manager and recieved this error:
Device activation error. The physical file name 'd:sqldatabasemssqldatastklaweb_db_data.mdf' may be incorrect. File 'stklaweb_db_data' cannot be restored to 'd:sqldatabasemssqldatastklaweb_db_data.mdf'. Use With Move to identify a valid location for the file. Device activation err. The physical file name 'd:sqldatabasemssqldatastklaweb_db_data.ldf' may be incorrect. File 'stklaweb_db_data' cannot be restored to 'd:sqldatabasemssqldatastklaweb_db_data.ldf'. Use With Move to identify a valid location for the file. RESTORE DATABASE is terminating abnormally.
I need to get this db restored asap. Any suggestions/help would be GREATLY appriciated!!!
Jason
SQL Server 7 or 2000.
I restore a database on a different server from a device file. This database has a user defined for it. In this case, 'privuser' is the name. No login has been defined.
I can add the privuser login but cannot grant db access because privuser already exists in the db. I cannot drop 'privuser' because the user owns all the user objects in the db.
How can I make the privuser login I create access the db using the privuser username in the db? Would having the login created before restoring the db as a new db work?
Hi Folks:
This seems like a pretty straightforward question, but since I've been trying to find this in BOL and not having any luck, I thought I'd ask some folks more experienced than I.
How much room do I need on a SQL box to restore a 6.45 gig database? (it's
going from a huge machine to a backup tape, then from the backup tape to a
much smaller machine for testing an app, and we're trying to find out what we need to do to make it all work out.)
I'm assuming that I would need significantly more than the 6 gigs of the db for the work of actually doing the restore, but can't find out how much more...
Any ideas would be most appreciated.
Many thanks in advance,
Tom
Hi,
This is the problem which we face now and then. There is an automated job that run everyday night to restore the Database for reporting purposes. Before running that job an NT Task Schedular stop starts the sql server. This is to make sure that all the connections are closed. Nobody uses it at Night. But some times the job fails with bellow error message...
"Database in use. The system administrator must have exclusive use of the database to run the restore operation. [SQLSTATE 42000] (Error 3101) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated statement is not prepared [SQLSTATE HY007] (Error 0). The step failed."
Should I make the DB option set to single user mode. I don't think that is the problem.. because it runs fine most of the time..but sometime it fails.
Please help.. Thanks in advance :-)
Shyam.
Hello friends,
Kindly guide, if you are aware of any mechanism for restoring the differential backup only.
This can save a lot of time other than restoring complete database.
Thanks in advance.
Shrirang
It appears that in SQL 2000 restoring a single table is a bit more tedious and less user friendly as in earlier versions (6.5). Can anyone explain in a nutshell the easiest/quickest way to restore a single table from a backup?
Thanks,
Christine
Hi,
I am working on a .NET application (VB.NET). We had to restore our SQL server database from yesterday's backup as we accidentally overwrote some tables. After restorign, we are able to log to the database alright from SQL Query analyzer and Enterprise manager but when we try to access it from the .NET application, it gives an error message "Cannot open database requested in login '****' login fails.
Login failed for user '****'". What could be the reason and what would be the solution?
Thanks in advance
Hi,When i use to work on Windows 2000 advance server and Sql 2000I was able to do network restoration by using a mapped drive where mybackup use to be lying.What i did was I assigned administrator rightsto my login and in Services (mssqlserver)i added my login.Then I was able to access any mapped drive from my network in Sqlserver 2000.Now i have switched my OS to Windows Server 2003.What I want to know is , is their any other setting that has to donefor network restoration , because after following above steps then to Iam not able to access mapped drive in SQL SERVER 2000.So can anyone help me to know what setting should i do in Windows 2003server or in SQL Server 2000 to do a network restoration of database.Thanks in advanceTV
View 1 Replies View Related