Solved

MS SQL DB settings and auto shrink question

Posted on 2004-08-18
5
1,358 Views
Last Modified: 2008-01-09
1) What is auto shrink in MS SQL , should i check it ?

2) I have schduele a backup DB in my MS SQL to do backup every 3 hours , it seems it has only generated one file , should i be needed to restore the back up , how can go about doing it ?

3) migrating DB , if i want to change server , when i import data from my old MS SQL server to my new server , the tables and rows are imported , but the table attributes is always short fall or never did went over , how can i make sure the imports of the data and all its attribute are imported as well ?

please advise

thank you

regards

0
Comment
Question by:weeee
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:ram2098
ID: 11828895
1. Shrink the database is only an administrative option and it is not recommened to check this by default.

2. Backups are needed to recreate your database in case of database corruptions/losing data etc...So, continue to take the backups as per the schedule..but, need to restore only when needed.

3. Not sure how you are doing the migration..if you are using Enterpirse Manager ==> Import/Export option, you can set this as below..

Select option "Copy Objects and data between SQL Server databases" and uncheck "Use default options".

Select "Options" button and modify the options based on the attributes you require.

Hope this helps .. Ram
0
 
LVL 12

Expert Comment

by:patrikt
ID: 11828929
1) Auto shrink means that SQL will free unused space in your database files if it is possible. When that shrink will be done depends on your backup strategy and db operations.
In general if your database is of type "Constant grow" you don't need that.
If there is big logged operation from time to time in your db it should save you time and free disk space automaticaly.

2) You have to specify backup type you are using: Complete, Diferential or Transaction log?
All restore strategy depends on backup strategy. Describe when and what backups you are doing.

3) Simpliest way to move database is detach/attach. You will preserve all except logins. Logins are server wide and are stored in marer db. You have to create logins.

Patrik
0
 

Author Comment

by:weeee
ID: 11829395
Hi patrikt and all ,

My restore strategy is straightforward , retrieve all data on DB up to point when the last successfully backup is done

cheers

0
 
LVL 11

Assisted Solution

by:ram2098
ram2098 earned 250 total points
ID: 11829505
Since you are saying it is one file..I think you would have chosen the "Append" option when taking the backup.

So, when you try to "Restore" Thru Enterprise Manager..

Right click on database ==> All Tasks ==> Restore database => and select the file.

It shows you all the valid backup files on this. (From the first full backup to last differential backup)..Again depends on how you did the backup...like PatrickT said.

You can restore select any of the individual/ full backups from the list.

Is this clear?
0
 
LVL 12

Accepted Solution

by:
patrikt earned 250 total points
ID: 11829570
Explanation:

1) You have to have one comlete database backup to start from. No way if you don't have that, but doeson't meter how old it is.
Say that it is time T1 and backup BF1.
You are able restore DB to T1 with this.

2) May be you have some diferential backups. Say that you have last differential backup from T2>T1. Say it is BD2.
You are able restore DB to T2 with BF1 plus BD2.

3) May be you have some transaction log backups T3..T5>T2. Say this is BL3-BL5.
You are able restore DB to any point in time between T2 and T5. You have to use BF1+BD2+BL1+(BL2+BL3+BL4+BL5).


Is it clear?

All this you can see in EM as ram2098 writes.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now