Tag Info

Hot answers tagged

16

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 ...


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

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 ...


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 ...


8

You'd have use the Import/Export wizards in SSMS to migrate everything There is no "downgrade" possible using backup/restore or detach/attach


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 ...


7

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.


7

No, you cannot rely on system or NTFS backups as an appropriate disaster recovery strategy for SQL Server. If all your current solution is doing is backing up the database files (i.e. mdf, ldf, ndf) and if you needed to revert to those "backups", then you could potentially run into a horrific situation where SQL Server can't use those database files. You ...


7

622 is an internal intermediate version, never released. How come you have an 622 version DB? The explanation is simple actually: an aborted upgrade. Look at the sequence Aaron posted: Converting database 'x' from version 611 to the current version 655. Database 'x' running the upgrade step from version 611 to version 621. Database 'x' running the upgrade ...


6

The only way is to create a new database on the 10.00.2531 version and import the data from 10.50.1600. You can't restore down to an older version.


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

You can try and use data recovery software to recover the blocks as odds are the blocks haven't been overwritten yet. If they have been overwritten, then the data is gone. I'm sorry to say, but you've just learned why backups are important.


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

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 ...


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; -- ...


5

Check the MySQL error log. It looks like trying to read table 44 is actually crashing the server. "Lost connection to MySQL server during query" is (or should be) heart-stopping time for a MySQL DBA because it often means that whatever your query just did has actually crashed the server. The subsequent messages seem to bear this out: mysqldump: Got ...


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 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.


4

You can use the filelistonly option to retrieve the files names in a backup: restore filelistonly from disk='c:\mumu.bak'


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

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, ...


4

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 ...


4

Unless you are running all of your databases in simple recovery mode, you are eventually going to run out of transaction log space. Read some good write-ups about transaction log management. As transaction log management is fundamental a topic of managing an Sql Server, I'd recommend you to pick up a book about the subject too. For example, Microsoft Press ...



Only top voted, non community-wiki answers of a minimum length are eligible