July 3, 2016 at 2:33 am
This is venu, i am learning SQL Server DBA.... while practice i got a doubt.
1. I have attached "AdventureWorks2012' in default & named instance and set backups paths also separate.
2. Now i taken backup from default instance login and closed instance. then open named instance again taken backup from this.
3. After that, i have checked "Filelistonly" to both backups. it shows same instance-database for both backups.
4. How can i take backup from each instance-database, here database is same for both instance.
Pls provide your valuable suggestion..... thanks in advance...
July 3, 2016 at 12:52 pm
What do you mean by #4? Take a backup from each instance? There isn't a thing as an instance-database. If you want to take a backup from each instance, you will need to connect to each instance and run the backup command. This can be from SQLCMD, SQL Agent, Powershell, SSMS, or other ways.
If you are using SSMS to connect to each instance, there is no need to disconnect from one instance to connect to the other.
July 3, 2016 at 10:07 pm
Thanks for your reply....
i got solve my problem by using SSMS only.... i have no idea about "Power shell & sqlcmd"...
July 4, 2016 at 6:39 am
You can't attach a single database to two instances. A database can only be attached to a single instance.
I suspect you only have one instance, not two, hence when taking backups it shows the same instance name, because it is the same instance.
Connect to you 'default' instance, run the following and paste the results here
SELECT @@SERVERNAME
Then connect to your 'named' instance and do the same (run SELECT @@SERVERNAME, paste the results here)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2016 at 10:51 am
You can't attach a single database to two instances. A database can only be attached to a single instance.
_______________________________________________________________________________________________
Hi Mr. Gail,
Thanks for respond on my post.....
Pls find attached snapshot for your idea...
Here i have attached "AdventureWorks" sample databases to "default instance (11.0.6020) & named instance (11.0.2100). same databases in two instances...
Mr. Gail,
I have another query on this topic.......
i have taken backups from both instances. can i apply restoration using backup files (11.0.6020 .bak file on 11.0.2100 instance & 11.0.2100 .bak file on 11.0.6200 instance) on opposite instances? Actually i have tried, but getting error...
Pls provide valuable suggestions.......thanks in advance....
July 5, 2016 at 11:49 am
You cannot attach the same database to two instances. You can attach two copies to different instances, but when you attach the database, the SQL Server service takes an exclusive lock on the mdf/ldf/ndf files.
The name of the database is irrelevent. This is something you come up with, but doesn't mean anything. If you look at the properties of the databases, they will be using two different files. If they're using the same one, then you can't start both instances at the same time, and the down-level (older) instance will not be able to recover the database if you've opened it in the newer instance.
If you restored the database, which is the way most people do this, you didn't attach it. You restored it, which takes a copy of the backup file and moves data into a mdf/ldf/ndf file(s).
When you backup a database, you copy the contents to a file. You can only restore that file on the same level (version) of SQL Server or a newer version. So if you have a 2008 RTM instance and a 2008 SP3 instance, you can restore RTM backups to the SP3 instance, but not vice versa.
July 5, 2016 at 1:53 pm
Steve Jones - SSC Editor (7/5/2016)
So if you have a 2008 RTM instance and a 2008 SP3 instance, you can restore RTM backups to the SP3 instance, but not vice versa.
Nope, you can restore from SP3 to RTM. The DB version is NOT bumped by service packs (only exception there was one of the SQL 2005 SPs)
What you can't do is restore from SQL 2012 to SQL 2008. It's the major version that matters, not the build.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2016 at 1:56 pm
SSC Venu (7/4/2016)
Here i have attached "AdventureWorks" sample databases to "default instance (11.0.6020) & named instance (11.0.2100). same databases in two instances...
No you haven't.
You have two copies of the AdventureWorks database, one attached to each instance. Think of it like having one copy of a certain book at home and another copy of the same at your office. Same book title, but two different copies of it. You wouldn't say that the same book is in both locations. Same with the databases here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2016 at 11:05 pm
Thanks for respond,
Now i got clear my doubt. yes i have done two copies for instances....
July 6, 2016 at 1:23 pm
GilaMonster (7/5/2016)
Steve Jones - SSC Editor (7/5/2016)
So if you have a 2008 RTM instance and a 2008 SP3 instance, you can restore RTM backups to the SP3 instance, but not vice versa.Nope, you can restore from SP3 to RTM. The DB version is NOT bumped by service packs (only exception there was one of the SQL 2005 SPs)
What you can't do is restore from SQL 2012 to SQL 2008. It's the major version that matters, not the build.
Thanks, my mistake. I know there were bumps in some service packs, but checking, it's very rare.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply