Solved

MS SQL DB settings and auto shrink question

Posted on 2004-08-18
5
1,364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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