I had a development server that had all the raid drives fail at once.
This included most of the data/log files and all of the backups.
I need to get DTS packages from the MSDB database, I have the ldf/mdf's for that database, is there a way to restore it?
Thanks
Susan
I am trying to restore my msdb database after rebuilding my RAID, and reinstalling sql. I can restore my user databases fine, but my msdb gives me error 3132:
The media set for database msdb' has 2 family members but only 1 are provided. All members must be provided.
I really don't want to lose all my jobs, so any help would be appreciated. thanks.
hi, I already have jobs in the sql server agen. I want to copy all these jobs to another sql server, What is the best way to do it.
here what I am thinking to do
1. Back up the msdb from the sql server which has the jobs
2. restore msdb into the sql server where I need to create the jobs.
I got an error message, 15089 cannot change the 'single user' option of a database while another user is in the database.
But no one is in except me.? thanks for your input
Ahmed
I am trying to restore my msdb database after rebuilding my RAID, and reinstalling sql. I can restore my user databases fine, but my msdb gives me error 3132:
The media set for database msdb' has 2 family members but only 1 are provided. All members must be provided.
I just performed a normal database backup to disk, and i was able to restore the other db's i backed up the exact same way...
I really don't want to lose all my jobs, so any help would be appreciated. thanks.
Hello all!
First of all our servers:
SQL_PROD: production server with SQL Server 2005
SQL_TEST: test server with SQL Server 2005
What we are trying to do:
We are testing our backup and recovery procedures.
To do so we are using the backups from SQL_PROD and restoring them on SQL_TEST.
We are restoring: all user databases, master database, msdb database and model database.
The issue we are experiencing:
I think we've hit a kink in our recovery procedures concerning msdb database in the context of a recovery on another machine (SQL_TEST in this case).
We include msdb in our nightly backups so that, in the event of server loss, our maintenance plans and jobs get restored.
On SQL_TEST (after a recovery), the maintenance plans and jobs do get restored but the problem is that they reference the SQL_PROD server (server's name, disks, etc...).
Deleting the jobs and maintenance plans from SQL_TEST we got no errors and the jobs and plans disappeared from beneath the Maintenance Plans and Jobs nodes. Doing a refresh on the nodes, the €œdeleted€? plans and jobs reappear beneath their respective nodes! Upon the second deletion (and any consecutive delete) we get the error:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
...
The SELECT permission was denied on the object 'sysmaintplan_plans', database 'msdb', schema 'dbo'. (Microsoft SQL Server, Error: 229)
Clicking on "Show technical details" reveals this:
...
Server Name: SQL_PROD
Error Number: 229
Severity: 14
State: 5
Line Number: 1
...
Note that SQL_PROD is being referenced in the error message!
This morning I took a look at SQL_PROD€™s Maintenance Plans and Jobs (the source of the msdb backup) and surprise surprise: THEY WERE ALL GONE!!! This is a MAJOR BUG!!! Deleting a job or a plan on one server should not have the effect of deleting it from a different server (regardless of the origins of the msdb database). Is this a known bug? Or is it considered €œnormal€? behavior?
My questions are:
How do we delete the maintenance plans and jobs from SQL_TEST without deleting them from SQL_PROD?
What is the proper/recommended method to backup and restore maintenance plans and jobs (in fact msdb)? Is it by scripting things and recreating them on the recovery server in an empty msdb or is there a way to restore msdb properly from the crashed server?
Thanks for your help.
Regards,
Mark Tabash
Late last Sunday I had to do an emergency rebuild of our SQL Server (v2012). In my haste, I neglected to restore the MSDB and MODEL DBs, and am instead using those which came with the fresh SQL install -- which obviously don't contain my jobs and maintenance plans. Would there be any problem/conflicts with me restoring those DBs this weekend?
View 5 Replies View RelatedMy scenario -
-Dev server lost all its raid drives (which included user data/log files and ALL backups)
-Raid drives were removed from server, so we could try and recover the data (not sure why they had to remove them)
-Recreating the user db's isn't an issue, but I REALLY need to retrieve the jobs from the MSDB (developer was working on dts package for the last 2 months, but didn't check it in to VSS)
-Since the System DB's (model, master, msdb) were installed on the C drive, I was able to recover their mdf/ldf files.
So we build an new server and recreated the user db's.
How do I go about getting the jobs data from the msdb database?
I've tried the following -
Started SQL server with the -T3608 and was able to detach the MSDB database, but when I go to reattach using the mdf/ldf files from the other server I get the following error message.
Error 5172: The header for the file 'C:mssqldatamsdbdata.mdf' is not a valid database file header. The PageAudit property is incorrect
I've been doing some research and found a lot of great articles on how to RESTORE (from backup - dont have) or Rebuild (wipes out the data I'm looking for) the MSDB, but not how to replace it with only the ldf/mdf files....
Any idea's would be greatly appreciated.
Thanks
PS - All backups are now being copied to another server (lesson learned)
How does one correctly go about restoring the MSDB database?
How/what should I do with the status (loading)?
I have a good backup of the MSDB.
Any help will be appreciated.
I have a sql2000 server that has failed
i moved the databases to another server
I am trying to restore msdb to another server is this possible in order to get the dts packets and the jobs
Hi,
I have a weekly job that runs to execute msdb.dbo.sp_delete_backup_history on history older than 7 days on a server that is a log-shipping destination server. However, I have now noticed after putting a new deadlock alert, that this job causes RESTORE LOG jobs for all 6 of my log-shipped databases, which run every minute, to deadlock.
I am not concerned with the fact that the RESTORE LOG jobs are then chosen as the deadlock victims, as they will simply re-run in the next minute anyways.
What I would like to do though is to somehow lock all the tables being cleared by sp_delete_backup_history, so that the restore jobs are forced to wait or fail without causing a deadlock to appear. I was wondering if I could do this via setting the job which runs sp_delete_backup_history to use serializable isolation level, or would that cause other problems/issues? If not that, would setting the deadlock priority level to high prevent deadlocks, or just simply force the restore log jobs to be chosen as the victim?
The main thing is that I would like to stop being "annoyed" by the deadlock messages being generated.
Any comments would be appreciated.
Regards,
Diane.
I have been researching on how to cleanup four backup system files located in the MSDB database. I currently have approximately 1 million rows in each of these tables which is making my MSDB database to be 4 gig in size. I cannot find any instructions on how to gracefully clean these tables up or what to do to keep the number of rows down in these tables. The tables are backupfile, backupmediafamily, backupmediaset, and backupset. Thanks.
View 1 Replies View RelatedWhat is the best way to restore a database from a folder of backups (including full, diff and log backups) without using the backup history in msdb?
I have a restore process that restores all backups on a regular schedule in order to fully verify their integrity. To do this, I use the backup history in msdb on each server that I'm monitoring. I had a thought the other day that I would be in trouble if I lost msdb. Then my backup history would only be as good as the last backup of msdb.
What I'd like to do is read a folder of backup files and generate a restore script up to a specified time. Would I use RESTORE HEADERONLY to do this? If so, would I use PowerShell to traverse each file in the folder?
Hi,
While backing up our database, I am getting the following message:
Could not insert a backup or restore history/detail record in msdb.dbo.sysbackuphistory or sysrestorehistory. This may indicate a problem with the MSDB database. DUMP/LOAD was still successful. (Message 3009)
Does anybody have any resolution for the same ?
Thanks,
Rajan
I have deployed to production a number of nested packages (parent packages that call child packages) to the SQL msdb via the Save As option rather than building a deployment utility. These packages reference configuration files in a static location off of the c: drive on the production server. In the development environment, when connection changes are made and I run the Reload with Upgrade option the connection manager takes on the new server and user id settings. However, out on the production side I get the following error from the SQL job log:
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
As a result the SQL job uses the default connection information which references the development database rather than the production database. I did research the error but found no good solutions. Is there a way to ensure the configuration files are formed correctly and that the packages are correctly referencing the configuration files? We are trying to run the ETL updates via a SQL job.
Any suggestions? Thanks.
T
Whenever I start my SQL Express 2005 database, I get the following in the logs :
View 10 Replies View RelatedHi,
I backup db into several .bak files to reduce backup time. but how can I restore them back? Do I have to do it by scripts? Can I restore it by using management studio?
cheers
I have troubles with my computer, in the pc I have some databases but the only thing that they can recover were the .mdb and .ldb files.
How can I restore a database from these files?
i accidentally restored a db to a backup from last year and i don't have a recent backup - is there anyway to restore from log files?
View 2 Replies View Relatedhi
To restore files and filegroups in a different server. First, Do i have to create the database with same name as the old one? or it doesn't matter... Can we use Enterprise Manager to do this restore?
Thanks in advance
I have a 24gig DB that has 1 data file.
Is it possible to RESTORE the backup to a DB that has 2 files
(1 = 10gig 2 = 14gig).
Does one of the files on the the DB have to be at least 24gig to RESTORE?
I'm the DBA in an environment with approximately 240 identical (structurally) MSSQL 2000 databases in an environment. Each one of these databases are different business units around the country. For each of these DBs, I have my backup schedule set up to do a full backup every friday night, along w/ doing a transactional every 4 hours in between. Every once in a while (about once every 3 weeks), I get the call to do a restore to pull some piece of data that some end user accidentally deleted or broke. Now most of the time it isn't a big deal, because I can use enterprise mgr to restore to a new DB from the live backup set of the database in question.But, several of the databases seem to have an issue where (for some reason) my predecessor renamed the logical file names of the data and log files to various goofy things. It doesn't affect their operation, but when I try to do a 'restore from database,' it gives me a bunch of big fat errors. This is also the case when the backup I want to restore something that has been written to tape and has been rolled out of the backup history.SO... basically for these databases, now I go into Enterprise mgr and first restore the base .bak file to a new database, leaving it marked 'read only' and 'able to restore additional transactions.' I then have to do the same thing with restoring each additional TRN file, all the while making sure I don't forget to set the 'able to restore additional transactions.' Whenever I forget to set that I have to start all over. And honestly, it happens more often than not when I have to restore 5 days out from the .bak file, which means that I have restore 30 trn files on top of the .bak. Not hard, but incredibly tedious.So here's my idea. I know I can script restores thru QA. I've been looking quite a bit at this MS doc (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#E2AA) and have come up with some good ideas. What I want to eventually do is create a script that you can just paste the .bak file and the list of .trn files. The script basically creates a temp table w/ those file names and cursors thru the restore process. The cursor part is easy for me, I'm just not sure about scripting the trn restore. The MS doc is a bit vague and really only shows the Enterprise Mgr method.Here's what I've been playing w/ so far for restoring the base .bak file:restore database restore_dbname --NAME OF NEW DBfrom disk = 's:mssqlackupuserdbnamedbname_db_20060120184 1.bak' --PATH TO BAK FILEwithmove 'pm65mr1_default_data_OldDBName_Data' --LOGICAL FILE NAME OF ORIGINAL MDFto 'I:MSSQL$MSSQLSERVER2Data
estore_dbname.mdf' --PATH TO NEW MDFmove 'pm65mr1_default_data_OldDBName_Log' to --LOGICAL FILE NAME OF ORIGINAL LDF'U:MSSQL$MSSQLSERVER2Log
estore_dbname.ldf' --PATH TO NEW LDFpartial, recoveryI think I'm pretty close with this, but I'm not really sure how I can stack the trn restores on top of that. Any ideas? I've been putzing around the web and so far haven't been able to come up w/ anything really useful.
Hi,
I have a database that over time has become spread over different files, file groups all of various sizes.
I want to restore this database to a different set of files/filegroups and evenly spread.
It appears that I can only resotore a database to number/of and size of files from which it was backed up..
I want to redistribute a 40GB file, using EMPTY is taking for ever and then eventually fails.
What can I do?
Thanks for your help
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
Hello,
I have a SQL 2000 DB. Current tables in sql 2000 DB are in single file. I am planningto migrate the DB to sql 2005. I am going to partition the tables in sql 2005 and have multiple files. What is the best way to do this? Would backup/restore work? If I restore onto sql 2005 from sql 2000 backup, will the tables spread over different files automatically or not? Any ideas will be appreciated...
Thanks........
Hi everyone,
already tried this in other SQL forums, but maybe i have some luck here.
I need mainly to restore database backups from customers. They arrive in all kind of formats (zip, rar, gz). I'd like to be able to restore those directly from the compressed file, because i'm talking up to 7GB rar files which take a while to uncompress in a separate step.
I'm working for 6 years in R&D environments, but mostly on Linux/Oracle where this is an easy task using pipes, but i haven't found a sinlge web page, post or even script to do this with MSSQL. The VDI is not really what i'm looking for, so aren't backup software like SQLBackup, Litespeed etc. because i can't force the customer to use those.
Anybody any idea or even the same problem maybe with solution?
Thx.
I have a full backup followed by transaction log every Monday, Wednesday and Friday, how can i restore this file using sql agent to automate restoration of backup files with different file-name.
View 6 Replies View RelatedI have a client that has POS software called Restaurant Pro Express (RPE) from www.pcamerica.com
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 have an automation project to review the software. The software is using MSVS2012 and uses DBs from MSSQL2012. Is it possible to restore the DB files on my local laptop to review the code completed thus far? If so what other software would I need besides MSQLserver 2012 on my local laptop.  I'm able to use MSSQL server 2012 on my local laptop, but the DBs are on servers.Â
View 4 Replies View Related1. Created a database with a couple of tables with no data.
2. Taken a full database backup - db.bak.
3. Deleted the database.
4. Restored the database db.bak and filled the tables with some data.
5. Taken log backup - dblog.trn
BACKUP LOG <dbname> to DISK='D:Demodblog.trn' WITH NO_TRUNCATE, INIT
6. Dropped the database again.
7. Restored the database again from db.bak.
8. But when I am trying to restore log file dblog.trn on this database, i keep getting this error :
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 2 RESTORE LOG is terminating abnormally.
What is the best method to restore a DBTest1 (with one .mdf and one .ldf) into DBTest2 (with one .mdf, multiple .ndf data files and with 4 filegroups associated with specific data files). I do not see how the one .mdf file (in DBTest1) can be separated into the other 4 filegroups (in DBTest2). This does not sounds like it is possible with Backup DBTest1/Restore to DBTEST2 or (Detach/Attach) because the underlying filegroup and file structure is different.
What method should be used to get the data and structure from DBTest1 (includes 1100 Tables and 550 GBs of Data) into DBTest2 (with 4 filegroups)? Is the following possible:
1) First, in DBTest2, execute a script to create tables/indexes on appropriate filegroups.
2) In DBTest2, use scripts to pull data from DBTest1 into DBTest2, for example INSERT INTO DBTest2.dbo.tables with SELECT FROM DBTest1.dbo.tables OR use SELECT/INTO DBTest2.dbo.tables FROM DBTest1.dbo.tables.
Or, is it possible to use the BULK INSERT or BULK COPY Options? Export/Import Wizard?
Does the Create Index step needs to be done after the data is loaded into DBTest2?
Hello,
I am attempting to restore the database from within VB.NET application I am making the following 3 calls:
RESTORE FileListOnly FROM DISK = 'C:MyDatabase.dat'
USE Master RESTORE DATABASE MyDatabase FROM DISK = 'C:MyDatabase.dat' WITH NORECOVERY, MOVE 'MyDatabase' TO 'C:Program FilesMicrosoft SQL ServerMSSQLDataMyDatabase.mdf', MOVE 'MyDatabase_log' TO 'C:Program FilesMicrosoft SQL ServerMSSQLDataLDFMyDatabase.ldf', REPLACE
RESTORE DATABASE MyDatabase FROM DISK = 'C:MyDatabase.dat'
using SMO. This logic works fine with small *.dat files, however when using *.dat file of about 4Gb I get an error on the 3d restore database call:
ExecuteNonQuery failed for Database 'master'.
An exception occurred while executing a Transact-SQL statement or batch.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Operator aborted backup or restore. See the error messages returned to the console for more details.
ExecuteNonQuery failed for Database 'master'.
An exception occurred while executing a Transact-SQL statement or batch.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Operator aborted backup or restore. See the error messages returned to the console for more details.
The same program/logic also works fine when I use MS SQL 2005 and it runs fine from MS SQL 2005 Query Analyzer for both 2005 and 2000 databases. There seem to be only problem with MS SQL 2000 from within VB.NET. Anybody has any idea? I'd appreciate any response. Thanks
Eugene
LocalDb cannot restore a backup whose original data and log files are in different folders
[URL]
In SQL Studio, I can go to the restore window and the click "verify backup media". This would check the restore plan listed in this window and check if some of the file are missed.Is there any way to reach this with TQSL? I know there is a "restore verify" command, but this will only verify one backup/file and not the complete restore path. I'm looking for a TSQL solution which is able to control that all necessary backup files are still present on the file System and not moved or deleted (e.g through cleanup task).
View 3 Replies View Related