Hot answers tagged database-restoring
10
If you cannot take the databases offline, tne you need to do backup/restore. I would suggest the following:
Install SQL 2008 on the new box, using the same file structure as the old box for MDF and LDF files.
Take backups of all databases on the old box.
Restore master from the old box to the new box having the SQL started in single user mode. Restore ...
9
Shrinking your transaction log should not be part of your routine. A transaction log backup clears the log file automatically; shrinking it afterward just causes it to have to grow again afterward. Read this for more information.
Yes, you should be able to perform point-in-time restores using your latest full backup and any subsequent transaction log ...
8
Using SSMS, you cannot chain a restore of the backups in one operation. You would have to do multiple restores. You'll want to use T-SQL in order to be more efficient.
--Restore the most recent full backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to full backup'
WITH NORECOVERY, STATS=10 --If only restoring the full, change to RECOVERY
--Restore the ...
7
You could use file-system level snapshots, but that is often pretty cumbersome, needs special file systems, and is not always available, especially on aging laptops. ;-)
How about you create your base state as a database, and then create a new database from it for your test run, using the CREATE DATABASE ... TEMPLATE functionality. After the test, you ...
7
You mention 24/7 operation, so the best way to do this is to create a database mirror on the new system, then you can simply switch over with minimum - perhaps zero, depending on how your application is structured - downtime using a client-side redirect. The basic steps are:
Make sure your primary database is running in FULL recovery mode.
Backup the ...
6
Never heard such rubbish.
FULL or BULK LOGGED mode means you require log backups. SIMPLE means you don't.
You always require full backups.
Copying the MDF and LDF files isn't a backup and can potentially lose** you data if you are in a transaction as you shut SQL Server down.
If the mdf files are missing it means they aren't there because of finger ...
6
I see you already found a solution to your problem, one thing I noticed in your original question was that you still had access to the old server.
The following question on SO had a similar problem and includes links to a Microsoft article with a script to generate the user permissions.
...
6
At my previous job I did exactly this. We were in the 500 tenant range, but once you get above 10 or 20 the automation part makes the actual number much less important.
Assuming that your hardware can handle backing up 10,000 databases in a sufficient time, and that all 10,000 aren't having significant enough activity to overwhelm your server with or ...
6
If the backups on those 5-10K databases are run serially, there shouldn't be a material performance difference than if you were running a backup on one giant database. You might get by with running a few backups simultaneously, if your databases aren't large and you have good I/O capacity, but I wouldn't count on that.
You will want to stay away from the ...
6
A much more efficient path than messing with that wizard would be to:
Take a full backup of your 2000 database.
Restore it to a SQL 2005, 2008 or 2008 R2 instance, all of which support 2000 databases. You can still get the evaluation edition of 2008 R2 here, if you don't have any applicable instances in place. Note that Express won't work as it has a 4GB / ...
5
Unfortunately not. SQL Server will always attempt to restore to exactly what is recorded in the backup file, unless you explicitly specify otherwise (with MOVE in this case).
The REPLACE option only works if you're replacing files owned by the database you're restoring (which makes sense, You may have said you're fine killing DB_A by restoring over it, but ...
5
When restoring a sequence of backups (differential & logs) you specify NORECOVERY. This leaves the database in a state that will accept further restores, rather than recover it ready for use.
RESTORE DATABASE MyDb FROM DISK = '<path>\MyDb_FULL.bak' WITH NORECOVERY;
RESTORE DATABASE MyDb FROM DISK = '<path>\MyDb_DIFF.bak' WITH NORECOVERY;
-- ...
4
This is known as "orphaned users". Here are 2 ways to fix it
If you can, restore the original master database as "loginsource" and sys.server_principals has enough info to generate all SQL Server and Windows logins. That is, the SIDs and encrypted password
If you use Windows logins only, then you can run this per database to generate a script
Script:
...
4
--Restore the most recent full backup
RESTORE DATABASE <DATABASE NAME>
FROM DISK = <Path to full backup>
WITH NO RECOVERY
--Restore the most recent differential backup
RESTORE DATABASE <DATABASE NAME>
FROM DISK = <Path to differential backup>
WITH NORECOVERY
--Restore all the transactional log backups which was taken after the ...
4
You can let mysqldump create the dump in such a way that it does not create or select the database.
EXAMPLE : You are dumping the database db1 and loading it into database db2
This will put in the CREATE DATABASE and the USE commands in the dump
mysqldump -u... -p... --routines --triggers --databases db1 > /root/db1.sql
This will not put in the ...
4
Personally, I like my backups to be timestamped something like MyDB-yyyymmddhhnn-Full.bak or MyDB-yyyymmddhhnn-Log.bak in the file name. I've never actually read the LSN from a backup file, neither have I read the LSN information from the system tables.
You can sort the files and pick out which ones you need quite easily.
4
Typically, backups to file are appended with a timestamp (e.g. MyDatabase_FULL_201202060900.bak) so you have the option of using a script to generate the restore sequence given a directory of files. Google will give you dozens of examples, as will the SSC script library. I have this script in my library currently.
If the server you've taken the backups from ...
4
No, for a restore you don't have to have the database existing already (be mindful though to not have the with replace option set in your restore database command).
The below statement would create a new database from the backup:
RESTORE DATABASE [YourNewDbName]
FROM DISK = N'C:\YourBackupFile.bak'
WITH FILE = 1,
MOVE N'YourDataFile' TO ...
4
There is a hack you can try: where you create a dummy DB with the same name and size as your old data and log files. Then you have to stop SQL server, replace the new empty files with the old files that have data in them.
Check out this blog link by Paul Randal where the hack is explained in detail. It looks like you have tried the first method mentioned, ...
3
By default, a DB is not ready to use. It stays "restoring" ready to receive log restores.
Simply run this to bring the DB on-line
RESTORE DATABASE MyDB WITH RECOVERY
Or using the GUI, this is step 12 in the MSDN SSMS "how to" (a different place to what you mention)
3
Use RMAN to back up to the network share. An export is NOT a true backup as you cannot perform a point-in-time recovery with it. XE comes with backup scripts that you can just schedule with AT or similar, the documentation says:
To schedule automatic backups, use any operating system or third party
task scheduling software to run the supplied backup ...
3
You can use the Import/Export wizard to move data between databases.
Right click on the database you want to export, choose "Tasks", then "Export Data". The wizard will guide you through the process.
You're right, though. You won't be able to do a backup/restore moving from SQL Server 2008 R2 to SQL Server 2008 Express.
3
DISCLAIMER : I am not a PostgreSQL DBA, though I dabble a lot with it.
You probably need to check your timezone in the OS and in psql.
In the OS run this:
[postgres@radarPG-db1 ~]$ date
Fri Jun 17 12:55:37 EDT 2011
In psql, run the following:
postgres=# SELECT EXTRACT(timezone_hour FROM now()),EXTRACT(timezone_minute FROM now());
date_part | date_part
...
3
Although less than ideal, cold backups are a perfectly legitimate way to backup a database. The assumption is that the database has been shut down, hence the "cold" nomenclature versus a "hot" or "warm"(1) backup where the database is online.
The down side of a cold backup is you're copying the entire file structure of the database regardless of how full ...
3
Ideally you would script out the users and permissions prior to doing the restore. If that has not happened, then you need to go about fixing things after the fact, and chances are something will be missed but you should be able to get about 90% of the way there.
The first thing you need to ascertain is if the same logins exist on the new server. If they ...
3
If you are concerned about your logfile and the postgresql.conf, just do a file system backup of them before running initdb.
If your database did suffer from a harddisk corrupted then it is probably advisable to run initdb to make sure everything (including the system tables) are re-created properly.
As the configuration files are just plain text (as are ...
3
Have you tried the --disable-triggers option to pg_restore?
Per the documentation: Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload.
Please note that this only is valid for a --data-only restore and requires the --superuser=username option to be passed, as well.
3
If you abort a RESTORE mid-way the database is in an unusable state. This makes sense: Some pages are old, some are new.
Stopping the debugger kills the client process causing SQL Server to kill the connection and all associated sessions and requests.
To get it working, restart the last restore step that was interrupted. In your case, restore from snapshot ...
3
It sounds like what you're talking about is more aligned with High Availability.
If you are already on SQL Server 2012, or you have the ability to upgrade then it sounds like AlwaysOn Availability Groups could be right up your alley. This is a technology that will allow you to create a cluster and you could have two geographically separate servers (i.e. ...
3
At one point, this was in principle possible. All you had to do in theory was to reset the current transaction ID, and you would see a previous state of the database. Search for "time travel" in very old documents.
That, however, was before WAL, HOT, lazy vacuum, freespace map, virtual transaction IDs, and all the other goodies that were introduced among ...
Only top voted, non community-wiki answers of a minimum length are eligible
