New answers tagged

0

Welcome to the forum. You do not need to do log restores between a FULL and a DIFF backup in SQL Server. If you do you'll break the LSN (Log Sequence Number) used to mark the delta's between the restores. A differential backup is all the transactions from the last full backup. SQL Server notes the last full backup in a data page when it backs up so it ...


1

I assume that your database is in the restoring state and you alredy have restored a full backupset WITH NORECOVERY. In this case you can inspect the contents of the log backups using RESTORE HEADERONLY: this will return the column FirstLSN, that you can use to compare with MAX(redo_start_lsn) from sys.master_files. All log backups with FisrtLSN < ...


0

This script will get you the last log that was restored to the database. You could use it to amend your script to pickup a the first file with a date > the backup_start_date. Alternatively, have you considered configuring Log Shipping? select top 1 bs.database_name, backup_type = case bs.type when 'D' then 'FULL' ...


2

That is the typical error message you get on Windows without properly setting up the environment. While the steps in the manual may work flawlessly on Linux/UNIX, on Windows you need extra steps. On Windows, you need to create a service with oradim, start it (if it was not started), and after that you can use sqlplus / as sysdba. The bare minimum is: set ...


1

In order to do a clone, you need to connect to the target database which is the database that you are cloning from. In this case you are doing a restore. So there is no auxiliary database. Just do a startup nomount, provided that you have a pfile with the proper settings. Then restore a specific control file as in "restore control file from ...


0

I would recommend backing up the transaction log more frequently than once a day, possibly every 15 minutes. It may be that a single very large transaction has blown up its size. You can monitor how full the transaction log gets during the day (in between log backups) using the command dbcc sqlperf(logspace) And yes, you can shrink the log, using a ...


0

Dual-Master, where you write to both masters, is full of problems. You need to carefully set the two auto_increment_* "variables". You need to make sure users cannot insert the same PRIMARY or UNIQUE values into different masters. If 'East' goes down, the Slave is hard to reconnect. If a hurricane hits the East, you lose both the master and slave there. ...


-1

Run the following script on source and destination SQL Servers, the versions should either match or the destination should have a higher version of SQL Server otherwise you cannot restore the .bak file: SELECT @@Version


0

It depends how far behind it fell and what status you have it in your config file at the time of resync. Basically if you've been down so long that it can't catch up with the OpsLog or the OpsLog no longer has the transactions it needs due to it being removed over time you'll want to resync yourself. Check out the docs for your version: Restart the ...


1

Actually, we have just released a new version of SqlBak where you can make scheduled backups and send them to OneDrive for Business.


0

Why don't you Detach your databases? If you do want to get a backup, just RMB on your database --> Tasks --> Detach Then you'll get a .mdf and a .log files(some times only a .mdf files). And if you want to restore your database you can simply attach those databases. Just RMB on Databases --> Attach and you can locate your .mdf file. HTH


0

