Hello Friends,
I am new to SMO, Till now I have only worked with Selct,Insert/update Queries.. Now I want to Implement some backup and restore feature for my application. I looked at some documents of SMO and found it is possible using SMO.
First of all I will explain my situation.
My application wants to store data belongs to different companies(which is maintained with in application) in to single database server, Sql server 2008.
I am planning to use Partition Scheme function and create filegroup for each company. Advantage here is, taking backup of each company will be easy.
I am generating primary key for each table without taking into consideration of companies. In other words Particular table's ID 1,2 belongs to Company A, 3 Belongs to Company B and 5 belongs to Company A... But these data will be split in to Different filegroups using Company ID.(horizontal partitioning)
Ok... Now the Problem..
My backup for a particular company was taken successfully. Now I want Restore function. When I am trying to restore this backup to somewhere else. Target database may or may not contain Companies installed. When Restoring Will there be any Primary key Violation?
I know one solution is to generate primary key by taking into consideration Company Id. But this will cause a complete rewriting of code, as it contains lot of edit/delete queries.
I want to know, whether the

I am trying to restore a brand new database from a copy of another one and am encountering an error message stating:
System.Data.SqlClient.SqlError: The file <Backup source database file location> cannot be overwritten. It is being used by database
<Backup Source Database>. (Microsoft.SqlServer.Smo)
I am running the Restore procedure with the REPLACE option and am wondering why it is stating that the source database is the database in use when I am trying to restore and overwrite a completely different database.
This issue happens when running the replace both in C# using SMO and when manually restoring with SqlServer Management Studio
Interesting note is, the original source database is created / deployed using a database project within VisualStudios 2010 and then deployed to SqlServer through VisualStudios.
Any thoughts on why this is happening?
Thanks in advance for any help!

Hi. I am creating a vb 2010 application and I am using sql server express as my database. I need to create a backup and restore feature for the user so that the user can backup or restore the database while using the program. I have nop clue where to start. Does anyone have some code for or any suggestions?Thanks in advance.

We are upgrading the platform for our production SQL 2005 server. Of course, I have done this task numerous times....however this is the first time I will be dealing with Reporting Services. I am concerned that it may not be a simple backup databases
and then restore on new server, because of the encryption keys.
What do I need to do ?
Thanks!

VERY IMPORTANT
i am trying to restore database.bak in sql server 2005 (i know the database.bak was also generated in sql 2005 server)
i am trying to restore back up database .bak into the new database i just created in sql server 2005
i
have saved my database .bak into c drive and when i select database
.bak "From Device", it doesn't get populated in the list below and i see
nothing and it keeps on prompting a message "You must select a restore source"
Here's the screen shot:
PLEASE
HELP..it's really important (i tried restoring database in sql server
2008 and it was sucessful but i am facing this problem in sql server
2005 only)

Hi,
I am using SQL Server 2008.
I made a backup of a database. its size is about 1000MB.
I did a verify the backup and it does not give me any error.
I copy the backup file to my Machine(Development Machine).
I am using the exact same version of SQL server.
When I try restoring from back i get the error.
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The media family on device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\dcaRms1-05Bkp.bak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)
When I tried doing in from code:
just a Restore Verifyonly I get this error:
Msg 3241, Level 16, State 7, Line 1
The media family on device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\dcaRms1-05Bkp.bak' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
I really do not understand.
It is not like I am trying to restore the backup created in 2008 to 2005 version.
and I am pretty sure the backup file is not corrupt. I can restore it in the same database.

I have a SQL Server 2008 R2 instance that I need to backup and restore onto a SQL 2008 instance.
When I attempt to restore the back up (.bak) database from the R2 SQL server onto the SQL 2008 server I get the following message:
The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with
this server. (Microsoft.SqlServer.Smo)
How can i backup the SQL 2008 R2 database to be compatible with SQL 2008?Ian Ceicys

I thought this would work, but obviously I've overlooked something. I have SQL Server 2008 on 2 servers.
Have database on publisher server and want to merge replicate to subscriber, but didn't want to take days like it did last time.
1. I published the database and ran the snapshot agent.
2. I backed up the database to a .bak file.
3. I copied the .bak file to the subscriber computer.
4. I restored the database on the subscriber computer from the .bak file.
5. Back at the publishing server, I added the subscriber under subscriptions, but I unchecked the "Initialize" box since that was the point of doing the backup, copy and restore.
And. Oops. The replication monitor says it has a syntax error at "AccountPK." That would be the primary key for the account table. That wasn't what I hoped for.
Suggestions?
You can't be successful at this unless you're at least 1/2 a bubble off level.

