6

Is it possible to take Backup of SQL Server Compact database (i.e. *.sdf) and Restore the same.

Jon Seigel
  • 12,251
  • 8
  • 58
  • 92
team-ferrari22
  • 181
  • 2
  • 7
  • 2
    SQL Server Compact is not SQL SERVER. Is a subset of SQL. If you want full SQL Server compatiblity use SQL Server Express. – devSpeed Feb 19 '10 at 17:51

2 Answers2

10

Just copy the file. Done.

Quote from Maintaining Databases:

Because SQL Server Compact 3.5 is a file-based database system, you can accomplish many common database tasks such as backing up, restoring, and deleting a database by using the file system APIs.

To back up a database, close all connections to the database, and then copy the .sdf file. To restore a database, copy the .sdf file back to its regular working location. These operations work even if the database is set up for replication. To drop a database, delete the .sdf database file.

AngryHacker
  • 59,598
  • 102
  • 325
  • 594
  • 1
    No programmatically we need to take backup like, SqlCeCommand cmd = new SqlCeCommand(); cmd.Connection = conn; cmd.CommandText = @"BACKUP DATABASE [D:\\CompactDatabases\\Northwind.sdf] TO DISK = 'C:\\Test\\DB.bak'"; cmd.CommandType = CommandType.Text; int i=cmd.ExecuteNonQuery(); but it is throwing exception as below : There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = BACKUP ] Then how take the backup programmatically? – team-ferrari22 Feb 19 '10 at 07:46
  • 5
    Programmatically? Sure. File.Copy("mydb.sdf", mybackup.sdf"); – ctacke Feb 19 '10 at 13:58
  • 4
    The biggest issue with the file based restore you mentioned AngryHacker is "close all connections to the database" This may not be possible without taking the website offline (assuming it's a website) and even then the application pool process may be holding the file open. – Simon Holman Feb 21 '11 at 10:31
2

I write the compact SQL database out to an XML file using LINQ to SQL and read it back in to restore it.

Mark W
  • 811
  • 2
  • 11
  • 17
  • Thank you for your reply. Do you think we can restore backup taken from SQL Server database in to SQL Server Compact database. – team-ferrari22 Feb 22 '10 at 05:43