You can either synchronize your backupt to OneDrive for Business or you can mount OneDrive as network drive via webdav. I tried both ways for a similar purpose but was pretty disappointed because OneDrive for Business does not work reliable. To mount OneDrive for Business you have to login with Internet Explorer (with Internet Explorer, not Firefox, Chrome ...


0

I use nightly lvm snapshots of production databases that are made static by removing the files needed for replication (master.info, relay-logs), and making that snapshot writable. You'll need to shut mysql down for a few minutes to make the snapshot and have room on the machine to hold that snapshot, but it's an easy and elegant solution.


0

After a lot of help from over at the PostgreSQL irc channel it was determined that the database was corrupted. The most likely cause of which was a hardware error on my ageing PC but also and much less likely a potential PG bug. I'm going to have to start again


0

I use gdrive (follow the installation and set-up guidelines from the link). Once gdrive is installed, I dump my own databases to a /bak folder daily, and then upload my files to Google Drive's PGBAK folder using gdrive. I use the following script in /etc/cron.daily/: #!/bin/bash PATH=$PATH:/usr/local/bin BAK=/bak # backup globals (database names, users, ...


0

You could try: https://github.com/andreafabrizi/Dropbox-Uploader to send your pg_dump output to Dropbox. I got this by Googling script load file dropbox. You may find some of the other scripts more to your liking. Remember the dba's motto - backup or f*ckup and test, test and test again. Personally, I would recommend a local (speed of recovery) and remote ...


4

you can download the PostgreSql backup here.. http://postgresql-backup.com/ this stores the backups on a network folder, FTP server or in the cloud (Google Drive).


1

ApexSQL Restore will mount the backup as virtual Database, and you can query it using SSMS. I also published a PowerShell script that will let you analyze your backups in a given folder. I wrote it for analyzing size but you could easily adjust this to include properties like the date taken and such to find which backup you need to mount. You can find the ...


2

Kris Johnston said in a comment: nomount/mount/open are the states of the database... something can't connect to the db one way and connect as nomount, and connect to the same db a different way and connect as mount or open. It doesn't work like that. It sounds like you are connecting to a different database altogether. To check, verify the dbid's are ...


0

To connect to RMAN from the operating system command line and hide authentication information, you must first start RMAN and then perform either of the following actions: Run the CONNECT commands at the RMAN prompt. ... Run a command file at the RMAN prompt that contains the connection information.


1

Yes, it can be a good idea. In fact, allowing this was a major feature in the release of PostgreSQL 9.2.


8

A .bacpac file = Schema + Data. Note that Data is BCP'ed out using Native format (not readable by Human). You can rename the .bacpac to .zip to see the actual contents. You can use ...DAC\bin\sqlpackage.exe commandline to extract the .bacpac contents programatically. It is a snapshot that includes User data + Schema from SQL Server or Azure SQL ...


0

On a terminal, type systemctl cat 'postgresql*' That will show you where the PGDATA environment variable is pointing to when you start the service normally via systemd, and all the parameters used for starting and stopping the service.


2

A solution for a database set to recovery mode SIMPLE is having the FILESTREAM data in a read-only file group (which isn't your ideal option), and then backing up only the read/write file groups with DIFFERENTIAL like this: BACKUP DATABASE [name] READ_WRITE_FILEGROUPS TO DISK = '' WITH DIFFERENTIAL, COMPRESSION; It will get any data that has changed in ...


1

I feel dirty providing this as an option, but if you choose to segregate the FILESTREAM data into its own database, you could maintain RI between the tables in the separate dbs by way of triggers: Triggers -> Remarks -> Limitations: A trigger is created only in the current database; however, a trigger can reference objects outside the current ...


3

Its easy using T-SQL. I would suggest not to use Maiantenance plan. Instead go with a much flexible and widely adapted TSQL based solution - SQL Server Backup by Ola Hallengren EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES, -DB1,DB2', -- All user databases, except Db1, DB2 @Directory = 'C:\Backup', @BackupType = 'FULL', @Verify = 'Y', @Compress ...


1

This seems to me like the opposite of a problem... Regardless of requirements, I just feel better knowing every database is being backed up. Unless these two databases are quite large and can fill up a significant amount of disk space with backups, what negatives are there to backing up those files?


1

I would check the Management > Maintenance Plans folder, in the Object Explorer pane of SSMS. The other place would be under SQL Server Agent > Jobs, so I'm curious what you meant by, "I was able to find the full backup option on the SQL Agent." If there is a job titled "Full Backup" under the Jobs folder, then that's probably what you're looking for.


2

The problem this caused was that the log file (which also gets backed up) is now unnecessarily large because it includes the FILESTREAM data. I'm not sure if you mean the log file itself is too large or that the log file backups become too large. If it's the former then how often were you backing the log up? Depending on the application design you may ...


2

Will these applications interfere with each other if they are both processing the same database? Yes they would affect each other, in terms of blocking, when running "concurrently", the one would block the other. But if they are not running concurrently like you schedule copy_only backup after full backup or before full backup, copy_only full backup is ...


4

I've set up option 3, Master Server, a number of times, but never for scheduling backups. I'm able to schedule jobs for performance data collection, pushing out administrative changes/script updates via SQLCMD, and run tests against multiple versions of SQL Server on multiple instances to test for any unexpected changes made after SP/CU application (among ...


0

This was a very helpful link: https://mysqlintheenterprise.com/tag/mysql-enterprise-backup/


2

Their cannot be a comparison drawn as Snapshot is "photo copy" of data file. The difference lies in amount of activity both does and consistency of product arising out of both the operations. Full backup "Is Always" more reliable than snapshot backup Full backup includes all committed and uncommitted transaction when full backup has finished. Read more ...


4

Mostly because a snapshot isn't really a backup. As explained in How database snapshots work The snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of ...


2

Here's a shell script that can do what you want: SCHEMA="myschema" DB="mydb" psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" $DB |\ while read TBL; do psql -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV" $DB > $TBL.csv done Make sure you set the DB and SCHEMA variables to your particular database and schema. The wrapping psql ...


7

It can be specified in TSQL but the documentation states COPY_ONLY has no effect when specified with the DIFFERENTIAL option. There's no need for copy only differential backups because you can just take a normal differential backup. Taking a differential backup doesn't break anything as each such backup is cumulative and contains all changes since the ...


3

It's not really a requirement. The database itself will work perfectly without backup after switching between ARCHIVE / NOARCHIVE mode. But if something ever goes wrong (or database needs to be copied/cloned to other machine), you'll need a backup. You may or may not use incremental backups feature ; if you do want to use them you must take 0-level ...


3

You cannot use backups from before the switch to ARCHIVELOG mode to restore and recover the database to a point in time after the switch. Looking at this quote it makes sense. If you enable archivelog on a database where it was not enabled, then any backup that you have before archive log was enabled can't be used to do a point in time recover, since ...


0

Turned out to be a minor corruption of the SQL backup file. After re-running the entire process again, the restore worked fine with no errors. You would think that Postgres would have in-built checking to ensure integrity of exported data!



Top 50 recent answers are included