Currently, I am unable to do it. It gives me the error than I cannot be restored because it was created by a different version of the server. What is the work-around? I hate scripting all my maintenance plans and alerts, there are tonns of themJulieShop

I have a problem when i restore my .DAT_BAK file.ÃÂ I am getting error like "The backup set holds a backup of a database other than existing database.ÃÂ Restore Database is terminating abnormally".
ÃÂ
I tried by using
ÃÂ
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:\DATA\MYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:\PROGRAM FILES\..\MSSQL\TEST.MDF',
MOVE 'VZAI_LOG' TO D:\PROGRAM FILES\..\MSSQL\TEST.LDF',
REPLACE
ÃÂ
And also i tried like
ÃÂ
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:\DATA\MYTEST.DAT_BAK'
WITH REPLACE
ÃÂ
When i use like this,
ÃÂ
RESTORE FILELISTONLY FROM DISK = 'D:\DATA\MYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
ÃÂ
Can i anyone please help me out?
ÃÂ
Thanks in Advance,
Anand Rajagopal

Dear all,
we are facing a problem here. Our sys admin set up everything to run the farm backups. He is using Symanteck Backup Exec 2010. In that farm we have 3 web applications which represents 3 content databases on a separate SQL back-end machine. When the backup
is done, the report tells the backup succeed. It happens that when we try to recover from the backups, we can recover from an item level from 2 of those web applications, but not from 1. It created the whole site backup, but we cannot dig in to recover an
item or a subsite.
Did anyone had this problem before?
(I gave farm admin levels to the account used by Backup Exec to double check the rights. That user could log on to the site and indeed he could go to all subsites and all item levels. So it is not access I believe).
Any other expiriences with this tool? What about other tools? We are having a demo of AvePoint soon. Does anyone worked with that before?
Thanks in advance for any help.
Regards,
GoncaloGonÃÂ§alo

I need to migrate the server from sql server 2005 to 2008. I need to migrate all 500+ user databases. in order for me to minimize the downtime, can I do the following:
1) run full backup on 2005 source and transfer the data over to the destination
2) restore user dbs from these backups
3) shut down the apps on source
4) run diff backup on 2005 source and transfer the data over to the destination
5) restore user dbs from these backups
Is it possible to apply only diff backup on the destination or the dbs should be restored from full+diff only? Please helpJulieShop

After installing the trial version of Sharepointn 2010 I wanted to restore my Sharepoint 2007 sites to it but received the following error:
"The site collection could not be restored. If this problem persists, please make sure the content databases are available and have sufficient free space."
I went on to create a new content database in sharepoint 2010 but it still did not work.
The old database i.e the one used by the sharepoint 2007 server is SQL 2005. During the installation of the sharepoint 2010 version I selected SQl Server Express for the database.
Is there any specifics for migrating from MOSS 2007 to sharepoint 2010?
Thanks for helping

Hello,
I disabled TDE on my dev database because we determined no personally identifiable info will be stored. Disabling was done like so:Then I created a backup of MyDB database
and tried to restore on a different server instance on my same machine (using SSMS) and I get the following error:
ALTER
DATABASE MyDB
SET
ENCRYPTION
OFF
GO
use MyDB
go
drop
database
encryption
key
go
use
master
go
DROP
certificate DevCert
GO
DROP
MASTER
KEY
GO
Afterwards, I ran the following query to ensure there are no encrypted databases, and it returns zero rows:
SELECT
DB_NAME(database_id)
AS dbname,
encryption_state,
case encryption_state
WHEN 0 THEN
'Unencrypted (no database encryption key present)'
WHEN 1 THEN
'Unencrypted'
WHEN 2 THEN
'Encryption in Progress'
WHEN 3 THEN
'Encrypted'
WHEN 4 THEN
'Key Change in Progress'
WHEN 5 THEN
'Decryption in Progress'
ELSE
CAST(encryption_state
AS varchar(20))
END AS encryption_state,
key_algorithm,
key_length
FROM sys.dm_database_encryption_keys
Then I created a backup of MyDB database and tried to restore on a different server instance on my same machine (using the SSMS) and I get the following fatal error:
The media family on device 'c:\Program Files\...\Backup\TDE_removed_from_db.bak' is incorrectly formed. SQL Server cannot process this media family.

Hi Folks, i'm recieving this message when i try to make a site collection/farm/site backup "The backup/restore job failed because there is already another job scheduled. Delete the timer job from the Timer Job Definitions page, and then restart
the backup/restore job.". I'm going to central administration/monitoring/timer jobs/check jobs status but i can not find anything.