Hi,
I've got a question regarding attaching/restoring of DBs and wonder if anyone could help me out.
Does SQL Server 2000 provide a functionality to attach/restore DB automatically? ie, some kind of polling service to attach/restore DB that were detached previously?
Thanks
Danny.
I'm trying to see the differences between Backup/Restore and Attach/Detach. I backup and detach a database from SQL Server 2000 SP3 and then attach and restore it to SQL Server 2005 SP1.
The differences I noticed are:
1. The restored database has a much larger initial size (database size is same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is showing owned by the database owner of the database restored over but syslogins and sysusers do not match.
I don't understand why #1 happens.
Are there any other differences between Backup/Restore and Attach/Detach?
Thanks,
Peter
Hello there,
I've some really big problems with SQL Server 2005 Express. I Recently, I had 2 instances on my machine, one was a SQL Server 2000 Developer and the other a 2005 Express Version. The 2000 version was not necessary anymore, so I unstalled it, since then, the Express version keeps having probems.
Under the Server Properties -> Database settings -> Database Locations I've changed the path to D:sqldatamssql, but now, the programm always takes the "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" to store and load the databases, no matter what I do.
And there are more problems see here:
Restoring does not work=======================
I wanted to restore a database called "fw40_admin" from a backup file (.bak), but that didn't work at all, it always says this as an error:
"System.Data.SqlClient.SqlError: The operating system returned the error '5(error not found)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:sqldatamssqlfw40_admin.mdf'. (Microsoft.SqlServer.Express.Smo)"
But as soon as I type in the installation path "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" inside the "Restore As" under "Options", it works.
Why does the backup only work in "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData", BUT the database locations under the server properties is indicated with "D:sqldatamssql"????
Attaching does not work=======================
Attaching an existing database does not work either. Most databases (mdf) are located on the path D:sqldatamssql, but as soon as I press
Attach Databases -> Add
The dialog window appears with the "D:sqldatamssql" path, but it is unable to find any *.mdfs altough THERE ARE .mdfs in this directory. But Express can find databases in the selected installation: "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData".
Why can't Express see the directory on the D: partition???
What is wrong? Here? Can anybody help me?
Dear All,
We have a DB on sql server 2000 64 bit and the build is 2187.
The database consisted of two transaction log files and several data files at the time we took backup. However, the backup taken was a cold backup (backup of files through OS). In this backupset, one transaction log file was skipped due to some error...... Now I want to restore the database for referring to some history data. Will I be able to attach the database with all MDF and one of the transaction log files.??
I heard this kind of recovery is not possible. If anybody can post some solution , I would be grateful...!!
Regards,
Chetan
Hi,
My last post was terminated without the error message. To recap, I am unable to re-attach a database to a SQL server 2000 server after I detached it and attached it to a 2005 Server. When I detached it from the 2005 server and tried to re-attach it to the 2000 server I got the following error message:
" Error 5123: CREATE FILE encountered operating system error 5(Access denied) while attempting to create the physical file ..."
I tried using both the 2005 Server Management Studio and the 2000 Enterprise manager to do the re-attachement but got the same error message.
Any ideas on how I can reattach the files?
Thanks for your help.
Ken McLean
and how can it be done?
View 1 Replies View RelatedHi,I want to restore my databases from the old SQL Server installationinto a new installation of SQL Server. My new installation of SQLServer has different data path from the old installation. In addition,the data owners of some databases in the old SQL Server installationare not dbo. I have backups for all the databases in the oldinstallation SQL Server.Can anyone tell me how to restore my databases from the old SQL Serverinstallation into a new installation of SQL Server for my case? (Iwant to use new path for data and log files as well as keep the olddata owners in the restored databases.) Do I need to perform restoreoperation against my master database in the new installation of SQLServer? (The data path for a newly created database has been changedin the new installation of SQL Server.)My idea is:1. Create new databases whose names are the same with the databases ofthe old SQL installation. Set the new databases with the new data pathI want.2. Create new login accounts whose user names and passwords are thesame with the old login accounts. Assign appropriate permissions tothe new login accounts (e.g, the data owner for some database).3. Use Enterprise Manager to perform restore operation against thenewly created databases using the backups from the old SQL Serverinstallation.Please advice.Hai-Chu
View 1 Replies View RelatedHello, everyone:
I want to move a database instance (about 50 user databases) to another server, both are running SQL Server 2000. Which method is better, retore from backup files or detach/attach? Some papers said restore amybe cause incorrect login and password transfer. Is it true?
Thanks
ZYT
I am currently adding 250 databases to a SQL Server (MS SQL Server 2008) using SQL Server Management Studio. It took all day yesterday, and I am sure there has to be another way to do it. The filenames are quite complex and it is not easy tracking which ones I have added. Is there a wee loop or something I can use to add all .mdf and .ldf files in a folder to the server?
View 4 Replies View RelatedHello all,
We are planning to change the location of the log files from my production server. We have setup the mirroring also for those databases. I know for the databases without mirroring job i could detach the database, copy the log file to the target location and attach the database with the new logfile location. But for the mirroring databases I don't have any idea how to do that. Could some body guide me thorugh the process on how to do this task? thank you very much for all your help!
I have a new problem with doing a restore of a number of databases using powershell. The script I'm using is based mainly on this one (Part 2 in particular): [URL] .....
The problem I'm having is around the RedgateGetDatabaseName function. My hunch is that its down to the different version of red gate and how sqlbackup works. Basically when the call is made to the function it is returning both the Database Name and the number of row's that the SQL command in the function has ran. I've tried to include a SET NOCOUNT ON at the start of the SQL command in the function but its still returning the now count.
Hello,
I would like to restore SQL Server Express and its databases from a tape backup to the same server. This is a disaster recovery senario.
I backed up the Master, Model, MSDB and my own test database using SQLCMD scripts. I have no problem restoring these using task manager on the server before the disaster recovery.
However, in my real disaster recovery testing, When the server is restored by tape drive (HP one button disaster recovery), I try to run my SQLCMD restore scripts in task manager and I cannot connect to the sql server. Also I cannot connect with Management studio. I have recieved the following error in event viewer.
----------------------------------------------------------------
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: (2)
Event ID: 3411
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.
Data:
0000: 53 0d 00 00 15 00 00 00 S.......
0008: 16 00 00 00 43 00 4f 00 ....C.O.
0010: 50 00 4c 00 45 00 59 00 P.L.E.Y.
0018: 4e 00 45 00 57 00 53 00 N.E.W.S.
0020: 5c 00 53 00 51 00 4c 00 .S.Q.L.
0028: 45 00 58 00 50 00 52 00 E.X.P.R.
0030: 45 00 53 00 53 00 00 00 E.S.S...
0038: 00 00 00 00 ....
Event Type: Warning
Event Source: SQLBrowser
Event Category: None
Event ID: 3
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The configuration of the AdminConnectionTCP protocol in the SQL instance SQLEXPRESS is not valid.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The SQL Server (SQLEXPRESS) service terminated with service-specific error 3411.
C:Program FilesMicrosoft SQL Server90ToolsBinn>sqlcmd -S.SQLExpr
COPLEYNEWSDATABASEscriptsMASTERFULLRESTORE.sql"
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred whi
shing a connection to the server. When connecting to SQL Server 2005,
re may be caused by the fact that under the default settings SQL Serve
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
----------------------------------------------------------------------
My question is, what is the correct procedure to follow when I want to do a disaster recovery and restore SQL Server Express from tape backup using the Simple Backup method and scripts.
Is it always required to reinstall sql server express from the original program file or is it possible to reinstall from back up tape.
I know my backup and restore scripts work because I tested them on the server before I do the disaster recovery and rebuild that server from tape.
This is some kind of issue with SQL Server Express being restored by tape backup.
Any suggestions, thanks.
Hi everyone,
Hope somebody can help me on this.
I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:
"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"
There are NO users using the application. These are DBs for the Portfolio Server 2007 application.
After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.
Can they be deleted? if so, would there be any serious consequences?
Thank you for your help on this.
Oscar E.
First, let me say that I have already reviewed information posted by experts within the chat area.
Someone completely deleted my SQL Server 7 database. I retrieved the .LDF and .MDF files from a network backup. Now I am trying to attach the database. I have tried this without creating an instance of the database by using the attach stored procedure to attache the .LDF and .MDF files I retrieved from the network backup. Also, I have tried it by creating an instance of the database and doing a detach of the newly created db .LDF and .MDF files and an attach of the .LDF and .MDF files I retrieved from the network backup. Neither of these approaches have worked.
Here is what I have tried in the Query Analyzer, but to no avail:
For the following example, I created a database instance called 'qarun_diamond_48_brett' with brand new .ldf and .mdf files and then tried to detach and then attach the .ldf and .mdf files from the network backup:
use master
go
sp_detach_db
'qarun_diamond_48_brett', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett_log.ldf'
Message: Usage: sp_detachdb <dbname>, [TRUE|FALSE]
use master
go
sp_detach_db
'qarun_diamond_48_brett', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett.mdf'
Message: Usage: sp_detachdb <dbname>, [TRUE|FALSE]
I tried the following attaches of the retrieved/recoverd .ldf and .mdf from the network to the newly created db instance. That didn't work, so I tried attaching to a db that had not yet been created.
use master
go
sp_attach_db
'qarun_diamond_48_brett2', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett2_log.ldf'
Message: Server: Msg 1801, Level 16, State 3, Line 1
Database 'qarun_diamond_48_brett' already exists.
If I use a different db name I get the following error:
Server: Msg 5105, Level 16, State 13, Line 1
Device activation error. The physical file name 'qarun_diamond_48_brett' may be incorrect.
use master
go
sp_attach_db
'qarun_diamond_48_brett2', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett2.mdf'
Message: Server: Msg 1801, Level 16, State 3, Line 1
Database 'qarun_diamond_48_brett' already exists.
I have also tried these statements wtih the EXEC sp_attach_db and EXEC sp_detach_db commands from within the Master db in Query Analyzer.
Any help would greatly be appreciated.
Thanks,
-Brett
Database Restore & Attach Error
I have a problem with restoring or attaching DB files from different version of the engine. I Have a existing SQL server 7.x and another one is SQL 2000 but I try to backup my database and restore it into the 2000 engine This is the following Problem which occurs :-
1st Problem : During Restoration, The restoration process was runing fine until it reach the last inficatior tab and it fail and say Database terminated abnormal. I been try to restore into other machine also but it give me the same problem.
2nd Problem : Say I can't restore but I stop the engine and manual copy the *.mdf and *.ldf to another machine. I try to restore it but it "is on a network device not supported for database files." So what shall I Do with it.
Pls Advise If anything need to be done before I do the restoring and anything need to be done in both 7.x and 2000 Engine.
:confused:
Hi,
I was having problems on myt PC so i did a fresh installation. However I had backed up my SQL database (I only have the MDF file and not the log file).
I have tried restoring and attaching options but nothing works :(
I also get the error saying
"Can not open backup device.... Device error or device off-line. See the SQL server error log for more details. RESTORE database is terminating abnormally".
Please this is urgent. WIll be VERY grateful for your urgent reply.
Hi,
I was having problems on myt PC so i did a fresh installation. However I had backed up my SQL database (I only have the MDF file and not the log file).
I have tried restoring and attaching options but nothing works :(
I also get the error saying
"Can not open backup device.... Device error or device off-line. See the SQL server error log for more details. RESTORE database is terminating abnormally".
Please this is really urgent. Will be VERY grateful for your urgent reply.
Is there a way to automatically enable Service Broker on SQL Server 2005 Express Edition after a database has been attached or restored?
My initial idea was to check if broker is disabled whenever the client application starts and enable it from there but the problem with this is that the current user might not have the required permissions. So now I'm looking for another way to enable it right after restore/attach if at all possible. Any ideas would be appreciated.
I can restore databases one by one, setting .mdf and .ldf destination paths.
How can I restore all my Databases at the same time?
Thanks
G. Zanghi
We have 5 databases which will sum up to 8+TB, housed in our EDW server. We are planning to migrate to another storage system for better performance.
I am planning Full backup and Differential backup and detach the DB and restore the DB in the new storage system.
Or should I detach,copy the data and log file and attach the files, will this approach have any advantages ?
I'm using SQL Server Management Studio eExpress and I'm tryng to link to a database created in Visual Web Developer Express. My problem is that I can't see large parts of my file system when I try to attach a database in SQL Server Express. Web Developer Express likes to put its databases in C:Documents and Settings<User Name>My DocumentsVisual Studio 2005WebSites<Site Name>App_Data. However, when I try to attach a database in that location to SQL Server Express (Right Click Databases node, select Attach from the shortcut menu then Add from the Attach Databases dialog) I get a folder tree that won’t let me go any further than C:Documents and Settings<User Name>. There seem to be large parts of my folder structure that I cannot access with this dialog.
Just in case this is a permissions issue, I am using Windows XP Pro on a stand alone machine. I log on as Administrator, and the <User Name> in the above paths is Administrator. The Administrator is a member of the Administrators group. I have never logged on as anything else and never made any changes to the way permissions are set. When I start SQL Server express I select Windows Authentication.
TIA
I am working towards automating the process of testing our backups. For the meantime, I do it all manually - I copy the backup files (full + transaction logs) to our test server and then run the restore script. Once database restored I run the DBCC CheckDB. The results of checkdb I manually upload to our Sharepoint portal as proof that the backup file is intact with no errors.
here are some ideas I have but have not yet tested:
Create a maintenance plan with each 3 jobs:
--> Powershell script to copy the files from Prod server to Test server - add this scrip to Job1
--> Powershell script to restore databases files - add this script to Job2
--> Run the DBCC in powershell (yet to find if possible in PS) - add this script to Job3
I would like to use seperate jobs as to get a report on the duration and status of each job
Would also like to get the results of the DBCC Checkdb as proof that no errors were found for upload to our Sharepoint portal. Dont know if possible via the job.
Hi,
I had a database running under a SQL Server 2000 server. I detached it and then attached it to a 2005 Server. I the detached it from the 2005 server and tried to re-attach it to the 2000 server. I got this error message:
Hi all,
Please let me know if SQL Server 2000 databases be held in RAM inorder to improve performance.
Thanks in advance
Praveena
When I proposed start to use SQL Server 2005 for new VS 2005 web sites, one of my co-workers responded that
we will update the old SQL Server 2000 databases to SQL Server 2005 when we are ready to use 2005 SQL Server.
Questions:
1. Any expected problems to upgrade old 2000 databases to new 2005 SQL Server?
2. I have installed both 2005/Management Studio Express and 2000/Enterprise Manager in my PC. Any expected
problems when running both 2000 and 2005 SQL Server at the same database server?
3. What is the best configuration for running SQL Server 2005 when we have old 2000 databases? Upgade or not upgrade?
TIA,
Jeffrey
Hello All,I am not a SQL Server expert, but I have been put in a prettyprecarious situtation and I am close to panicking :( I have need torestore a SQL database from backup (through Enterprise Manager). Thedatabase got corrupt and I detached it, then was unable to attach itagain. I created a new database with the same name and file names torestore to. When I go to All Tasks, restore, and restore from apreviously run full backup, it says "SQL Server is currently in theprocess of restoring the backup set," but the progress bar does notbudge. I look in the data folder and see the full sized transactionlogs (35GB I know, need to be shrunk). The .mdf file remains at 1024Kthough. Do I need to just wait longer? Normally when restoring from aSQL Server .BAK file, the progress is shown to me as it goes along. Ido not know what to do here. Any help would be GREATLY appreciated. Iapologize for sounding like a newbie, but with SQL Server, I amdefinitely in that category.Thanks,-D
View 1 Replies View RelatedDear all,
I have a backup for SQL server database.
It use differential backup.
I use following steps to restore the database to one of date's backup,because I want to monitor this date's data.
(1) restore a set of the complete backup first.
and select " Leave database nonoperational but about to restore additional transaction logs"
(2) restore one of date's backup, the backup number is 70.
if I restore the backup number by sequence after a set of complete backup is restore, it is ok
But I can not restore them by sequence,because there are many diffenential backup.
The error message is :
Cannot apply the backup on device 'c: empCk2006backup' to database 'CK2006'.
Restore database is terminating abnornally.
I have tried to use following choice :
Force restore over existing database
Leave database operational. NO additional tracsaction logs can be restored.
Leave database nonoperational but able to restore additional transaction logs.
Leave database read-only and able to restore addtional transaciton logs.
But it also failed to restore.
How should I do?
Thanks you
Hai friends.....
I am doing project in ASP.NET(vb) my backend is sql server 2000...in my PC..
I have another pc....where sql server 2000 is installed.....
i have nearly 50 tables...in sql server 2000. in my PC..
I want to backup these tables from my pc and another pc...
can u tell me the steps...
I will try immediately....
Ambrose......
Hi,
Any one know how to restore a SQL 2000 backup on to a server which has SQL 7.0??
Thanks,
Naveen
Is there a way to restore a "single" object in SQL Server 2000? (I only need 1 table and 1 stored procedure restored -- however it appears as though I need to restore the ENTIRE DB to get to these!)
thx.
Hello!
I have a bug in SQL Server 2000 that I would like to discuss.
The bug is described briefly on http://support.microsoft.com/kb/821334.
"The potential for inconsistency in the backup history tables backupset and backupmediafamily is resolved. This issue may cause the restore process to point to the wrong backup files."
According to my experiences the bug is extremely serious. SQL Server backup and restore operations are not fully reliable. If you have a disaster situation there is a possibility that you can not perform a restore.
Technically a media_family_id is generated in a backup operation and inserted into msdb.dbo.backupmediafamily. What could happen is that a media_family_id, that already exists in backupmediafamily, is generated.
The bug is fixed from version 8.00.0859, but only if you have an special undocumented trace flag (3003) enabled.
Here is the information that I have got from Microsoft about this trace flag.
"Trace flag 3003 changes how SQL generates media_set_id for new backup files. It will guarantee that new media_set_id values in backupset table are unique when you create a new file with your backup. This is the only place in code where TF 3003 is used so it will not impact your SQL Server operations."
I have a SQL Server with 80 databases. I have a maintenance plan with full backup every 24 hours and log backup every 5 minutes.
The error happened on my server a few times every 24 hours. I believe it's a general problem.
Best regards
Ola Hallengren
scott writes "Hi!
I have been asked to write a DTS package that copies a database (call it DBOLD) to another database (call it DBNEW) every night. I do the following:
BACKUP DATABASE DBOLD_App
TO DISK = 'C:ackupDBOLD.bak'
WITH INIT
GO
Then I do the following:
RESTORE DATABASE DBNEW FROM DISK = 'C:ackupDBOLD.bak' WITH MOVE 'DBOLD_App_Data'
TO '... the new one .MDF', REPLACE,
MOVE 'DBOLD_App_Log' TO '... the new one.LDF', REPLACE
The RESTORE often (but not always) fails, particularly if 2005 Reporting Services has queried the data in DBNEW during the day. There should be no-one using the DBNEW overnight when the DTS package runs, so I have permission to "toss then overboard" if they are (note - DBNEW is effectively read-only, for reporting purposes, and is completely refreshed each night, so lost updates etc are not an issue).
How can I force the restore to proceed? I've tried dropping, detaching/attaching but the existing connection(s) apparently prevent this. I dont care about the contents of DBNEW as the data is conmpletely refreshed each night.
If you have any ideas I would be pleased to hear them...
Thanks in advance...Scott"
Just a quick question, does anyone know any major issues with Migrating Sybase Databases to SQL Server? Just wondering if their any oddities I should be aware of? Thanks nixies
View 2 Replies View Related