Moving Db Across The Country- Full Copy, Then Differential?
Jul 23, 2005
My company needs to move a 30 Gig SQL Server across the country as soon
as possble on July 1. Turns out moving the full db across the network
takes a few hours.
I'd like to move a full copy of db a week ahead of time, and then just
move either a differential backup(s) or transaction log with with the
week's new data on July 1.
Can anyone suggest the best strategy for doing this? Currently we're
doing a differential backup each hour on the db, and dumping the txn
log each night.
Many thanks,
Burt
View 1 Replies
ADVERTISEMENT
Jan 11, 2001
PLEASE HELP...
WE ARE DOWNLOADING THE BACKUP FILE FROM OTHER BRANCH OFFICE AND THE FILE SIZE IS GROWING LIKE HELL SO I AM CHANGING THE BACKUP POLICY TO DOWNLOAD THE DIFFERENTIAL BACKUP FILES EVERYDAY. BUT EVEN THE FILE SIZE GROWS DAY BY DAY UNTILL WE TAKE THE FULL BACKUP THERE. SO I WANT TO TAKE A DIIFERNETIAL BACKUP AND THEN IMMIDIATELY A FULL BACKUP AT THE REMOTE SERVER END. SO MY NEXT DIFFERNTIAL BACKUP WILL HAVE THE DATA OF ONLY PREVIOUS DAY.
FOR THIS I AM SCHEDULING A JOB WHICH FIRST TAKES A DIFFERENTIAL BACKUP AND THEN IMMIDIATELY FULL BACKUP EVERYDAY AT 12.00AM.
BUT I HAVE A CONCERN.. WHILE TAKING THE DIFFERENTIAL BACKUP ANY TRANSACTIONS THAT ARE TO BE COMMITED WILL NOT COMMIT UNTIL THE DIFFERENTIAL BACKUP COMPLETES. BUT IMMIDIATELY I AM STARTING FULL BACKUP. IS THERE ANY CHANCE THAT A TRANSACTION GETTING COMMITTED IN BETWEEN THESE TWO STEPS. IF SO IS THERE ANY WAY .. NOT RELEASE THE DATABASE UNTILL THESE 2 BACKUPS ARE COMPLETED?? I AM RUNNING IT AS A JOB..
ANY SUGGESTION??
THANKS IN ADVANCE.. :-)
KUMAR
View 2 Replies
View Related
Mar 28, 2007
Does the full and differential backups have to be in the same location or can I do a once a week full backup on one drive and everyday differential backups on a different drive?
Thanks
View 4 Replies
View Related
Aug 28, 2007
Hi All,
I am trying to restore full backup in Sql Server 2005. I am using the following query to restore full backup.
"Restore database Testdb from Disk = 'C:Testdbfull.bak'
with move 'Testdb' to 'C:DBTestdb.mdf ',
move 'Testdb_log' to 'C:DBTestdb_log.ldf ', norecovery "
The above query is running successfully and database restored. But i am not able to access this database since in the database tab it is showing 'Testdb (Restoring)'
The next day I want to restore the differential database backup so i used the 'norecovery' statement.
please help why it is showing as '(Restoring)'
Thanks in advance,
Senthil
View 21 Replies
View Related
May 12, 2008
Hi sql server experts.
This is a beginners question.
Let's say, I run a full backup at 4:30 am it takes usually 35 minutes to finish.
I want to schedule a differential backup to run every hour.
Should I start this job at 5:10 after the full backup finishes? or it doesn't matter?
I am confused about timing.
Let's say my full back up takes 1 hour to finish, that means that when the differential backup runs the full backup will be still running, so in case I need to restore the database, can I use this differential from 5:10 or the differential from 6:10.
Same issue with the transactional, the job runs every 30 minutes, should I started after the differential is done?
I am so confused, what happens with the backups jobs that run while other backups are running?
Is this something that should never happen.
Please advise.
Thanks
View 8 Replies
View Related
Sep 11, 2007
I want to set a full and differential backup to one database in sql server 2000.
Is there a way to set both full and differential to just one database.
i want the full backup weekly once and differential every day to set up.
Please let me know
View 3 Replies
View Related
Jun 29, 2007
Hi,
After some advice - I have a SQL Server 2005 database which is part
of an anti-virus setup. The main database is 25Gb is size, and it
is running in simple recovery mode. There are two backup jobs in
place, one to do a differential backup each Mon-Sat, and one to
do a full backup on Sun. Although the backups do get done they
are taking 5 hours to do. Any wiz out there care to suggest what
the problem is, I would've though that maybe an hour was more
acceptable ?
Cheers,
Gordon
View 2 Replies
View Related
Sep 13, 2006
I am running the following script to attempt a restore of a differential backup:
RESTORE DATABASE AdventureWorks
FROM DISK='C:SQL2005_BackupsAutoBackupsAdventureWorks.bak'
WITH
NORECOVERY
GO
RESTORE DATABASE AdventureWorks
FROM DISK='C:SQL2005_BackupsAutoBackupsAdventureWorksDiff.bak'
WITH RECOVERY
GO
I thought this was the way to do it. It does restore the full backup, but on the attempt to restore the differential backup, I get the following error:
Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Does anyone know what this means? Do I have to use "with recovery" on the first restore? (The sample I took this from used "with norecovery")
The original backups were done with SQL Agent scheduled jobs. The script for the full backup is:
BACKUP DATABASE AdventureWorks
TO DISK='C:SQL2005_BackupsAutoBackupsAdventureWorks.bak'
The script for the differential backup is:
BACKUP DATABASE AdventureWorks
TO DISK='C:SQL2005_BackupsAutoBackupsAdventureWorksDiff.bak'
WITH DIFFERENTIAL, INIT
All I can say is, it's a good thing I am testing this out with non-critical data, because I obviously don't know what I am doing. (Sorry, I'm primarily a programmer, not a DBA) Can anyone help?
Thanks
View 4 Replies
View Related
Feb 19, 2015
Using Ola Hallengren's scripts I do a full backup of a database on a Sunday. Then differential backups every 6 hours and log backups every hour. I would like to keep a full week of backups based off the full backup done on Sunday. Is there a way for me to clear out the diff and log folders after the successful full backup on Sunday nights?
View 2 Replies
View Related
Nov 16, 2015
I have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
View 13 Replies
View Related
Jul 19, 2007
Hi
I am using the Simple recovery model and I'm taking a weekly full backup each Monday morning with differentials taken every 4 hours during the day.
On Wednesday afternoon, a programmer ran a process that corrupted the db and I had to restore to the most recent differential. It was 5pm in the afternoon and a differential backup had just occured at 4pm. No problem, I figured.
I restored the full backup from Monday morning and tried to restore the most recent differential backup. The differential restore failed. Since I had used T-SQL for the initial attempt, I tried using Enterprise Manager to try again.
When viewing the backup history, I see my initial full backup taken on Monday plus all the differentials. BUT, on closer inspection, I noticed another full backup in the backup history that was taken early Tuesday morning. I can't figure out where this Tuesday morning full backup came from. It wasn't taken by me (or scheduled by me) and I'm the only one with access to the server. My full backups are usually named something like HCMPRP_20070718_FULL.bak. This erroneous full backup was named something like HCMPRP_03a_361adk2k_dd53.bak. It seemed like it was a system generated name. Not something I would choose. To top it off, I could not find this backup file anywhere on the server and when I tried to restore using this full backup, it failed.
Does anyone have any clues as to where this full backup might come from? Does SQL Server trigger a full backup on its own if some threshold is reached?
I ended up having to restore using the differential taken just before this erroneous full backup and lost a day of transactions.
Any insight is greatly appreciated.
View 3 Replies
View Related
Jun 6, 2007
Hi,
Using SQL Server 2005, we have a 2.8Gb database under the Simple recovery model. The database contains ~50M rows and each night ~60k rows are loaded(appended) to the database by a SSIS task.
We configured a Maintenance Plan which is executed once a week to perform a full backup of the database. The resulting backup file is ~2.8Gb, as expected.
We also configured another Maintenance Plan which is executed every day, a few hours after the SSIS task is executed, to perform a differential backup. To our surprise, the resulting backup file is about the same size as the full backup, ~2.8Gb when it should only be a few MB (only 60k rows are added to the database)
When we launch the "Restore Database" wizzard we clearly see the different backup set, Full and Differential but they all have about the same size (same for the physical backup file on disk).
Is there anything we are missing, why are the differential backup that big?
Thanks for any advice.
View 4 Replies
View Related
Mar 24, 2008
SQL Server 2005 sp2 Standard Edition.
I have a bunch of databases to relocated. relocating them on the same server - I just want to split the data and log files onto their own Volumes. these DBs have Full Text Indexing enabled.
This Server is a brand new build and NOT in production yet
How is the best way to move these Dbs and how do locate and move the full text catalog for each?? or do I even have to worry about that?? Is back-restore (with move) better for this?
When I look in BOL.. here is the syntax for an attach
USE master;
GO
--Detach the AdventureWorks database
sp_detach_db AdventureWorks;
GO
-- Physically move the full text catalog to the new location.
--Attach the AdventureWorks database and specify the new location of the full-text catalog.
CREATE DATABASE AdventureWorks ON
(FILENAME = 'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data.mdf'),
(FILENAME = 'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_log.ldf'),
(FILENAME = 'c:myFTCatalogsAdvWksFtCat')
FOR ATTACH;
GO
Do I have to move the Full text Catalog? If so, how do I find it's current location. If I do have to move it, where is the best place to locate the catalogs???
View 2 Replies
View Related
Aug 21, 2000
Hi,
I have a 44 gig db that I moved to another server via the NT copy and paste method. The db has gotten a little larger, and now NT gets almost to the end, but can not complete -- either starts over completely and just does the last few minutes over and over.
Is there an NT copy and paste limit? Any ideas what the problem is? Is the db mixed up?
Thanks for any help.
Judith
View 4 Replies
View Related
Sep 1, 2006
Hi,
Can anyone please explain the proper precedure for copying a SQL Express database between two instances?
I am accessing the database without problems from a local web application. And I want to copy the database to a SQL Express instance on another server, running the same web application.
I run into two problems every time I copy:
1) Orphaned users. I have to drop the database users and the re-map the server users to database users.
2) The full-text indexes are not available after copy, so I have to drop and re-create the indexes and the catalog.
And I suspect there's an easier way..
Regards,
Jens Erik
View 1 Replies
View Related
Nov 10, 2007
Is there any way to move a db with full-text indexes from one physical machine to another without having to drop catalog/indexes and the re-create them?
Cheers!
/Eskil
View 2 Replies
View Related
Jun 26, 2007
I installed the evaluation copy remotely and wish to register the product by entering its product key, would that possible since I don't have the cd in the box.
Matt
View 1 Replies
View Related
Feb 13, 2006
Hi all,
I am using Visual web developper 2005 with sql server express 2005 and i have also sql server management studio express. it's all free now .
my web site is ready
I didn't have problem to upload my site to my hoster.
Now I want to upload all my tables and my stored procedure create locally with VWD express
How can i do it ?
NB: I know i can't design DB (create/modify tables and stored proc) with express edition
thank's for your help
View 1 Replies
View Related
Apr 2, 2008
Hello,
I'm new to the SQL world and want to know if this is the site for a newbie or would you recommend another site more my speed. Thanks for the input.
Clint
US Navy - fueled, armed, and go for launch.
View 7 Replies
View Related
Mar 2, 2007
I have the country iso table does anyone have the data for tying contenents to the countries?
You can do anything at www.zombo.com
View 5 Replies
View Related
Dec 28, 2007
Hi,
I have created some statistics for my site based on the ip of the visitors. For that reason I have created two tables
1. One table with a column which hold the ip number of the visitor
Table: AccessLog
Column: IP
2. One table with the ip range and the countries
Table: Countries
Columns: ip_to, ip_from, country_name
I would like to create a query which will bring the country name of the visitor IP which IP will be like "ip_to <= ip <= ip_from"
Thanks
Denis
www.tabletennis.gr
View 20 Replies
View Related
Nov 18, 2014
I dont know the exact position of '/',',','--' or any special character.I have to fetch first name of the country.
create table abc
(country nvarchar(200))
go
INSERT INTO abc VALUES ('Tennessee/Virginia,USA')
GO
INSERT INTO abc VALUES ('Kansas,USA')
[code]...
View 7 Replies
View Related
Jul 20, 2005
Hi!How I can know the "Country version" of SQL Server using VB.NET(perhaps with the SQLDMO)?I mean: if the SQL Server is in "English version" or in "Italianversion" or ...Thank you so much!!Bye
View 1 Replies
View Related
Nov 9, 2011
I am having a users table which contains "Mobile" column as well. I want a query to set the country code value by default into the column name so that the column should be updated with the mobile number along with the default country code.
View 10 Replies
View Related
Oct 2, 2007
Quick questiom, I need to Import a table with City and State Information
What are your recomendations? Should I create a Unique Key for them?
CityID, CityName, StateID?
StateID, StateName?
Or can I just store them in a table with one column?
CityName, State
Thanks for your help/
View 2 Replies
View Related
Sep 16, 2013
I have built a sample table, query, and results for this question. I am using SQL server.
declare @TableX Table
(
Date Date not null,
ID int not null,
Tick varchar(6) not null
)
INSERT @tableX
[Code] ....
Expected results:
DATE(No column name)count
2013-09-02LON1
2013-09-03LON1
2013-09-04LON1
2013-09-05LON1
2013-09-06LON1
2013-09-02USA2
2013-09-03USA2
2013-09-04USA2
2013-09-05USA2
2013-09-06USA2
I want to select unique country - date pairs. It is not even necessary to have the count of each one, just the list of unique country/dates.
My query here uses 'group by' to accomplish this task, but there may be a way to do this with a self join. I believe using a self join would make the query faster.
1) Is this possible to do with a self join?
View 2 Replies
View Related
Feb 12, 2015
This Question is pertaining to AND and OR operators.
If we want to retrieve data form a country combined with 3 to 4 cities how do we handle this?
Say for ex:- i want to retrieve all data from Customers table where country is Germany and cities are Berlin, Mannheim,Brandenburg and München.
View 1 Replies
View Related
Sep 14, 2006
Any help here would be greatly appreciated...
Unfortunately, data wasn't filtered prior to getting inserted into this table. Now I am stuck with cleaning it up. I have thought about writing a query to update all the values, but there are just too many variations, including spelling mistakes, so I've ruled that out as a possible solution.
I have a table which has a Country field but the values per record vary. For example US, U.S., USA, United States, UK, United Kingdom, Canada, Can, etc. I'm trying to find the percent of records per country.
Sample table data: mytable
Id Name Country
1 John US
2 James UK
3 Jane United States
4 Mary Canada
5 Jack U.S.
6 Tony United Kingdom
7 Jeff US
8 Tom Canada
9 Beth UK
10 Mark USA
I would like to show
US: 50% --> (includes any variation of US ncluding US, U.S., USA, United States)
UK: 30%
CAN: 20%
I've made several attempts myself with no luck. Thanks in advance.
View 3 Replies
View Related
Aug 20, 2014
I am searching for the Sql Server Script of all country State and City with the Following type..
For the Country only 2 Columns are there i.e. CountryId and CountryName(CountryId is primary Key)
For the State 3 Columns are there i.e. CountryId, StateId and StateName(StateId is Primary Key and CountryId is Foreign Key)
For the City 3 Columns are there i.e. StateId ,CityId and CityName(CityId is PrimaryKey and StateId is foreign Key).
I need this type of Script with Column name.
View 7 Replies
View Related
Nov 10, 2015
I trying to get the moving total (juts as moving average). It always sum up the current record plus previous two records as well and grouped by EmpId.For example, attaching a image of excel calculation.
View 3 Replies
View Related
Apr 30, 2008
hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country}
who has an idea how to solve this problem?
View 9 Replies
View Related
Jun 13, 2006
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.
View 3 Replies
View Related
Jul 26, 2007
As I tested, I found with Transaction logs and Full Backup can support any changing of database.
So I doubt that Do I need to back up by diffrential style ?
Please suggest me.
View 1 Replies
View Related
