Solved

MS SQL DB settings and auto shrink question

Posted on 2004-08-18
5
1,361 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

776 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