2

I am working on project in which I have used visual c# as front end and SQL Server 2008 R2 Express for backend.

Now I know that SQL Server express database has a size limit is of 10 GB, so I have written the code for database backup when pick limit is reached and I empty the database once the backup is successful.

I want to know what will be best approach to restore the backup file so that my current applications backend (which I have emptied earlier) should not be disturb.

Is it okay if I restore the same in my current database, in that case I have question to ask does it affect my applications working, as my application is kind of real time and after every 15 min. interval stored some values in database.

Whether I need to write some other utility for seeing the old data..?

Every day around 50 MB of data is inserted into database, so it will take around 8 months to reach the pick size( as per my rough calculations). and as far as the nature of application is concern user will not going to use archive data frequently. please consider this and suggest me the approach.

thanks in advance..!!

Real Master
  • 347
  • 3
  • 14

2 Answers2

0

If you enter the data in a whole new database server your old queries won't work on the new one. As SQL Express limit is not per database, but per database server. You could create a new SQL Express Server, link your servers and create a query with a linked server ( How to create a linked server @ msdn )

You will need to adjust your queries.

If you query your data now like this: 

SELECT em.Name, em.Telefone FROM employees AS em

You need to refer the database too. 

SELECT [server1\db1].dbo.em.Name, [server1\db1].dbo.em.Telefone FROM [server1\db1].dbo.employees AS em

for your current database, and 

SELECT [server2\backup].dbo.em.Name, [server2\backup].dbo.em.Telefone FROM [server2\backup].dbo.em.Name

It is possible like this but I would not advise it. If you exceeded 10GB data already then you might have large tables. Each table at a linked server is copied completely to you server and could cause serious network traffic and takes quite some time to be executed.

I would think of getting the SQL Standard edition.

Bruellhusten
  • 318
  • 1
  • 5
  • thanks for your prompt reply. every day around 50MB of data is inserted into database, so it will take around 8 months to reach the pick size. and as far as the nature of application is concern user will not going to use archive data frequently. please consider this and suggest me the approach.. – Real Master Jul 29 '13 at 08:18
0

Hope i got your question right, but consider the following suggestion for working:
one database ("Current DB") that stores the real-time data.

when it comes to a size, it is dumped (or copied mdf+ldf) to archive.
and stored with time stamps (FROM-TO).

When data is needed, the relevant mdf is attached as a new "offline" database.
(you can use a connection string to attach MDF file to an SQL Server.)
and use that connection instead of the live one.

The application can run smoothly on the On-line database.
while reading, loading etc...
is done from the temporary attached and detached database files.

Take a look at : Connection String to Connect to .MDF for how to Attach a MDF to SQL Server instance.

Community
  • 1
  • 1
Tomer W
  • 3,395
  • 2
  • 29
  • 44
  • thanks for your reply. You exactly got my questions and u r suggestion is also relevant in fact i was also thinking of doing something like this.. but as i am newbie i need to do more homework for implementing this.. it will be greatly appreciate if you can provide me something like code.. – Real Master Jul 29 '13 at 08:19
  • one more thing i want to ask as we know sql express 2008 server is having database size limit of 10GB so, Is it not possbile that restored database + currentDB will exceed 10 GB..? your feedback on this will be really helpful for me. – Real Master Jul 29 '13 at 15:40
  • 1st, I think the limit is per Database, not per instance. 2nd, You can have Two SQL Server Express Instances as well :) – Tomer W Jul 29 '13 at 19:48