If you were trying to restore a user database - this would restore alright. The problem is that this is a system database. The system databases are designed for the version of SQL they were intended to work in. In order for SQL Server 2008 R2 MSDB to work in SQL Server 2012, you'd have to have no features different between the two versions. Make sense?
The Short Answer
To bring your jobs across, you'd script them out from the old server and apply the script on the new one. You can do this as simply as right clicking and scripting each job if you have a few. Or looking into a script with PowerShell or some other approach if you have a ton and don't want to one at a time.
To take the Logins you'd use the Sp_help_revlogin script I reference below and a script to copy server level roles and permissions from the old and copy them to the new..
Basically for 90% of what you'd bring across, I'm pretty sure the answer is "script it out" and then just apply that script on the new server and the logins, the jobs, etc. will all now live in the Master and MSDB databases designed for SQL Server 2012.
The slightly longer answer and a quick discussion on approaches to migration/upgrade
So if you are trying to transfer all of these objects to SQL Server 2012 you have two basic options on your approach.
- Do an in place upgrade. Upgrade your instance from SQL Server 2008 R2 to SQL Server 2012..
Pros - you get all that 'stuff' (agent jobs, linked servers, logins, alerts, operators, mail profile, etc.) and you don't have to copy objects around..
Cons - it can be a bit messy, it works fine nowadays and is supported but I am paranoid and like to know for sure that I have success and a quick rollback option (if issues on new server, just revert back to your old server during a migration. With an in place upgrade, it is much more, well, final).
You can start here for an in place upgrade.
- Migrate to a new server...
In this case you just flip the pros and cons from above.. The approach isn't that tough or strenuous. It just requires some good planning. Basically you:
On your old server, script out all of the objects you want to move according to the instructions and approach for each object type which can typically be found in Books Online (objects like Jobs, Linked Servers, SQL Agent Alerts, etc.)...
Use a tool like sp_help_revlogin to move your logins across and a script kind of like this to move the login permissions across..
Backup and restore your databases which already contain the in-DB users and permissions(I like doing this over detach attach because it helps preserve that rollback ability but I've seen and done this either way).. Change your compatibility mode if you are planning on supporting the DBs in 2012 mode and have tested them that way. Or keep them in 2008 mode if that was your plan.
Run those scripts for all of the objects like jobs, logins, etc. on the new server that you created above from the old server(good to do most of these after the DBs.. as logins will error on you if their databases that they default to aren't there or a T-SQL step in a job's database isn't there, etc.)
The other nice thing about this approach is you can do a trial run ahead of time during business hours.. Point a test or dev version of the app(s) used on that instance to the 2012 and see what breaks.. Fix it and test out your checklist, your rollback plan, etc. and do that prep for go live night.
The database is a SQL Server 2000 database (version 8.00.2055) - and you cannot directly update a SQL Server 2000 database to SQL Server 2012.
You'll need to take an interim step first (restore it in SQL Server 2008, back it up from that server again).
See the relevant MSDN documentation:
In SQL Server 2012, you can restore a user database from a database backup that was created by using SQL Server 2005 or a later version.
but SQL Server 2000 is NOT supported.
Best Answer
It is not possible to restore a single table from an earlier point in time directly into an existing database.
You can restore all or part of the database as a new database, then copy the required data across. Or, you can restore using the
REPLACEoption, but the whole database will be reverted to the earlier point in time, not just the table you are concerned about.If all your data is stored in the
PRIMARYfilegroup (the default), you have no choice but to restore the whole thing. If you have multiple filegroups, you may be able to restore just some of the filegroups, reducing the space needed. The details depend on the database recovery model as well as how any filegroups are arranged, see:You can determine in advance how much space will be needed for the restore by running the following command and inspecting the output:
RESTORE HEADERONLY(Transact-SQL)The paths to the files that will be written are all from the perspective of the SQL Server instance, not the user performing the restore. You will need to ensure that enough space is available for all the files that will be restored. Note that the size of the file may be larger than the amount of data stored in the file, and larger than the size of the backup file. SQL Server only writes the data actually stored to a backup, which may also be compressed, but a restore must create the full file size.
The process of creating the data files will be much faster if Instant File Initialization is enabled on the restore target.
Backup and restore is a large topic, well covered in the documentation, see:
The T-SQL
RESTOREcommand offers more flexibility and control than using the SQL Server Management Studio UI.If you have differential and/or transaction log backups as well as a full database backup, you may be able to apply one or more of these to get closer to the point in time when the accidental truncation occurred.